date() and sorting

yamcha2021

Hello everyone.

Really quick question for you all 🙂

I have a database that holds tour dates for local bands and stuff. I have one column called show_id which increments automatically and another called tour_date which holds the day in format YYYYMMDD.

The goal is to sort the shows (when displayed on a site) using the column tour_date.

This is easy with my current format YYYYMMDD, but I was wondering if I used the PHP function date() and the sql date (format YYYY-MM-DD), would I still be able to sort the output using the dates and not the show_id.

Thanks!

keith73

quick answer: YES.

keith

yamcha2021

Ok. Cool. Yes is great!

Would you have some example code that would do that or is it a simple

$sql = "SELECT * FROM tours ORDER BY tour_date";

$bla = mysql_query($sql);

and so on and so on?

diego25

Yup, that simple.

Diego

keith73

Originally posted by yamcha2021
Ok. Cool. Yes is great!

Would you have some example code that would do that or is it a simple
$sql = "SELECT * FROM tours ORDER BY tour_date";

$bla = mysql_query($sql);
and so on and so on? [/B]

you could even sort it most recent first. The default in MySQL sort is to do it in Ascending order. By adding DESC at the end of your ORDER BY statement, it reverses the order so the last date entered is the first date seen. In your case, you probably don't want to do that so folks can see the tour dates in order. But in case you did:

SELECT * FROM tours ORDER BY tour_date DESC;

Also, you can specify a month, day or year or all 3.

SELECT * FROM tours WHERE MONTH(tour_date) == 3 AND DAYOFMONTH(tour_date) == 17 ORDER BY tour_date ASC;

MySQL date functions
http://www.mysql.com/doc/en/Date_and_time_functions.html
MySQL date/time column types
http://www.mysql.com/doc/en/Date_and_time_types.html

Keith