Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2011
    Posts
    51
    Thanks
    18
    Thanked 0 Times in 0 Posts

    ORDER BY not working

    I know I'm probably going to feel really stupid once someone shows me what I'm doing wrong, but I'm utterly stumped so I'm giving up and asking for help.

    I'm running an ordinary query and trying to order the results by one of the columns. Done it a thousand times and never had an issue. But for some reason, the order by in the query just won't work. It connects to the database and pulls down the data just fine, it just won't order it. The data prints out with the default order. I've tried using different columns in the table but nothing ever changes. Below I'll post a copy of the mysql table and my php query code. Let me know if you need to see anything else.


    PHP Code:
      $query4="SELECT * FROM letters ORDER BY year";
      
    $result4=mysql_query($query4);
      
    $num4=mysql_numrows($result4);
      
    $i=0;
      while (
    $i $num4) {
        
    $l_name1=mysql_result($result4,$i,"file");
        
    $month1=mysql_result($result4,$i,"month");
        
    $year1=mysql_result($result4,$i,"year");
        
    $letter_name "$month1 $year1";
        
    $letter_ref[$l_name1] = $letter_name;
      
    $i++;
      } 


  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,695
    Thanks
    80
    Thanked 4,511 Times in 4,475 Posts
    YEAR is a keyword/builtin function in MySQL. So maybe you just need to put back ticks around the name of the field?

    Back tick shares the keyboard with the ~ key.

    Code:
    $query4="SELECT * FROM letters ORDER BY `year`";
    MONTH is the same, so you may want to do
    Code:
    $query4="SELECT * FROM letters ORDER BY `year`,`month`";

  • #3
    New Coder
    Join Date
    Jan 2011
    Posts
    51
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Didn't work. I just now even tried renaming the columns and it still won't order them.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,695
    Thanks
    80
    Thanked 4,511 Times in 4,475 Posts
    Dunno what to tell you.

    I just tried it myself. Recreated a close approximation of your table. Tried it without the back ticks, even.
    Code:
    mysql> describe letters;
    +-----------+--------------+------+-----+---------+----------------+
    | Field     | Type         | Null | Key | Default | Extra          |
    +-----------+--------------+------+-----+---------+----------------+
    | id        | int(11)      | NO   | PRI | NULL    | auto_increment |
    | file      | varchar(255) | YES  |     | NULL    |                |
    | month     | varchar(100) | YES  |     | NULL    |                |
    | num_month | int(11)      | YES  |     | NULL    |                |
    | year      | int(4)       | YES  |     | NULL    |                |
    +-----------+--------------+------+-----+---------+----------------+
    5 rows in set (0.18 sec)
    
    mysql> select * from letters;
    +----+------+---------+-----------+------+
    | id | file | month   | num_month | year |
    +----+------+---------+-----------+------+
    |  1 | xyz  | jan2011 |         1 | 2011 |
    |  2 | abc  | feb2011 |         2 | 2011 |
    |  3 | yes  | nov2010 |        11 | 2010 |
    |  4 | oof  | may2011 |         5 | 2011 |
    +----+------+---------+-----------+------+
    4 rows in set (0.00 sec)
    
    mysql> select * from letters order by year;
    +----+------+---------+-----------+------+
    | id | file | month   | num_month | year |
    +----+------+---------+-----------+------+
    |  3 | yes  | nov2010 |        11 | 2010 |
    |  1 | xyz  | jan2011 |         1 | 2011 |
    |  2 | abc  | feb2011 |         2 | 2011 |
    |  4 | oof  | may2011 |         5 | 2011 |
    +----+------+---------+-----------+------+
    4 rows in set (0.00 sec)
    Presto. It worked.

    I even went back and changed my VARCHAR fields to TEXT, to exactly match yours. No diff. Worked fine.

  • Users who have thanked Old Pedant for this post:

    hunter1611 (06-15-2011)

  • #5
    New Coder
    Join Date
    Jan 2011
    Posts
    51
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Alright. Sometimes I wish I could for once have a simple problem. But, I think I'm starting to figure out what's going on. I just thought to print the array that all of the values are being sent to and it's actually printing out in the right order, so I'm on my way to finding the problem....since you showed me that it wasn't a mysql issue. Thanks.

    Later Edit: Yep, figured out the problem. For some reason, I was loading everything from the folder with the actual files into an array and I also had my array with the date information from the database. I was using the first array and that's what was messing me up. Thanks for you help.
    Last edited by hunter1611; 06-15-2011 at 02:38 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •