...

View Full Version : ORDER BY not working



hunter1611
06-14-2011, 08:47 PM
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.



$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++;
}
http://stokestospain.com/table.gif
http://stokestospain.com/Untitled-4.gif

Old Pedant
06-14-2011, 10:32 PM
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.



$query4="SELECT * FROM letters ORDER BY `year`";


MONTH is the same, so you may want to do

$query4="SELECT * FROM letters ORDER BY `year`,`month`";

hunter1611
06-14-2011, 11:09 PM
Didn't work. I just now even tried renaming the columns and it still won't order them.

Old Pedant
06-15-2011, 12:36 AM
Dunno what to tell you.

I just tried it myself. Recreated a close approximation of your table. Tried it without the back ticks, even.


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.

hunter1611
06-15-2011, 02:14 AM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum