循环遍历数组以创建SQL查询

Question

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

Answer 1

$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)