Sorting help

Anon

Hey guys, just having a little trouble with some php code of mine. Im sorting a sql database using date but it seems to use a sorting of both date and alphbetical, this can be seen here ->

http://frozenpea.net/date.php

Feburary and March are rihgt but april has been put up top for some reason. The code is as follows:

<?
$db = mysql_connect('localhost','user','password');
mysql_select_db("db", $db);

$sql="SELECT * FROM CDS ORDER BY Date";
$result = mysql_query($sql,$db);

$num = mysql_num_rows($result);
$cur = 1;

echo "<TABLE cellSpacing=1 cellPadding=2 width=\60%\" border=0 bordercolor=\"#000000\" bgcolor=\"#000000\"><TBODY>";

while ($num >= $cur) {

$row = mysql_fetch_array($result);
$artist = $row["Artist"];
$cd = $row["CD Name"];
$date = $row["Date"];

echo "<TR bgcolor=\"#6F9986\"><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\">$artist</font></td><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\">$cd</font></td></font></td><td><font face=\"Verdana, Arial, Helvetica, sans-serif\" size=\"2\">$date</font></td>";

$cur++;
}

echo "</TBODY></table>";

If anyone can help me out and edit my code so its lists only in date, ie april down the bottom as well, it would be greatly appreciated. Thanks a lot,

Regards,

Stewart

Anon

How are your dates being stored? 'January', 'February', 'March'? If you're just storing them as strings then MySQL will sort them as strings: Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep.

Anon

You are right,they are stored as text. If I change it to text will I have to change how the values are entered into my DB? if I convert to date now will it corrupt the current Text values?

Anon

Mmhm.

Suggest the following: create a new column - call it "date_temp" or the suchlike, of a suitable date/time type (I'm not familiar enough with MySQL to know what you've got available).

Then write a wee script for yourself that will go through each row, taking the existing date text, making a real date out of it, then inserting it back into date_temp.

Run that, check, delete the old date column and rename the date_temp column to the old name.

Once you've done that, you'll need to rewrite the bits of code where you read or manipulate that column to take into account the change in type.

Sounds like a fair bit of work, I know. But trust me, it's much less painful to do it in one go at the start than it would be to have to constantly convert things back and forth on the fly. That's why I didn't whack out a usort() callback function.

Anon

that sounds really nasty, im a newby at php and was a mircale for me to even generate the code ive already generated! Can there not be easy easier way?