Date base of dates - what is the closest date to the current date?

Hi I am using a sort date function in PHP like this:
$result = mysql_query("select * from shows ORDER by showdate");
Now what I need to do is a comparison of the current date to the dates in my data base.
I need to know the number of the row that is closet to the current date. Like this

todays date = 08.04.04

Before sort:

afrer sort:

The number of the row that is closest to the curret date is "2" (08.03.04)

I need this number so I can pass it on to a data grid so it will automatically select the date closest to the current date. Can this be done?



Who is Participating?
madwaxConnect With a Mentor Commented:
correction table strcuture:

sorry... =/
How do you store the dates in the database? If you store them as timestamps (@see: I would guess you could do as follows, select the date with the smallest difference to the current date, this is easily done i sql. Just an example:

SELECT id,MIN((UNIX_TIMESTAMP()-myTimestamp)) AS diff FROM `test` GROUP BY myTimestamp ORDER BY diff

where the table looks like:
test(id*, timestamp)

The query returns the id of the tuple with the smallest difference in time.

N.B!. you will have to modify it to work 100%, e.g. how to react for future dates which now return a -number and so on. But I think this would be the way I would try to do it. You

You might also want to check out the MySQL function: DATEDIFF ( but it only counts days...

Good luck,
arantiusConnect With a Mentor Commented:
Run these two queries:
SELECT showdate FROM shows WHERE showdate>=NOW() ORDER BY showdate DESC LIMIT 1
SELECT showdate FROM shows WHERE showdate<=NOW() ORDER BY showdate DESC LIMIT 1
Extract the two dates. Turn them into a timestamp thusly:
  substr($time, 11, 2), substr($time, 14, 2), substr($time, 17, 2),
  substr($time, 5, 2), substr($time, 8, 2), substr($time, 0, 4)

Compare those two values to the value of time() in PHP.  The closer value is the closer date.  Manipulate that timestamp as you will, perhaps with the date() function.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.