3

UPDATE

I'm making a backend of a website. The mean point of the website is to give a clear overview to the visitors when and what activities we are organizing.
So I filled the database with all kinds of activities. Every activity has a date, a name and some other information.

It's quite obvious the visitors aren't helped by showing the activities in the past, so I only want them to show the forthcoming activities. And to save myself a lot of work, it would be nice to do PHP/MySQL all the work and showing the visitors only the 2 next events.

Can you advise me a MySQL function to do this?

Thanks in advance,

Michiel
  • 7,855
  • 16
  • 61
  • 113
  • Erm. PHP doesn't have direct access to *your database*. This means you don't use a PHP function to get something from MySQL. Can you give this a bit more thought and rephrase? – Alin Purcaru Apr 18 '11 at 14:23
  • When you say closest to today, are you talking about activites before today, after today (assuming you have activities in the future), or both? – d.syph.3r Apr 18 '11 at 14:23
  • @Alin Purcaru: yeah right, my bad. I sure meant MySQL of course. I'm not that skilled in PHP, so I was just wondering. Do you get what I'm trying to accomplish? Or do you need more information? – Michiel Apr 18 '11 at 14:26
  • @d.syph.3r I do mean activities before today. The database is filled with activities both past and future. The ones in the past are just for our information, the ones in the future are for the visitors. So I only want to display the ones in the future. – Michiel Apr 18 '11 at 14:27
  • @Alin Purcaru I don't have enough information to know for sure what you want. I can only guess. You should edit the question and add more details. – Alin Purcaru Apr 18 '11 at 14:29
  • @Michiel, update your question... you don't want the closest date, you want the closest future date... – Jacob Apr 18 '11 at 14:34
  • @Alin @Jacob, Question updated. I hope it's more clear now. If not, please let me know – Michiel Apr 18 '11 at 14:36
  • @Michiel You don't have a certain *function*. You have to do your own query. For what you want is quite simple. See my updated answer bellow. – Alin Purcaru Apr 18 '11 at 14:39

3 Answers3

3

You can use DATEDIFF and ABS...

SELECT * FROM `activities` ORDER BY ABS(DATEDIFF(NOW(), `date`)) LIMIT 2

From the updated question only future activities are to be considered... So...

SELECT * FROM `activities` WHERE `date` > NOW() ORDER BY `date` LIMIT 2
Jacob
  • 8,278
  • 1
  • 23
  • 29
  • Quick solution. But bad performance-wise if there are too many events. You can't add an index on an expression like that, but you can add it on the time column. – Alin Purcaru Apr 18 '11 at 14:36
3

Just some advice:

Select the closest 2 events before the current moment and the closest 2 after the current moment.

(SELECT *
FROM ...
WHERE `time` < NOW()
ORDER BY `time` DESC
LIMIT 2)
UNION
(SELECT *
FROM ...
WHERE `time` > NOW()
ORDER BY `time` ASC
LIMIT 2)

At this point you'll have at most 4 events in PHP. But you only need 2. The closest 2. To find out which are the closest you make use of strtotime and check the differences between the timestamps of the events and the current timestamp that you can get by calling time(). After that you keep only the 2 closest events.

Note that this can be achieved directly from MySQL, but it's harder that way.

@Latest update. It's sufficient to read this if you don't want to know more.

Just use this query:

SELECT *
FROM ...
WHERE `time` > NOW()
ORDER BY `time` ASC
LIMIT 2
Alin Purcaru
  • 43,655
  • 12
  • 77
  • 90
0
$query = 'SELECT .... FROM ... WHERE `date` > '.$today.' ORDER BY `date` DESC LIMIT 2';

$today depends on your date column type.

Emmerman
  • 2,371
  • 15
  • 9