please help with ORDER BY

Anon

Ok...I've got a pretty simple script, and a probelem I cannot seem to troubleshoot by myself. I use this while statement to call up rows of data, but which are SUPPOSED TO be sorted by the showDate(stored as a yyyy-mm-dd formated date)...However, since I reformatted showDate to display the day of the week first, all the entries come out sorted alphabetically by their day of the week! that's bad, because I want them sorted by their dates. any help?

heres the code:

$result = mysql_query("SELECT showDate,showCity,showState,id FROM $DB_table ORDER BY showDate ASC LIMIT $ShowListStart,$ShowListLength",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("%s @ %s, %s (<a href=\"main.php?scene=0&show=%s\">More Info</a>) \n<br>", $myrow["showDate"], $myrow["showCity"], $myrow["showState"], $myrow["id"]);
}

thanks!

Anon

this is the code with the ORDER BY in it

$result = mysql_query("SELECT date_format(showDate, '%a, %b %D') showDate,showCity,showState,id FROM $DB_table ORDER BY showDate ASC LIMIT $ShowListStart,$ShowListLength",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("%s @ %s, %s (<a href=\"main.php?scene=0&show=%s\">More Info</a>) \n<br>", $myrow["showDate"], $myrow["showCity"], $myrow["showState"], $myrow["id"]);
}

Anon

This may be a case of the blind leading the partially sighted.. but..

and sorry if this is a bit off iffy coding..

$result = mysql_query("SELECT showDate,showCity,showState,id FROM $DB_table ORDER BY showDate ASC LIMIT $ShowListStart,$ShowListLength",$db);
while ($myrow = mysql_fetch_array($result)) {

$id = ($myrow["id"]);
$get_date = "SELECT *, DATE_FORMAT(showDate, '%a, %b %D') AS FORMATTED_DATE FROM $DB_table WHERE id=$id";
$the_date = mysql_query($get_date);
if ($daterow = mysql_fetch_array($the_date)) {
printf("%s @ %s, %s (<a href=\"main.php?scene=0&show=%s\">More Info</a>) \n<br>", $daterow["FORMATTED_DATE"], $myrow["showCity"], $myrow["showState"], $myrow["id"]);
}

That code is probably a load of rubbish, dunno if it would work as is.. but what I'm trying to show.. is the date format done after the initial selection, that way you still get the ORDER ASC done by the proper date format.. if you see what I mean.

I know what I mean if know one else does

Regards

Lee