• Upgrade
  • Advertise
    • Enjoy unlimited access to all forum features for FREE! Optional upgrade available for extra perks.

    MySql Select statement help please

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

    Thread Status:
    Not open for further replies.
    1. Admin

      Admin Administrator Staff Member

      Joined:
      Jun 2004
      Posts:
      11,134
      Likes Received:
      468
      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");
       
      Admin, Jul 3, 2007
      Admin, Jul 3, 2007
      #1
    2. monaghan United Kingdom

      monaghan Well-Known Member

      Joined:
      May 2007
      Posts:
      2,126
      Likes Received:
      78
      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?
       
      monaghan, Jul 3, 2007
      monaghan, Jul 3, 2007
      #2
    3. bb99 United Kingdom

      bb99 Well-Known Member

      Joined:
      Mar 2005
      Posts:
      1,614
      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.
       
      bb99, Jul 3, 2007
      bb99, Jul 3, 2007
      #3
    4. Admin

      Admin Administrator Staff Member

      Joined:
      Jun 2004
      Posts:
      11,134
      Likes Received:
      468
      How do I do that? Sorry I am a novice at this
       
      Admin, Jul 3, 2007
      Admin, Jul 3, 2007
      #4
    5. Jeewhizz

      Jeewhizz Well-Known Member

      Joined:
      May 2006
      Posts:
      1,174
      Likes Received:
      18
      that is going to be the easiest way - 12 queries will update it all :)
       
      Jeewhizz, Jul 3, 2007
      Jeewhizz, Jul 3, 2007
      #5
    6. bb99 United Kingdom

      bb99 Well-Known Member

      Joined:
      Mar 2005
      Posts:
      1,614
      Likes Received:
      38
      And remember to backup your data first - don't trust forum users not to break your data :)
       
      bb99, Jul 3, 2007
      bb99, Jul 3, 2007
      #6
    7. monaghan United Kingdom

      monaghan Well-Known Member

      Joined:
      May 2007
      Posts:
      2,126
      Likes Received:
      78
      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, Jul 3, 2007
      monaghan, Jul 3, 2007
      #7
    8. monaghan United Kingdom

      monaghan Well-Known Member

      Joined:
      May 2007
      Posts:
      2,126
      Likes Received:
      78
      Don't trust anyone, not just forum users :)
       
      monaghan, Jul 3, 2007
      monaghan, Jul 3, 2007
      #8
    9. monaghan United Kingdom

      monaghan Well-Known Member

      Joined:
      May 2007
      Posts:
      2,126
      Likes Received:
      78
      Did you get it working to your liking?
       
      monaghan, Jul 4, 2007
      monaghan, Jul 4, 2007
      #9
    10. Admin

      Admin Administrator Staff Member

      Joined:
      Jun 2004
      Posts:
      11,134
      Likes Received:
      468
      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.
       
      Admin, Jul 4, 2007
      Admin, Jul 4, 2007
      #10
    11. monaghan United Kingdom

      monaghan Well-Known Member

      Joined:
      May 2007
      Posts:
      2,126
      Likes Received:
      78
      I did suggest adding a field rather than changing the existing one :)
       
      monaghan, Jul 4, 2007
      monaghan, Jul 4, 2007
      #11
    12. tifosi United Kingdom

      tifosi Well-Known Member

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

      S
       
      tifosi, Jul 4, 2007
      tifosi, Jul 4, 2007
      #12
    13. Admin

      Admin Administrator Staff Member

      Joined:
      Jun 2004
      Posts:
      11,134
      Likes Received:
      468
      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?
       
      Admin, Jul 4, 2007
      Admin, Jul 4, 2007
      #13
    14. monaghan United Kingdom

      monaghan Well-Known Member

      Joined:
      May 2007
      Posts:
      2,126
      Likes Received:
      78
      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.
       
      monaghan, Jul 4, 2007
      monaghan, Jul 4, 2007
      #14
    15. Admin

      Admin Administrator Staff Member

      Joined:
      Jun 2004
      Posts:
      11,134
      Likes Received:
      468
      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.
       
      Admin, Jul 5, 2007
      Admin, Jul 5, 2007
      #15
    16. monaghan United Kingdom

      monaghan Well-Known Member

      Joined:
      May 2007
      Posts:
      2,126
      Likes Received:
      78
      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.
       
      monaghan, Jul 5, 2007
      monaghan, Jul 5, 2007
      #16
    Thread Status:
    Not open for further replies.

    ▲