Try this simple function:
function create_query($p_species, $p_family, $p_zones, $p_marker) { $options = array( 'Speciesname' => $p_species, 'Family' => $p_family, 'Zones' => $p_zones, 'Marker' => $p_marker ); $cond = ''; $noopt = true; foreach ($options as $column => $value) { if ($value !== 0) { $noopt = false; if ($cond != '') $cond .= ' AND '; $cond .= "$column LIKE '%$value%'"; } } return $noopt ? false : "SELECT * FROM ncbi_bold_ plants WHERE $cond;"; }
This function returns false if all parameters are equal to zero, otherwise it returns a query string.
Example # 1:
$species = 'spectest'; $family = 0; $zones = 'zonestest'; $marker = 0; echo create_query($species, $family, $zones, $marker);
Output:
SELECT * FROM ncbi_bold_ plants WHERE Speciesname LIKE '%spectest%' AND Zones LIKE '%zonestest%';
Example # 2:
$species = 0; $family = 0; $zones = 0; $marker = 0; var_dump(create_query($species, $family, $zones, $marker));
Output:
bool(false)
Or, if you want to return a query string without conditions instead of false , simply replace the return string in the function with:
return $noopt ? "SELECT * FROM ncbi_bold_ plants;" : "SELECT * FROM ncbi_bold_ plants WHERE $cond;";
Now, if you want to do this on your way with a lot of IF, ELSEIF, and ELSE sentences, there is a solution:
Solution without function
(it works the same as the function above):
if ($species == 0 && $family == 0 && $zones == 0 && $marker == 0) { $sql = "SELECT * FROM ncbi_bold_plants"; } elseif ($species == 0 && $family == 0 && $zones == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Marker LIKE '$marker%'"; } elseif ($species == 0 && $family == 0 && $marker == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Zones LIKE '$zones%'"; } elseif ($species == 0 && $zones == 0 && $marker == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%'"; } elseif ($family == 0 && $zones == 0 && $marker == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%'"; } elseif ($species == 0 && $family == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Zones LIKE '$zones%' AND Marker LIKE '$marker%'"; } elseif ($species == 0 && $zones == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Marker LIKE '$marker%'"; } elseif ($species == 0 && $marker == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Zones LIKE '$zones%'"; } elseif ($family == 0 && $zones == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Marker LIKE '$marker%'"; } elseif ($family == 0 && $marker == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Zones LIKE '$zones%'"; } elseif ($zones == 0 && $marker == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%'"; } elseif ($species == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Family LIKE '$family%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'"; } elseif ($family == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'"; } elseif ($zones == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Marker LIKE '$marker%'"; } elseif ($marker == 0) { $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Zones LIKE '$zones%'"; } else { $sql = "SELECT * FROM ncbi_bold_plants WHERE Speciesname LIKE '$species%' AND Family LIKE '$family%' AND Zones LIKE '$zones%' AND Marker LIKE '$marker%'"; } // here you have your $sql ... now do whatever you want with your query echo $sql;
This code has been tested with all possible situations, so if it still doesn’t work, your problem is not with the PHP example you specified above. I do not recommend , because this is a completely wrong approach, even for beginners.
If you still want to do this with IF / ELSE / ELSEIF, consider the condition (s) checking the IF variable is NOT IMPOSSIBLE to zero. You will get less code and an easier implementation. Something like that:
Miscellaneous aproach
$species = 0; $family = 0; $zones = 0; $marker = 0; // default query w/out conditions $sql = "SELECT * FROM ncbi_bold_plants"; // conditions array $conditions = array(); // adding condition to array for every parameter <> 0 if ($species != 0) $conditions[] = "Speciesname LIKE '$species%'"; if ($family != 0) $conditions[] = "Family LIKE '$family%'"; if ($zones != 0) $conditions[] = "Zones LIKE '$zones%'"; if ($marker != 0) $conditions[] = "Marker LIKE '$marker%'"; // all we need now is to concatenate array elements with " AND " glue $sql_cond = join(" AND ", $conditions); // last thing, adding condition(s) to the main query (if there any) if ($sql_cond != '') $sql .= " WHERE $sql_cond"; // let see what we have now echo $sql;
The first 4 lines are for testing purposes only. Now we have the same solution as the previous one, but with less code (only 8 lines).
Hope this helps.