Search Multiple Items in Multiple columns – This article will take you through the common SQL errors that you might encounter while working with php, sql, mysql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I have 6 select items in a form. I want to search those 6 in MYSQL DB. I can retrieve results if I use only one, like:
$result = mysql_query("SELECT * FROM wsinmuebles WHERE Property_Type LIKE '%{$_POST['Property_Type']}%'");
But when I try more, I get no results!
$result = mysql_query("SELECT * FROM wsinmuebles WHERE
Property_Type LIKE '%{$_POST['Property_Type']}%' AND
Estado LIKE '%{$_POST['Estado']}%' AND
Ciudad LIKE '%{$_POST['Ciudad']}%' AND
Urbanizacion LIKE '%{$_POST['Urbanizacion']}%' AND
Operacion LIKE '%{$_POST['Operacion']}%' AND
Precio_bsf LIKE '%{$_POST['Precio_bsf']}%'");
This comes from a form by the POST method.
What I need is to look for Property_Type, Estado, Ciudad, Urbanizacion, Operacion and Precio_bsf variables in MYSQL DB, and receive only the results that match all those values.
Solution :
First, escape the post values using mysql_real_escape_string (Link) to avoid any SQL injection attacks and also issues with the data having ‘ characters.
Second echo the query and run it against the database and check the table data to see if the
query indeed should return some values or may be there are no matches when include the rest of criteria since you mentioned that you are expecting the results that match all those values.
Dont use And use Or between criteria, and after all you should know that concatenating strings and executing queries is giving possible SQL Injection, that is when instead of your search string I end your query and execute given action, for example “‘ and 1=1; delete wsinmuebles” if this is my serach query you will lose all your data.
$result = mysql_query("select * from tbl1 where Name='".mysql_escape_string ($_POST["value"]."'" );
If a field, say Urbanizacion is null, your query will not return it.
Urbanizacion LIKE '%%' => FALSE when Urbanizacion is Null
You will need to handle Nulls. I also strongly urge you to protect the code from SQL Injection using mysql_real_escape_string
$result = mysql_query("
SELECT * FROM wsinmuebles WHERE
IFNULL(Property_Type,'') LIKE '" . mysql_real_escape_string($_POST['Property_Type']) ."' AND
IFNULL(Estado,'') LIKE '" . mysql_real_escape_string($_POST['Estado']). "' AND
IFNULL(Ciudad,'') LIKE '" . mysql_real_escape_string($_POST['Ciudad']) ."' AND
IFNULL(Urbanizacion,'') LIKE '" . mysql_real_escape_string($_POST['Urbanizacion']) ."' AND
IFNULL(Operacion,'') LIKE '" . mysql_real_escape_string($_POST['Operacion']) ."' AND
IFNULL(Precio_bsf,'') LIKE '" . mysql_real_escape_string($_POST['Precio_bsf']) ."'");
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.