PHP/ MySQL - Band tour date script. - works out when a gig has passed.

[jameswoodhouse]

Hi there, basically i'm trying to write a php script that allows me to retrieve the tour dates for my band from a MySQL table. I want the gigs.php page to show every upcoming gig that there is.

On gigs.php i want a link to gigarchive.php which features all passed gigs. This page should update after the day on which the gig occured has passed.

Simularily the gig.php page should update once the day that a gig is occuring has finished.

I guess this would have to be done by some sort of date filter. e.g.

SELECT * FROM 'gigs' WHERE 'date' < Now()

As you can see i'm probably on completly the wrong track. Thanks in advance.

SELECT * FROM gigs WHERE date < now()

Seems OK to me, but it depends on how your date data is stored.

now() will also include the time, so you'll need to strip this out. Depending on the version of mysql you're running you can use DATE() or DATE_FORMAT() to strip this:

SELECT * FROM gigs WHERE date < DATE(now())

[B]or[/B] 

SELECT * FROM gigs WHERE date < DATE_FORMAT(now(),'%Y-%m-%d')

If you're wondering why we bother, mysql makes a full comparison of Y-m-d h:i:s when comparing to a datetime, so you may get a few quirks.

This assumes your date is stored as unix date (yyyy-mm-dd). If not, I'd recommend you change it to this, it just saves a lot of hassle when working with dates.

The update you talk about is handled automatically by the sql above.

I bet you can guess how to display future dates now.

[jameswoodhouse]

thanks that has helped lots. I still can't get the date format bit to work though. The 'date' datatype in mySQL saves it in yyyy-mm-dd.

i've gone back to using this: $sql = 'SELECT * FROM gigs WHERE date < DATE(now()) ORDER BY date DESC'; that doesnt sort it properly though. I sort get what you are saying but it just produces php errors :-(

The MySQL should work, unless you're using MYSQL4, in which case you'll need the DATE_FORMAT() thingy.

What exactly is the problem? If you show your data input and data output script, we'll be able to help.