Dynamically created WHERE clause PHP/MySQL

Question

I am dynamically generating the WHERE portion of a MySQL query. My code below works perfectly so far

PLEASE NOTE: _GET strings are all validated elswhere in my code but in order to keep this code down to a reasonable length for this question I have put them in directly below. For anyone looking to do something similar to what I am doing and is using my code here as a base, please be sure to validate your strings to avoid mysql injections.

  /* Loop through each column in the table */

        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
             /* check if the column has been marked as searchable and that the param sent from the client contains data */
            if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
            {
                if ( $sWhere == "" )
                {
                    $sWhere = "WHERE ";
                }
                else
                {
                    $sWhere .= " AND ";
                }



/* RANGE FILTER CODE - This part is not important to this question but included for completenes */

                $columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
                // check for values range
                $rangeSeparator = "~";
                if (!empty($rangeSeparator) && strstr($columnFilterValue, $rangeSeparator)) {
                    // get min and max

                     $columnFilterRangeMatches =  explode('~', $columnFilterValue);

                    // get filter
                    if (empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                        $sWhere .= " 0 = 0 ";
                    else if (!empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " BETWEEN '" . $columnFilterRangeMatches[0] . "' and '" . $columnFilterRangeMatches[1] . "' ";
                    else if (empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " < '" . $columnFilterRangeMatches[1] . "' ";
                    else if (!empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                        $sWhere .= $aColumns[$i] . " > '" . $columnFilterRangeMatches[0] . "' ";
                } else {



    /* Begin building WHERE clause */


        $sWhere = "WHERE (";

                $aORs = array();

                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                            {
                                if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
                                        {
                                                    $value = $_GET['sSearch_'.$i];

                                                        array_push($aORs, $aColumns[$i]." IN ($value)");


                                        }
                            }
                $sWhere .= implode(" OR ",$aORs);   

        $sWhere .= ')';
                }
            }
        }

Now what this code does it it takes strings of comma seperated values sent from the client and builds the WHERE clase based on those.

• $aColumns is an array containing the columns in the table

EXAMPLE:-

If the parameters...

• sSearch_1 contains a value of 1,3,5,6
• sSearch_2 contains a value of 1,2,3
• sSearch_4 cotains a value of 4,5,6
• sSearch 6 cntains a value of 7,8,9

Then the following WHERE clause would be generated by this code:

 WHERE genre_id IN (1,3,5,6) OR instruments IN (1,2,3) OR emotions IN (4,5,6) OR ratings IN (7,8,9)

This works fine however I want to make the OR's or AND's dynamic also by sending another string containing a list of OR's and AND's in the correct sequential order.

so for instance if $_GET['filtertype'] = a string like this:-

OR,OR,AND

then instead of the above it should return:

WHERE genre_id IN (1,3,5,6) OR instruments IN (1,2,3) OR emotions IN (4,5,6) OR ratings IN (7,8,9)

As you can see in my code above I am currently injecting the OR's in to my array via the implode function. (The relevant part of code is repeated below)

  $sWhere = "WHERE (";

                $aORs = array();

                    for ( $i=0 ; $i<count($aColumns) ; $i++ )
                            {
                                if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
                                        {
                                                    $value = $_GET['sSearch_'.$i];

                                                        array_push($aORs, $aColumns[$i]." IN ($value)");


                                        }
                            }
                $sWhere .= implode(" OR ",$aORs); 

        $sWhere .= ')';

How can I modify this to sequrntially add the correct AND or OR based on the correct loop?

Answer 1

Instead of creating a where string, I would first create an array with where parts.

$whereParts = array();
foreach($aColumns as $i => $column)
{
    <logic goes here>
    $whereParts[] = 'genre_id IN (1,3,5,6)'; // sample :)
}

$where = 'WHERE ' . implode(' OR ', $whereParts); // note the spaces around OR

Then it's easy to replace ' OR ' with ' AND '

It's easy to allow users to select between AND and OR for all where parts, but not if you want to do that for each individual item. That's also a logic issue. When the user specifies a OR b AND c, would he want (a OR b) AND c or a OR (b AND c)?