CODEWITHSUNDEEP
mysqlphpmyadmin
Jess McKenzie
Jess McKenzie

Reputation: 8385

Unknown column in order clause

#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

Upvotes: 13

Views: 56092

Answers (6)

luky
luky

Reputation: 2370

phpmyadmin started showing this error suddenly on one table. only fix was to rename column id to id2 then back. even deleting the table and making new copy didn't help

Upvotes: 0

Harvey
Harvey

Reputation: 49

when use @Query(nativeQuery = true), should use underline format ,just like this "Sort.by(Sort.Direction.DESC, "update_time")))", else should use camel properties in entity ,like this "Sort.by(Sort.Direction.DESC, "updateTime")))"

Upvotes: 0

innovative kundan
innovative kundan

Reputation: 631

Your query is fine. there is not any error when i run this query. there is nothing wrong with query.

SELECT * FROM default_ps_products AS p WHERE p.manufacturer_id = 2 it working fine.:)

Upvotes: 1

Guest
Guest

Reputation: 31

Open your phpmyadmin. Click on your selected database. Now you have a list of all tables on right side. Click on structure of default_ps_products table. Now you see a structure of it. Now Click on SQL tab and execute query as 'SELECT * FROM default_ps_products ORDER BY '. Once you execute this query, Now resolve your problem.

Upvotes: 3

Yehuda Adler
Yehuda Adler

Reputation: 121

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

Upvotes: 12

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

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

Upvotes: 8

Related Questions