{"id":1286,"date":"2022-08-30T15:15:16","date_gmt":"2022-08-30T15:15:16","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2013\/11\/10\/mysql-unknown-column-in-where-clause-for-simple-select-statement-solved-collection-of-common-programming-errors\/"},"modified":"2022-08-30T15:15:16","modified_gmt":"2022-08-30T15:15:16","slug":"mysql-unknown-column-in-where-clause-for-simple-select-statement-solved-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2022\/08\/30\/mysql-unknown-column-in-where-clause-for-simple-select-statement-solved-collection-of-common-programming-errors\/","title":{"rendered":"MySQL Unknown column in where clause for simple select statement (SOLVED)-Collection of common programming errors"},"content":{"rendered":"

I have a very basic select statement that is causing a column unknown error. The problem with the query happens when I try to use a character instead of just numbers in the variable. Wondering if it has anything to do with Collation.<\/p>\n

Here’s what I have so far:<\/p>\n

$titleno=$_REQUEST['title_no'];\n$titleno=mysql_real_escape_string($titleno);\n$titleno = utf8_decode($titleno); \/\/tried without this before but didn't work\n$query=\"SELECT * FROM `Titles` WHERE `title-no` = '\".$titleno.\"'\"; \n\/\/tried various versions of this query - left it as single quotes as that seems to be the correct way. This only fails when a character is entered. Numbers work fine.  \n\necho \"query - 
 $query 
\";    \n$get_title_result=mysql_query($query) or die(mysql_error());\n\/\/here I get the unknown column name error - MySQL treats the titleno as the column name \n<\/code><\/pre>\n
Echo output:<\/p>\n
SELECT * FROM `Titles` WHERE `title-no` = '1234566d' \nUnknown column '1234566d' in 'where clause'\n<\/code><\/pre>\n
If I didn’t use the ‘d’ in title-no, it works fine….Also, I tried a different column name that doesn’t have the hyphen and still get the same behavior. The DB defines collation for title-no as latin1_swedish_ci. (This problem doesn’t occur when I paste the query into mysqladmin)<\/p>\n
Here’s the table definition:<\/p>\n
CREATE TABLE `Titles` (  \n `id` int(11) NOT NULL auto_increment,  \n `title-no` varchar(15) NOT NULL,  \n  UNIQUE KEY `title-no` (`title-no`),  \n  KEY `id` (`id`)  \n  ) ENGINE=MyISAM  \n  AUTO_INCREMENT=9090949 DEFAULT CHARSET=latin1 AUTO_INCREMENT=9090949 ;\n<\/code><\/pre>\n
RESOLVED:<\/strong> The issue was not with this query. It was with a subsequent query. I was confused because I was only echoing this query. My bad. Thank you all for your support! \ud83d\ude42<\/p>\n
    \n
  1. \n
    Try with:<\/p>\n
    $query = \"SELECT * FROM Titles WHERE `Titles`.`title-no` = '\" . $titleno . \"'\"; \n<\/code><\/pre>\n<\/li>\n
  2. \n
    Here is a quick conversion to statement-based query (which is using MySQLi, adapt as necessary, your code or this example). The assumption is that the underlying prepared statement engine knows<\/em> that you cannot specify a column name with placeholders in a prepared statement, so it should be passing it correctly (here’s hoping \ud83d\ude42<\/p>\n
    $titleno=$_REQUEST['title_no'];\n$statement=mysqli_prepare($your_mysqli_link, \"SELECT `id` FROM `Titles` WHERE `title-no` = ?\"); \nmysqli_stmt_bind_param($statement, 's', $titleno);\nmysqli_stmt_execute($statement);\nmysqli_stmt_bind_result($statement, $found_id);\nmysqli_stmt_fetch($statement);\necho \"found id: $found_id\";\n<\/code><\/pre>\n<\/li>\n<\/ol>\n
    Originally posted 2013-11-10 00:14:23. <\/small><\/p>","protected":false},"excerpt":{"rendered":"
    I have a very basic select statement that is causing a column unknown error. The problem with the query happens when I try to use a character instead of just numbers in the variable. Wondering if it has anything to do with Collation. Here’s what I have so far: $titleno=$_REQUEST[‘title_no’]; $titleno=mysql_real_escape_string($titleno); $titleno = utf8_decode($titleno); \/\/tried […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1286","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1286","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=1286"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/1286\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=1286"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=1286"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=1286"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}