I have a simple MySQL database that I am querying from PHP. I have a user input some constraints in via a form, and then want to return results from a SELECT query based on the constraints. What I have is working when both constraints are used. However, if one of the constraints is not specified by the user, the SELECT query is returning no results, which I think is because it seeing the constraint variable as empty.
How do I get the SELECT query to use the constraint if it is not empty, but to ignore it if it is empty?
Here is what I tried earlier but it isn't working. I thought I could test for empty and use OR to ignore it.
$query="SELECT * FROM food_items
WHERE (type IN ('$foodtypes') OR ('$foodtypes') > '')
AND ('$budget' > '' OR cost <= ('$budget'))
";
In this example, type and cost are fields in the database and $foodtypes and $budget are variables taken from the user inputs.
Thanks
I think you should build your query differently based on the value of the constraints:
$query = "SELECT * FROM food_items WHERE " .
($foodtypes != "" ? "type IN ('$foodtypes')" : "TRUE") . " AND " .
($budget ? "cost <= $budget" : "TRUE") . " AND " .
.. and so on ..
Security note: Always escape your SQL strings before using (see mysqli_real_escape_string).