Order by 2 numbers?

[arbitter]

Hello there,

 

I have a table with dates in one column. Now, these dates are eg '8', and another one could be '24'. When I order it using

$row=mysql_fetch_array(mysql_query("SELECT * FROM activities WHERE month='9' ORDER BY day ASC"),MYSQL_ASSOC);

, the 24 get's displayed before the 8. This is logic, but nevertheless, how can I change this?

 

Because when I use this method to order things by id, it does work corectly.

[jdavidbakr]

Are your dates being stored as a numeric or string type?  I think if you're using something like VARACHAR 26 will come before 8 (because it's sorting by character, not number).  Change your row to something like TINYINT and it should sort as you expect.

[Pikachu2000]

The BEST thing to do would be to store dates in the appropriate DATE or DATETIME field type. Lacking that, either change the fields to TINYINT as suggested above, or typecast the field that is ORDERed BY.

 

"SELECT * FROM activities WHERE month='9' ORDER BY CAST( day AS UNSIGNED INT) ASC"

[arbitter]

Ahaa I see. That could indeed be the problem, because it is varchar.

Other problem is though that it can contain multiple days; and those get stored like this: 'dd - dd', so that's why I made it varchar...

[Pikachu2000]

Storing multiple values in one field is, in general, a bad idea.

[arbitter]

Hm probably, but I find i tannoying to have an extra field because then you need to keep that in mind... But I guess that's the only solution? Adding a begindate and an enddate field seperately?

[jdavidbakr]

Hm probably, but I find i tannoying to have an extra field because then you need to keep that in mind... But I guess that's the only solution? Adding a begindate and an enddate field seperately?

 

Depends on what all you need it to do - but yes, that's likely what you'll want to do.  You can have the enddate field set to NULL if there is no range.  I did a calendar app once that had events on a day or a range of days, and did just that - if the end date was null, it was on a single date, otherwise it spanned from the start date to the end date.

[Pikachu2000]

Yes, in DATE or DATETIME format would be best, so you can then make use of the many native date and time manipulation functions that MySQL has to offer.