我的行为让我很困惑如何解决此问题...
我有一个表“ favcolor”,共有4列,名称分别为“ pot1”,“ pot2”,“ pot3”,“ pot4”。
在此列中是饮料的ID。
当我在“ selectbox1”(“柠檬汁”)中选择饮料时:能量/值=“ 5”。
在“ selectbox2”(“ limonade2”)中,选择饮料:Mezzo / value =“ 3”。
它生成以下SQL语句:
"SELECT * FROM favcolor WHERE pot1= 5 AND pot2= 3" - i get 15 hits.
现在,我在“ selectbox1”(“柠檬汁”)中选择饮料:Mezzo / value =“ 3”。
然后在“ selectbox2”(“ limonade2”)中选择饮料:Energy / value =“ 5”。
现在,此SQL语句已生成:
"SELECT * FROM favcolor WHERE pot1= 3 AND pot2= 5" - i get 7 hits.
但是,我尝试找到一种解决方案,使所有命中“能量”和“ Mezzo”的位置都得到匹配,无论它们出现在Pot1,Pot2,Pot3还是Pot4中。
希望您能帮助我找到此“问题”的解决方案。
为了更好地理解,这是我当前的代码:
<form method="post"action="./usercolor.php">
<input type="checkbox" id="colorred" name="colorred" value="1"/>red</td>
<input type="checkbox" id="colorblue" name="colorblue" value="1"/>blue</td>
<input type="checkbox" id="coloryellow" name="coloryellow" value="1"/>yellow</td>
<input type="checkbox" id="colorgreen" name="colorgreen" value="1"/>green</td>
<input type="checkbox" id="colorblack" name="colorblack" value="1"/>black</td>
</select name="limonade">
<option value="1">Cola</option>
<option value="2">Fanta</option>
<option value="3">Mezzo</option>
<option value="4">Sprite</option>
<option value="5">Energy</option>
</select>
</select name="limonade2">
<option value="1">Cola</option>
<option value="2">Fanta</option>
<option value="3">Mezzo</option>
<option value="4">Sprite</option>
<option value="5">Energy</option>
</select>
</select name="limonade3">
<option value="1">Cola</option>
<option value="2">Fanta</option>
<option value="3">Mezzo</option>
<option value="4">Sprite</option>
<option value="5">Energy</option>
</select>
</select name="limonade4">
<option value="1">Cola</option>
<option value="2">Fanta</option>
<option value="3">Mezzo</option>
<option value="4">Sprite</option>
<option value="5">Energy</option>
</select>
<input type="submit" value="SEARCH"/>
</form>
PHP代码:
$whereArr = Array();
if (isset($_POST["colorred"]) && $_POST["colorred"]==1) $whereArr[] = "red=1";
if (isset($_POST["colorblue"]) && $_POST["colorblue"]==1) $whereArr[] = "blue=1";
if (isset($_POST["colorgreen"]) && $_POST["colorgreen"]==1) $whereArr[] = "green=1";
if (isset($_POST["coloryellow"]) && $_POST["coloryellow"]==1) $whereArr[] = "yellow=1";
if (isset($_POST["colorblack"]) && $_POST["colorblack"]==1) $whereArr[] = "black=1";
$sql = "SELECT * FROM favcolor WHERE " . implode(" AND ", $whereArr);
if (empty($whereArr)) {
if( $_POST["limonade"] > 0 ) $sql .= ' pot1 = ' . $_POST["limonade"];
if( $_POST["limonade2"] > 0 ) $sql .= ' pot2 = ' . $_POST["limonade2"];
if( $_POST["limonade3"] > 0 ) $sql .= ' pot3 = ' . $_POST["limonade3"];
if( $_POST["limonade4"] > 0 ) $sql .= ' pot4 = ' . $_POST["limonade4"];
}
else {
if( $_POST["limonade"] > 0 ) $sql .= ' and pot1 = ' . $_POST["limonade"];
if( $_POST["limonade2"] > 0 ) $sql .= ' and pot2 = ' . $_POST["limonade2"];
if( $_POST["limonade3"] > 0 ) $sql .= ' and pot3 = ' . $_POST["limonade3"];
if( $_POST["limonade4"] > 0 ) $sql .= ' and pot4 = ' . $_POST["limonade4"];
}
桌子:
User |Red |Blue |Yellow |Green |Black
Klaus |0 |1 |1 |0 |0
Jessy |1 |0 |1 |0 |1
Andy |1 |1 |0 |0 |0
Alex |0 |0 |0 |1 |1
Denis |1 |1 |0 |0 |1
最佳答案
您想喝些“能量”或“ Mezzo”饮料:
$limonades = array():
if( $_POST["limonade"] > 0 ) $limonades['pot1'] = $_POST["limonade"];
if( $_POST["limonade2"] > 0 ) $limonades['pot2'] = $_POST["limonade2"];
if( $_POST["limonade3"] > 0 ) $limonades['pot3'] = $_POST["limonade3"];
if( $_POST["limonade4"] > 0 ) $limonades['pot4'] = $_POST["limonade4"];
if(!empty($limonades)) {
if (!empty($whereArr))
$sql .= ' and ';
$sql .= '(';
$keys = array_keys($limonades);
for($i = 0; $i < count($limonades); $i++) {
if($i == 0)
$sql .= $keys[$i].' = '. $limonades[$keys[$i]];
elseif($i == count($limonades) - 1)
$sql .= ')';
else
$sql .= ' OR '.$keys[$i].' = '. $limonades[$keys[$i]];
}
}