I have a search form where users can choose a few checkboxes, set a date range, use a wildcard search etc. and via button click it gets sent via ajax to a PHP file and executed in a MySQL query.
In the php file I set up a cascade of if-statements to ensure the query works regardless of the user having chosen certain values, f.e. like this:
// variable from a risk level dropdown menu
if ($_POST['risklevelcount']=="") {
$risklevel = "MASTER.RISK_LEVEL != ''";
}
else {
$risklevel = "MASTER.RISK_LEVEL = "' . $risklevelcount . "'";
}
the variables then are implemented in the where clause of the mysql query, which looks like this:
$result = mysql_query("
SELECT *
FROM MASTER
WHERE
(( ". $buttonvalue ." ) AND ( ". $date . " ) AND ( ". $risklevel ." ))
");
To ensure the query gets executed regardless of the user having checked a checkbox, I analoguously implemented them in a if-clause like this:
if(!empty($checkbox1))
$checkbox1 = "MASTER_CHECK like '%dog%'";
else $checkbox1 = "MASTER.CHECK = '' OR MASTER.CHECK != ''";
if(!empty($checkbox2))
$checkbox1 = "MASTER_CHECK like '%cat%'";
else $checkbox1 = "MASTER.CHECK = '' OR MASTER.CHECK != ''";
if(!empty($checkbox3))
$checkbox1 = "MASTER_CHECK like '%bird%'";
else $checkbox1 = "MASTER.CHECK = '' OR MASTER.CHECK != ''";
If I add these to the above MySQL query where clause I obviously won't get the results I want:
$result = mysql_query("SELECT *
FROM MASTER
WHERE
(
( ". $buttonvalue ." ) AND ( ". $date . " ) AND (". risklevel .") AND
(
(". $checkbox1.") OR (". $checkbox2.") OR (". $checkbox3.")
)
)
");
I won't get for example results for 'dog' AND 'cat' but not 'bird' (= checkbox 1 and 2 checked but not 3).
I know this is a really stupid approach from the start, regardless of the checkboxes. I sadly don't know how to do any better yet. If someone could point me in the right direction, I would be very glad. I think there is a much more smart way to do it, f.e. with arrays? I just really lack a real basis knowledge!
Aucun commentaire:
Enregistrer un commentaire