CODEWITHSUNDEEP
phpmysqlsqlarrays
Giovanni
Giovanni

Reputation: 838

Loop through an array to create an SQL Query

I have an array like the following:

tod_house
tod_bung
tod_flat
tod_barnc
tod_farm
tod_small
tod_build
tod_devland
tod_farmland

If any of these have a value, I want to add it to an SQL query, if it doesnt, I ignore it. Further, if one has a value it needs to be added as an AND and any subsequent ones need to be an OR (but there is no way of telling which is going to be the first to have a value!)

Ive used the following snippet to check on the first value and append the query as needed, but I dont want to copy-and-paste this 9 times; one for each of the items in the array.

$i = 0;
if (isset($_GET['tod_house'])){
    if ($i == 0){
        $i=1;
        $query .= " AND ";
    } else {
        $query .= " OR ";
    }
    $query .= "tod_house = 1";
}

Is there a way to loop through the array changing the names so I only have to use this code once (please note that $_GET['tod_house'] on the first line and tod_house on the last line are not the same thing! - the first is the name of the checkbox that passes the value, and the second one is just a string to add to the query)

Solution

The answer is based heavily upon the accepted answer, but I will show exactly what worked in case anyone else stumbles across this question....

I didnt want the answer to be as suggested:

tod_bung = 1 AND (tod_barnc = 1 OR tod_small = 1)

rather I wanted it like:

AND (tod_bung = 1 OR tod_barnc = 1 OR tod_small = 1)

so it could be appended to an existing query. Therefore his answer has been altered to the following:

$qOR = array();
foreach ($list as $var) {
    if (isset($_GET[$var])) {
            $qOR[] = "$var = 1";
    }
}
$qOR = implode(' OR ', $qOR);
$query .= " AND (" .$qOR . ")";

IE there is no need for two different arrays - just loop through as he suggests, if the value is set add it to the new qOR array, then implode with OR statements, surround with parenthesis, and append to the original query.

The only slight issue with this is that if only one item is set, the query looks like:

AND (tod_bung = 1)

There are parenthesis but no OR statements inside. Strictly speaking they arent needed, but im sure it wont alter the workings of it so no worries!!

Upvotes: 0

Views: 2349

Answers (4)

Sparkup
Sparkup

Reputation: 3754

If the array elements are tested on the same column you should use IN (...) rather than : 

AND ( ... OR ... OR ... )

If the values are 1 or 0 this should do it :

// If you need to get the values.
$values = $_GET;
$tod = array();

foreach($values as $key => $value) {
    // if you only want the ones with a key like 'tod_' 
    // otherwise remove if statement
    if(strpos($key, 'tod_') !== FALSE) {
        $tod[$key] = $value;
    }
}

// If you already have the values.
$tod = array(
    'tod_house' => 1, 
    'tod_bung' => 0, 
    'tod_flat' => 1, 
    'tod_barnc' => 0
);

// remove all array elements with a value of 0.
if(($key = array_search(0, $tod)) !== FALSE) {
    unset($tod[$key]);
}
// discard values (only keep keys).
$tod = array_keys($tod);

// build query which returns : AND column IN ('tod_house','tod_flat')
$query = "AND column IN ('" . implode("','", $tod) . "')";

Upvotes: 0

Samy Massoud
Samy Massoud

Reputation: 4385

Their are a lot of ways out their 

    $list = array('tod_house', 'tod_bung', 'tod_flat', 'tod_barnc', 'tod_farm', 'tod_small', 'tod_build', 'tod_devland', 'tod_farmland');
    if($_GET){
     $query = "";
    foreach ($_GET as $key=>$value){
         $query .= (! $query) ?  " AND ":" OR ";
         if(in_array($key,$list) && $value){
          $query .= $key." = '".$value."'";
         }
    }
}

Sure you have to take care about XSS and SQL injection

Upvotes: 0

cornelb
cornelb

Reputation: 6066

$list = array('tod_house', 'tod_bung', 'tod_flat', 'tod_barnc', 'tod_farm', 'tod_small', 'tod_build', 'tod_devland', 'tod_farmland');
$qOR = array();
$qAND = array();

foreach ($list as $var) {
    if (isset($_GET[$var])) {
        if (!empty($qAND)) {
            $qOR[] = "$var = 1";
        } else {
            $qAND[] = "$var = 1";
        }
        $values[] = $_GET[$var];
    }
}



$qOR = implode(' OR ', $qOR);
if ($qOR != '') {
    $qOR = '(' . $qOR . ')';
}

$qAND[] = $qOR;
$qAND = implode(' AND ', $qAND);


echo $qAND;

This will output something like tod_bung = 1 AND (tod_barnc = 1 OR tod_small = 1)

Upvotes: 2

Smutje
Smutje

Reputation: 18183

As the parameter passed to $_GET is a string, you should build an array of strings containing all the keys above, iterating it and passing the values like if (isset($_GET[$key])) { ...

You could then even take the key for appending to the SQL string.

Upvotes: 0

Related Questions