Domain Manage

MySql Select statement help please

Discussion in 'Website Design' started by admin, Jul 3, 2007.

Thread Status:
Not open for further replies.
  1. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,083
    Likes Received:
    115
    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"); 
     
  2. Domain Forum

    Acorn Domains Elite Member

    Joined:
    1999
    Messages:
    Many
    Likes Received:
    Lots
     
  3. monaghan United Kingdom

    monaghan Moderator Staff Member

    Joined:
    May 2007
    Posts:
    1,993
    Likes Received:
    45
    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?
     
  4. bb99 United Kingdom

    bb99 Well-Known Member

    Joined:
    Mar 2005
    Posts:
    1,598
    Likes Received:
    38
    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.
     
  5. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,083
    Likes Received:
    115
    How do I do that? Sorry I am a novice at this
     
  6. Jeewhizz

    Jeewhizz Well-Known Member

    Joined:
    May 2006
    Posts:
    1,177
    Likes Received:
    18
    that is going to be the easiest way - 12 queries will update it all :)
     
  7. bb99 United Kingdom

    bb99 Well-Known Member

    Joined:
    Mar 2005
    Posts:
    1,598
    Likes Received:
    38
    And remember to backup your data first - don't trust forum users not to break your data :)
     
  8. monaghan United Kingdom

    monaghan Moderator Staff Member

    Joined:
    May 2007
    Posts:
    1,993
    Likes Received:
    45
    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)
     
  9. monaghan United Kingdom

    monaghan Moderator Staff Member

    Joined:
    May 2007
    Posts:
    1,993
    Likes Received:
    45
    Don't trust anyone, not just forum users :)
     
  10. monaghan United Kingdom

    monaghan Moderator Staff Member

    Joined:
    May 2007
    Posts:
    1,993
    Likes Received:
    45
    Did you get it working to your liking?
     
  11. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,083
    Likes Received:
    115
    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.
     
  12. monaghan United Kingdom

    monaghan Moderator Staff Member

    Joined:
    May 2007
    Posts:
    1,993
    Likes Received:
    45
    I did suggest adding a field rather than changing the existing one :)
     
  13. tifosi United Kingdom

    tifosi Well-Known Member

    Joined:
    Oct 2004
    Posts:
    3,128
    Likes Received:
    45
    Might get a more concise response if you put the table schema here

    S
     
  14. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,083
    Likes Received:
    115
    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

    Code:
    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

    Code:
    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?
     
  15. monaghan United Kingdom

    monaghan Moderator Staff Member

    Joined:
    May 2007
    Posts:
    1,993
    Likes Received:
    45
    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.
     
  16. admin Spain

    admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,083
    Likes Received:
    115
    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.
     
  17. monaghan United Kingdom

    monaghan Moderator Staff Member

    Joined:
    May 2007
    Posts:
    1,993
    Likes Received:
    45
    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.
     
Thread Status:
Not open for further replies.

Share This Page