ORDER BY not working in MySQL 5.1

Question

Hello, guys.

I have a problem with sql query in php:

select
    user, name, outlet, switch, port, vlan, mac, status
from access where 
    user like '%'
    and name like '%'
    and outlet like '%'
    and switch like '%'
    and port like '%'
    and vlan like '%'
    and mac like '%'
    and status like '%'
order by 'user';

When running query on MySQL client version: 5.1.36 query doesn't work totally (ORDER BY won't work), however when running SAME query on MySQL client version: 4.1.13, ORDER BY works!

Any suggestions?

I have checked nearly all manuals about ORDER BY, WHERE, LIKE commands, but no result. No mention about version differences, etc..

Guys, thanks!

Answer 1

I think what you need is:

SELECT `user`,`name`,`outlet`,`switch`,`port`,`vlan`,`mac`,`status` 
FROM `access`
WHERE `user` like '%' 
    AND `name` like '%'
    AND `outlet` like '%'
    AND `switch` like '%'
    AND `port` like '%'
    AND `vlan` like '%'
    AND `mac` like '%'
    AND `status` like '%' 
ORDER BY `user`;

Though I don't understand your WHERE clause. It doesn't filter on any of the fields. EDIT; some of your column names (user, name, port and status) could be MySQL keywords. Try enclosing them in grave accents (`) (I added them to my post as well).

Answer 2

You have to remove the quotes from user in the ORDER BY clause. This is what is causing the ORDER BY not working as expected, because you can use any expression in the ORDER BY clause, and the 'user' in quotes is being considered an expression (constant) instead of a column name.

Test case (MySQL 5.1.45):

CREATE TABLE tb (id int);

INSERT INTO tb VALUES (5);
INSERT INTO tb VALUES (1);
INSERT INTO tb VALUES (4);
INSERT INTO tb VALUES (2);
INSERT INTO tb VALUES (3);

SELECT * FROM tb ORDER BY 'id';
+------+
| id   |
+------+
|    5 |
|    1 |
|    4 |
|    2 |
|    3 |
+------+
5 rows in set (0.00 sec)

SELECT * FROM tb ORDER BY id;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)