0

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!!

Improve this question
2
  • What do you mean by "if any of these have a value"? Commented Feb 26, 2014 at 21:20
  • 1
    they are given their values by way of a GET from a checkbox, so if they arent checked they wont be there but if they are checked they will be, make sense?! Commented Feb 26, 2014 at 21:23

4 Answers 4

Reset to default
2 
$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)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Like it! I didnt quite get across to you what I wanted the outcome to be but your answer was helpful enough to allow me to edit it to suit. If your interested what I actually used is added to the original question! Thanks for your efforts!
0

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.

Improve this answer

Comments

0

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

Improve this answer

Comments

0

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) . "')";
Improve this answer

3 Comments

ive seen this IN function before but couldnt get my head around it - could you just give me a very quick lowdown on what it does? Im sure your right that it will work, but id like to understand the code :)
IN is the mysql syntaxe for in a list of values.
as for how the IN works, look at something like: select letter from alphabet where letter in ('A','E','I','O','U') to get all the vowels from an alphapet table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.