MySql Select statement help please

[Admin]

I am working on DomainPrices.co.uk and want to sort the domain list by month, trouble is, when I do so, the SQL reads it as text and sorts it alphabetically and not by month as required. So I guess i need to change how I enter the month data for this to work, what's the best way?

This is the SQL query code I am using:

$sql = mysql_query("SELECT * FROM `item` ORDER BY `Month` ASC");

 

[monaghan]

If Month is a string, then the sort is working correctly, why not store Month as an integer or have a generic datetime rather than a seperate month field?

 

[bb99]

You could do it two ways:

a) Change all the months to numbers (but you can do it relatively quickly by saying " update 'item' set 'month' = '01' where 'month' = 'January' " etc).

b) Setup another table with two fields - monthnumber and monthtext - so it's "01", "January" blah blah "12", "December" and then do a cleverer query statement that links the month text to the number in your second table.

 

[Admin]

why not store Month as an integer or have a generic datetime rather than a seperate month field?

How do I do that? Sorry I am a novice at this

 

[Jeewhizz]

a) Change all the months to numbers (but you can do it relatively quickly by saying " update 'item' set 'month' = '01' where 'month' = 'January' " etc).

that is going to be the easiest way - 12 queries will update it all

 

[bb99]

And remember to backup your data first - don't trust forum users not to break your data

 

[monaghan]

Without seeing your code or schema it's diffucult to give specific advise, however I'd personally add a datetime field (saledate) to hold your date then do something like

select name, monthname(saledate), other_fields_you_want from item order by month(saledate)

 

[monaghan]

And remember to backup your data first - don't trust forum users not to break your data

Don't trust anyone, not just forum users

 

[monaghan]

Did you get it working to your liking?

 

[Admin]

I managed to trash my data by changing the field type to date, it zapped all the field contents grrrrr I do have a backup but I need to do some data cleansing anyway, to make the categories more consistent.

I am still playing with it now.

 

[monaghan]

I managed to trash my data by changing the field type to date, it zapped all the field contents grrrrr I do have a backup but I need to do some data cleansing anyway, to make the categories more consistent.

I am still playing with it now.

I did suggest adding a field rather than changing the existing one

 

[tifosi]

Might get a more concise response if you put the table schema here

S

 

[Admin]

Sorted now, thanks to all for input.

I made the existing field a DATE format field
Added a Month and Year field

Manually edited every DATE field (didn't take long actually) then used this code to populate the 2 new fields.

Month

update item set Month = 'January' where month(Date) = 01;
update item set Month = 'February' where month(Date) = 02;
update item set Month = 'March' where month(Date) = 03;
update item set Month = 'April' where month(Date) = 04;
update item set Month = 'May' where month(Date) = 05;
update item set Month = 'June' where month(Date) = 06;
update item set Month = 'July' where month(Date) = 07;
update item set Month = 'August' where month(Date) = 08;
update item set Month = 'September' where month(Date) = 09;
update item set Month = 'October' where month(Date) = 10;
update item set Month = 'November' where month(Date) = 11;
update item set Month = 'December' where month(Date) = 12;

and Year

update item set Year = 2006 where year(Date) = 2006;
update item set Year = 2007 where year(Date) = 2007;

Now I can show just 2007 records on the homepage plus enter sales I find at any time later and have them appear in the right place.



Any other suggestions?

 

[monaghan]

OK, maybe I'm just being a sad DBA, but why duplicate the data? You have the month & year in the date, just apply a simple filter in your select and you've got all the data in a single field but presented in any way you see fit.

Also if you use the SQL date functions you can easily create a query that will always pull out this year's data or the last month's data or the last 6 months or whatever combination you want.

 

[Admin]

OK, maybe I'm just being a sad DBA, but why duplicate the data? You have the month & year in the date, just apply a simple filter in your select and you've got all the data in a single field but presented in any way you see fit.

Also if you use the SQL date functions you can easily create a query that will always pull out this year's data or the last month's data or the last 6 months or whatever combination you want.

Sounds great, I just dont have the SQL knowledge to do it easily, plus on the site, I only want to show the Month not the whole date string which is held as "2007/06/01" - I'd just want to show "June", that's why I've done it this way.

 

[monaghan]

I did give you a query showing how you could do it The method you're using will work, but adds complexity and is not that flexible should you want to be more creative.

If you want help, let me know.