Make SQL Ignore A Specific Search Value If Clicked In A Dropdown?

I am creating a multi-form search box that searches a Meat packaging database. The search works only if users are specific, I want them to be able to search the database without ha

Solution 1:

You can build your $sql differently:

$varRange="";
if ($_POST['trayrange'] != "*") {
$varRange="and trayrange like %".$_POST['trayrange']."%";
}

then concatenate

$sql="select"...$varRange...;

Solution 2:

You are correct in your assumption that if the "Any" options is used it would be better to not include the filter in the query (as you are generating the query).

An other option is to use a multi-select control and if the length of the selection is zero, then not too generate the filter

A word of caution, the extensive use of the LIKE keyword will lead to performance issues if the table the query is executed on grows.

Solution 3:

You can use optional parameters as in the following. You would set the value of a selection of "All" to asterisk or similar and then code your SQL as follows:

"SELECT * FROM delyn 
WHERE description LIKE '%".$descrip."%' 
AND ((.$height.   = '*') OR (trayheight LIKE '%".$height."%'))
AND ((.$width.    = '*') OR (traywidth LIKE '%".$width."%')) 
AND ((.$depth.    = '*') OR (traydepth LIKE %".$depth."%')) 
AND ((.$varRange. = '*') OR (trayrange LIKE '%".$varRange."%')) 
AND ((.$varType.  = '*') OR (traytype LIKE '%".$varType."%')) 
AND ((.$varShape. = '*') OR (trayshape LIKE '%".$varShape."%'))";

This example assumes that the first selection - description requires a value. The user could leave the rest of the selections set to "All".