MySQL: Unknown column where clause error

Question

I have a PHP script, and for some reason mysql continues to process the value to select / insert as a column. Here is an example of my sql query:

$query = mysql_query("SELECT * FROM tutorial.users WHERE (uname=`".mysql_real_escape_string($username)."`)") or die(mysql_error()); 

It turns into:

 SELECT * FROM tutorial.users WHERE (uname=`test`) 

Mistake:

Unknown column 'test' in 'where item "

I also tried:

 SELECT * FROM tutorial.users WHERE uname=`test` 

Answer 1

In MySql, backreferences indicate that the identifier is the name of the column. (Other RDBMSs use parentheses or double quotes for this).

So your query: “Give me all the rows where the value in the column with the name“ uname ”is equal to the value in the column with the name“ test. ”But since your table does not have a column called test, you get the error message that you saw .

Replace backticks with single quotes.

Answer 2

Weird? How so? He says exactly what is wrong. There is no "test" column in the table. Do you have the right table? 'tutorial.users'? Are you sure the table is not named differently? Perhaps you wanted to do

 SELECT * from users WHERE uname = 'test'; 

You need to refer only to the table name, not the database. Assuming the database is called tutorial

Answer 3

Example:

 $uname = $_POST['username']; $sql="SELECT * FROM Administrators WHERE Username LIKE '$uname'" 

Note the single quotes around $ uname. When you ping, this is the output -

 SELECT * FROM Administrators WHERE Username LIKE 'thierry' 

However, if you skip the quote around the $ uname variable in your request, this is what you get -

 SELECT * FROM Administrators WHERE Username LIKE thierry 

On a MySQL server, the two queries are different. thierry is an input string and is correctly encapsulated in quotation marks, where, as in the second query, this is not the case, which causes an error in MySQL.

Hope this helps and sorry my englis, which is not very good.

Answer 4

I had the same problem and it turned out to be a typo. My error message:

 Unknown column 'departure' in 'where clause' 

I checked this column in my table, and it turned out that I wrote it as "defect" and "NO" in the table, so I got an error message.

Subsequently, I changed my request to:

 Unknown column 'depature' in 'where clause' 

and it worked!

So it’s clear that I advise you to double check the spelling of the column name.

Hope this helps.

Answer 5

I also ran into the problem "Unknown column in where where" when executing the following from the linux (bash) command line.

 mysql -u support -pabc123 -e 'select * from test.sku where dispsku='test01' ; ' 

Here is what i got

 ERROR 1054 (42S22) at line 1: Unknown column 'test01' in 'where clause' 

I had to replace the single quotation mark test01 with the double quotation mark "test01". It worked for me. There is a difference in how and how you execute sql queries.

There is a slight difference when assigning a value to a variable in a script and later using this variable in the sql statement that the script must be executed.

Assuming the variable

 var=testing 

and you want to pass this value from script to mysql, then single quotes work.

 select '$var' 

Therefore, different engines can evaluate inverse and quotation marks in different ways.

This is my query that worked from linux command line.

 mysql -u support -pabc123 -e 'select * from test.sku where dispsku="test01" ; '