Sorting records by date, depending on date!

[Tjobbe]

I have a php script that loads records from a database, it shows the title, date and time of an upcoming gig.

The date is user enterred int he format dd/mm/yy - just as text not as "date" in mysql though.

I'd like this script that I have to show the next three gigs, so if it is after a gig, to skip it and then only show the next three, is this possible to do at all?

here is what I have right now:


<?php
include "gigs/admin/dbconnect.php";
include "gigs/includes/format_date.php";
$result = mysql_query("SELECT * FROM events ORDER BY id ASC LIMIT 0,3");
//echo's the articles
while($row = mysql_fetch_array($result))
{echo "
<p><strong>".$row['title']."</strong><br />
<em>".$row['date']." @ ".$row['time']."</em></p>
";
}

mysql_close($conn);
?>

[inimino]

First, change your database schema to use the proper date type for storing dates. A date stored as a string takes up more space in the database, can't be reliably sorted on, can't be compared as a date, and may contain values that do not even correspond to an actual date.

Once you've solved that problem you can do something like:
SELECT * FROM events WHERE date > now() ORDER BY date DESC LIMIT 3.

[Tjobbe]

First, change your database schema to use the proper date type for storing dates. A date stored as a string takes up more space in the database, can't be reliably sorted on, can't be compared as a date, and may contain values that do not even correspond to an actual date.

Once you've solved that problem you can do something like:
SELECT * FROM events WHERE date > now() ORDER BY date DESC LIMIT 3.

That's fantastic, thanks MJ.

I've got it working now, now just to change the default date display format! I might be back!