I'm trying to select a value from the database, it works perfectly if everything is just numbers - but fails to work if there are letters involved. This is in OpenCart.
My column is called campaign_code , its type is VARCHAR.
Here is the command...
$campaign_id = $this->db->query("SELECT campaign_id FROM " . DB_PREFIX . "campaign WHERE campaign_code = ". $this->db->escape($campaign_code));
When $campaign_code equals any number it's fine. It selects as expected.
When its letters or a combination of numbers or letters I get this error...
Uncaught exception 'ErrorException' with message 'Error: Unknown column '85f' in 'where clause'<br />Error No: 1054<br />SELECT campaign_id FROM oc_campaign WHERE campaign_code = 85f' in C:\xampp\htdocs\microcharity\system\database\mysqli.php:40
Thank you.
While using an integer in where clause, you can avoid quotes but while using a string you have to enclose it in single quotes.
For integer -
$campaign_id = $this->db->query("SELECT campaign_id FROM '".DB_PREFIX."' WHERE campaign_code = ".$this->db->escape($campaign_code)."");
For string -
$campaign_id = $this->db->query("SELECT campaign_id FROM '".DB_PREFIX."' WHERE campaign_code = '".$this->db->escape($campaign_code)."'");
You almost answer your own question by providing all the details.
Yes, when you provide WHERE col = 1 it interprets 1 as integer, an searches for this integer. When you provide WHERE col = something it searches for column something i.e. WHERE id = id2 (this is used when you want the results where the values from one column are equal to other column). If you want to search with strings, you need quotes like WHERE col = 'you string'
If you google your exception, you might find the answer unknown column in where clause