asked on

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

madwax

How do you store the dates in the database? If you store them as timestamps (@see: https://www.experts-exchange.com/questions/21082709/Sort-date-style-month-day-year.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

ASKER CERTIFIED SOLUTION

madwax

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

arantius

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial