Sorting Question

[artisticre]

The following code pulls distinct month out of the database into a dropdown menu. My question is how do I sort it so the months are in order such as Jan, Feb, Mar.... Right now it is in alpha order. Thanks

<?
#**********************************************************************************
$connection = mysql_connect("","","")
or die("Couldn't make connection.");
$db = mysql_select_db("***", $connection)
or die("Couldn't select database.");

#*************************************************************************************
$Showsql = "SELECT distinct Month from craftshows ORDER BY Month ASC";
$Showsql_result = mysql_query($Showsql,$connection)
or die("Couldn't execute query.");
while ($row = mysql_fetch_array($Showsql_result)) {

$Month = $row["Month"];
    $Shows.= "<OPTION value=\"$Month\">$Month</OPTION>";

}

?>

<form method="GET" action="displayshows.html" name="Month">
<SELECT name="Shows" style="color : #ffffff; background-color : #A59ACF; font : bold; letter-spacing : 0px" size="1">
<option value=\"\">Select a Show Month</option>";
<? echo "$Shows"; ?>
</SELECT>&nbsp;<input type="submit" name="Submit" value="Go" style="color : #ffffff; background-color : #A59ACF;
font : bold; letter-spacing : 1px">
</form>

[jtacon]

Create a table with the months:
mysql> create table convMonth ( monthNumber int(12), monthName char(30) );
mysql> insert into convMonth values (1,'Jan');
mysql> insert into convMonth values (2,'Feb');
mysql> insert into convMonth values (3,'Mar');
mysql> insert into convMonth values (4,'Apr');
mysql> insert into convMonth values (5,'May');
mysql> insert into convMonth values (6,'Jun');
mysql> insert into convMonth values (7,'Jul');
mysql> insert into convMonth values (8,'Aug');
mysql> insert into convMonth values (9,'Sep');
mysql> insert into convMonth values (10,'Oct');
mysql> insert into convMonth values (11,'Nov');
mysql> insert into convMonth values (12,'Dec');

Now you only need to do a left join in your selects. In this case:

SELECT distinct Month FROM craftshows LEFT JOIN convMonth ON test2.month=convMonth.monthName ORDER BY monthNumber ASC

[majik-sheff]

A better way would be to convert the column holding the month to an ENUM type by executing this SQL:

ALTER TABLE `table_name` CHANGE `month` `month` ENUM('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

Be sure to back up your database first, just in case the command mangles something.
This method will cause ORDER BY to work the way you expect and is:

• Faster

• More space efficient

• Cleaner

• Standard Practice

[artisticre]

Ok this is what I have after I created the table. I just copy & pasted the table info and created them but when I view the page, it cannot execute the query??

<?
#**********************************************************************************
$connection = mysql_connect("","","")
or die("Couldn't make connection.");
$db = mysql_select_db("***", $connection)
or die("Couldn't select database.");

#*************************************************************************************

$Showsql = "SELECT distinct Month FROM craftshows LEFT JOIN convMonth ON test2.month=convMonth.monthName ORDER BY monthNumber ASC
";

$Showsql_result = mysql_query($Showsql,$connection)
or die("Couldn't execute query.");
while ($row = mysql_fetch_array($Showsql_result)) {

$Month = $row["Month"];
    $Shows.= "<OPTION value=\"$Month\">$Month</OPTION>";

}

?>

<form method="GET" action="displayshows.html" name="Month">
<SELECT name="Shows" style="color : #ffffff; background-color : #A59ACF; font : bold; letter-spacing : 0px" size="1">
<option value=\"\">Select a Show Month</option>";
<? echo "$Shows"; ?>
</SELECT>&nbsp;<input type="submit" name="Submit" value="Go" style="color : #ffffff; background-color : #A59ACF;
font : bold; letter-spacing : 1px">
</form>

[majik-sheff]

Or you could just ignore me... shrug