lundi 22 février 2016

How to dynamically accumulate checkbox values in a SQL query

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