SQL查询从数组中选择具有多个动态条件的查询 [英] SQL query select with multiple dynamic conditions from array

问题描述

我想使用预先存储在数组中的条件执行SELECT查询.
但是，根据要传递的属性数量，将动态填充此数组.

I want to perform a SELECT query with conditions which are stored in an array beforehand.
However this array will be filled dynamically depending on how many properties will be passed.

属性将像这样存储在MySQL数据库中:

The attributes would be stored in a MySQL db like this:

| car_id | brand | fwd | ac | abs | gasoline | diesel |
--------------------------------------------------------
| 000001 |   VW  |  0  |  1 |  1  |     0    |    1   |

然后将通过表单选择变量.

Then the variables would be selected through a form.

$4wd = isset( $_POST['fwd'] );      
$diesel = isset( $_POST['diesel'] );                
$gasoline = isset( $_POST['gasoline'] );              
$ac = isset( $_POST['ac'] );                          
$abs = isset( $_POST['abs'] );     

$features = array();

if ($4wd == true) {          
    array_push($features, "fwd");        
}     
if ($diesel == true) {                       
    array_push($featuresn, "diesel");                 
}                    
if ($gasoline == true) {                    
    array_push($features, "gasoline");                    
}                   
if ($ac == true) {                    
    array_push($features, "ac");                    
}                   
if ($abs == true) {                    
    array_push($features, "abs");                
}              

现在在这里找不到我的问题的答案；
一个静态"选择查询将不起作用，因为在这一点上，数组$ properties可以具有不同的数字和值的类型:

Now here is where it I can't find an answer to my problem;
a "static" select query wouldn't work because at this point the array $properties can have different number and typed of values:

$features ([0] => fwd [1] => abs [2] => ac)  
$features ([0] => fwd [1] => abs [2] => ac [3] => diesel)   
$features ([0] => abs [1] => ac  [2] => gasoline)   

无法弄清楚实际的语法，所以这里我要用外行的话:

Can't figure out the actual syntax so here what I want in laymans terms:

SELECT car_id FROM car_list WHERE [firstarrayvalue==1] AND [secondarrayvalue==1] AND [thirdarrayvalue==1] 

直到将数组的所有值都用作条件.

until all values of the array have been used as condition.

推荐答案

我建议准备数组中的所有where子句并将其内插到查询中.

I'd suggest preparing all the where-clauses in an array and imploding them into the query.

foreach($features as $f) {
   $sqlfeatures[] = "$f = 1";
}

$sql = "SELECT car_id FROM car_list WHERE ".implode(' AND ', $sqlfeatures);

或者是简单的单班轮

"SELECT car_id FROM car_list WHERE ".implode(' = 1 AND ', $features)." = 1";

这篇关于SQL查询从数组中选择具有多个动态条件的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！