[RESOLVED] Comparing dates

harlequeen

I have a db which has a field called 'date' and is set to date type. I want to be able to take todays date, compare it with the dates held in the db and print out the record with the next date in it.

Say today's date is 20/09/06, the record with the next date, say 23/09/06, should be printed out.

How can I do this easily. I am very new to this, and can't seem to get it right.

Any help would be appreciated.

Regards
Harlequeen

Installer

SELECT * FROM my_table WHERE date > CURDATE() ORDER BY date LIMIT 1;

harlequeen

Thanks for the help. I tried what you suggested but get no output.

This is my code.

$sql=mysql_query("SELECT * FROM gigs WHERE date > CURDATE() ORDER BY date LIMIT 1");


while(@$row=mysql_fetch_array($sql)){
//Build formatted result here
echo("<h5>");
echo $row['date']."<br/>";
echo $row['venue']."<br/>";
echo $row['town']."<br/>";
echo("</h5>");

}

This would normally return the result from the table 'gigs' and echo it to the page. Where have I gone wrong?

Cheers

bradgrafelman

The syntax seems fine to me (a bit redundant with all of the separate echo() calls, but anyway...), so what exactly is happening?

Does the SQL query run without any errors? Are you checking for errors using [man]mysql_error/man ?

harlequeen

I have another query which runs after the code which I have posted. It basically gets a random record from a table and prints it to screen.

So I have a section which says

Today's date is Saturday 16 September.

Then I want the 1 record which depends on the date comparison.

Then the random record from the db eg.
21 against are a rock band from South Wales.

The date is being displayed correctly, and the random record is being pulled from the db and being displayed.

I am not using the error trap, mainly because I don't know how to.... my syntax knowledge is very very limited.

As for the over use of the echo statement, it is because as a learner I tend to stick to things that work, and am only just coming to terms with embedding HTML etc within php.

Cheers
H

Installer

I see in your first post you mention dates in 'd/m/y' format ("20/09/06"). That's not how you're storing them in the database, by chance? If it is, look at The DATETIME, DATE, and TIMESTAMP Types

harlequeen

No, it is stored like this: 2006-05-19

Harlequeen

harlequeen

I have stripped everything out of the file, but the code as posted earlier, and the connection to the db of course. Nothing is being displayed at all.

Harlequeen

Installer

Then your code should work fine. Are you sure there are records in your table that actually have dates in the future? Do a query like "SELECT date FROM gigs" and echo out all the results to make sure you have what you think you have in the db.

harlequeen

Hi Guys

This is where I hang my head in shame :o I have been wasting your time. I just realised that I wasn't getting anything back because there was nothing in the db that actually fit the parameters. There is nothing in there with a date later than 9/9/2006.

Once I added something with a later date, it worked.

:bemused: My abject apologies for wasting your time, but heartfelt thanks for your help.

Cheers

Harlequeen

Installer

Well, it wasn't a complete waste of time. We all learned something. 😉