I'm trying to create the search system with mutliple criteria fields. My question is how to handle the empty criteria fields (omitted by user) when you search by EXACT word not just it's similars (LIKE).
Code:
$prva = mysql_real_escape_string($_POST["crit1"]);
$druga = mysql_real_escape_string($_POST["crit2"]);
$tretja = mysql_real_escape_string($_POST["crit3"]);
$cetrta = mysql_real_escape_string($_POST["crit4"]);
$query = mysql_query("SELECT pointID FROM bpoint WHERE
(sName LIKE '%$prva%') AND
(sAddr LIKE '%$druga%') AND
(placeID LIKE '%$tretja%') AND
(sPhone = '$cetrta')
");
Someone suggested this, but i dont understand exactly how it works:
WHERE
(col1=@col1 or @col1 is null) and
(col2=@col2 or @col2 is null) and
(col3=@col3 or @col3 is null) and
.
.
thanks :)
$query="SELECT pointID FROM bpoint WHERE ";
if(isset($_POST['condition1']))
$condition[]="name='somename'";
if(isset($_POST['condition2']))
$condition[]="address='someaddress'";
//More condition here
if(!empty($condition))
$query .= implode(' AND ',$condition);
mysql_query($query)
The suggestion is the same as writing the following query:
SELECT
pointID
FROM
bpoint
WHERE
(sName = '$prva' or sName is null)
AND (sAddr = '$druga' or aAdds is null)
AND (placeID = '$tretja' or placeID is null)
AND (sPhone = '$cetrta' or sPhone is null)
which is basically querying for the EXACT match in your returned data unless the column you are searching is null. This will work quite well - unless you have records where all the searched fields are null, in which case it will return every single one of them.
col1=@col1 or @col1 is null
^^^ ^^^^^
here it will check here here check that col1 is null
that col1 is equal
to col1
> since there is OR condition then only tow condition that either the col is null(or specified ) or something so and if there is anything exist then fetch else it will return null here it will check every single one of them (every single will return)