nearest date

Yrogerg

i've got a series of events stored in mysql that each have a date associated with them via year, date, month - all int fields.

i'm wondering how i would calculate the nearest date to display to the user. ie, if today's date matches a date display that, otherwise display the next closest.

devinemke

Originally posted by Yrogerg
i've got a series of events stored in mysql that each have a date associated with them via year, date, month - all int fields.

why are you storing your dates this way? mySQL has native DATE and DATETIME formats which would make you life easier. you query would be simply:

SELECT * FROM table WHERE date >= CURDATE() LIMIT 1

Drakla

devinemke's absolutely right, you've created a whole heap of trouble for yourself not using a built in time type. I find MySQL's datetime functions revolting and always use timestamps as the maths is simple.

Yrogerg

i avoided the mysql timestamp b/c the user inputs all of these events and not in any particular order. they also love to go back and edit the crap out of them.

if there's an easy way to format user input into a mysql date that would be terrific.

Drakla

To create a timestamp within a mysql statement use it like

SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');

If you have 3 boxes in your input field

$query = "INSERT INTO mytable ('who', 'when') VALUES ('$name', UNIX_TIMESTAMP('$year-$month-$day'))";

Or if it was a date field you'd just insert '$year-$month-$day';

Within PHP you can use [man]mktime[/man] or [man]strtotime[/man]

Yrogerg

nice, much cleaner and efficient. many thanks.