MySql Select statement help please

admin · 03-07-2007, 03:39:05 PM

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:

Code:

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

monaghan · 03-07-2007, 03:44:58 PM

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 · 03-07-2007, 03:58:43 PM

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 · 03-07-2007, 04:00:45 PM

Quote:

Originally Posted by monaghan

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 · 03-07-2007, 04:04:31 PM

Quote:

Originally Posted by bb99

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 · 03-07-2007, 04:08:51 PM

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

monaghan · 03-07-2007, 04:10:31 PM

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 · 03-07-2007, 04:11:46 PM

Quote:

Originally Posted by bb99

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 · 04-07-2007, 03:52:17 PM

Did you get it working to your liking?

admin · 04-07-2007, 06:05:16 PM

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.

03-07-2007, 03:39:05 PM	#1 (permalink)
admin Administrator Join Date: Jun 2004 Posts: 8,601 No Classified Ratings Domain Trader Rating: (100% / 1)	MySql Select statement help please 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: Code: $sql = mysql_query("SELECT * FROM `item` ORDER BY `Month` ASC"); __________________ Domain Appraisal \| Domain Scripts \| Domain Software\| link to Acorn Domains

03-07-2007, 03:44:58 PM	#2 (permalink)
monaghan Join Date: May 2007 Posts: 1,148 No Classified Ratings Domain Trader Rating: (100% / 7)	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? __________________ Alex Monaghan - Drop Catch & Drop Lists - PM for details Online Accounting\|Pine Furniture\|Barbie

03-07-2007, 03:58:43 PM	#3 (permalink)
bb99 Join Date: Mar 2005 Posts: 1,547 No Classified Ratings Domain Trader Rating: (100% / 4)	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. __________________ Droppr - finely tuned .uk dropcatching for Nominet members Gazebo \| Flyers, Postcards \| Tree Surveys \| Dent Repairs Leeds \| Poster Printing

03-07-2007, 04:08:51 PM	#6 (permalink)
bb99 Join Date: Mar 2005 Posts: 1,547 No Classified Ratings Domain Trader Rating: (100% / 4)	And remember to backup your data first - don't trust forum users not to break your data __________________ Droppr - finely tuned .uk dropcatching for Nominet members Gazebo \| Flyers, Postcards \| Tree Surveys \| Dent Repairs Leeds \| Poster Printing

03-07-2007, 04:10:31 PM	#7 (permalink)
monaghan Join Date: May 2007 Posts: 1,148 No Classified Ratings Domain Trader Rating: (100% / 7)	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) __________________ Alex Monaghan - Drop Catch & Drop Lists - PM for details Online Accounting\|Pine Furniture\|Barbie

04-07-2007, 03:52:17 PM	#9 (permalink)
monaghan Join Date: May 2007 Posts: 1,148 No Classified Ratings Domain Trader Rating: (100% / 7)	Did you get it working to your liking?

04-07-2007, 06:05:16 PM	#10 (permalink)
admin Administrator Join Date: Jun 2004 Posts: 8,601 No Classified Ratings Domain Trader Rating: (100% / 1)	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. __________________ Domain Appraisal \| Domain Scripts \| Domain Software\| link to Acorn Domains

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Domain Name Community	Replies	Last Post
Domain name. How to select the right domain names - 24-7PressRelease.com (press release)	RSS	Domain Name News	0	04-06-2007 10:59:12 AM
Domain name. How to select the right domain names - 24-7PressRelease.com (press release)	RSS	Domain Name News	0	31-05-2007 07:59:07 AM
How to select a domain name for your company - Honolulu Star-Bulletin	RSS	Domain Name News	0	26-03-2007 12:59:16 PM
bounce.co.uk - Public Statement from Nominet	Beasty	Domain Name Disputes	11	28-09-2006 02:28:30 PM
moving mysql from one server to another	SecNam	General Board	1	07-08-2005 12:29:32 PM