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:
month.day.year.
07.11.04
09.14.04
08.02.04
08.07.04

afrer sort:
07.11.04
08.03.04
08.07.04
09.14.04

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?

Thanks,

Waffe






waffeAsked:
Who is Participating?
 
madwaxConnect With a Mentor Commented:
correction table strcuture:
test(id*,myTimestamp)

sorry... =/
0
 
madwaxCommented:
How do you store the dates in the database? If you store them as timestamps (@see: http://www.experts-exchange.com/Databases/Mysql/Q_21082709.html) 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 (http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html) but it only counts days...

Good luck,
//madwax
or
0
 
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:
mktime(
  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.
0
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.