Unknown column in order clause

Question

#1054 - Unknown column 'default_ps_products.manufacturer_id' in 'order clause'

Why am I getting the above error with the statement below it works fine without the p in the statement and I am not using an order clause?

SELECT * FROM `default_ps_products` p WHERE p.`manufacturer_id` = 2

Answer 1

To solve this use SELECT p.* FROM instead of SELECT * FROM.

The reason is that phpMyAdmin is adding an ORDER BY to your query for the first column in the results grid. Because of the alias, the code that does this fails.

This issue reproduces on phpMyAdmin 4.0.6. I don't know the status on the latest 4.2.5

Answer 2

Since you posted a partial query this wasn't obvious from the start, but your full query makes it clear;

SELECT *
FROM default_ps_products
WHERE manufacturer_id=2
ORDER BY `default_ps_products`.`manufacturer_id` ASC
LIMIT 0, 30

When you add an alias to default_ps_products table in the select, you can't selectively use the alias only in the WHERE clause, you'll also need to change the ORDER BY to use the same alias. The full query should in other words be;

SELECT *
FROM default_ps_products p
WHERE p.manufacturer_id=2
ORDER BY p.`manufacturer_id` ASC
LIMIT 0, 30