mysql使用pdo简单封装select语句

最终代码：

 1 function pdo_array_query($pdo, $table_name, $data, $fields=array('*')){
 2     //Will contain SQL snippets.
 3     $rows_sql = array();
 4     //Will contain the values that we need to bind.
 5     $to_bind = array();
 6     //Get a list of column names to use in the SQL statement.
 7     $codition_array = array();
 8 
 9     $codition_string = "";
10     
11     foreach($data as $column_name => $column_value){
12         $codition_array[] = $column_name.' = :'.$column_name;
13         $param = ":" . $column_name;
14         $to_bind[$param] = $column_value;
15     }
16     $codition_string = implode(" and ", $codition_array);
17     $fields_string = implode(", ", $fields);
18 
19     $sql = "SELECT $fields_string FROM `$table_name` WHERE ".$codition_string;    
20     
21     //Prepare our PDO statement.
22     $pdo_statement = $pdo->prepare($sql);
23 
24     foreach($to_bind as $param => $val){
25         $pdo_statement->bindValue($param, $val);
26     }
27     // return $pdo_statement->execute();
28     $pdo_statement->execute();
29     $result = $pdo_statement->fetchAll();
30     return $result;
31     //print_r($result);
32 }

View Code

用法：

    $pdo = get_mysql_connect(); // 自定义 获取pdo实例的方法
    $table_name = 'order'; // 表名
    $fields = array("idproduct", "email", "customer", "address");// 需要查询的字段
    $data = array(
        "idpayment"=>"1270117360-13027-963488",
        "name"=>"John",
        "age"=>"28",
        "sex"=>"boy",
    ); // 查询的条件
    pdo_array_query($pdo, $table_name, $data, $fields);

拼合后的 sql 如下：

SELECT idproduct, email, customer, address FROM `order` WHERE idpayment = :idpayment and name = :name and age = :age and sex = :sex

升级版：
由于上面的方法，只能处理条件为 and 的情况，所以新方法增加了能添加逻辑数组的参数，具体代码如下：

 1 function pdo_array_query($pdo, $table_name, $data, $fields=array('*'), $operators=array('and')){
 2         //Will contain SQL snippets.
 3         $rows_sql = array();
 4         //Will contain the values that we need to bind.
 5         $to_bind = array();
 6         //Get a list of column names to use in the SQL statement.
 7         $condition_array = array();
 8         $condition_string = "";
 9         
10         foreach($data as $column_name => $column_value){
11             $condition_array[] = $column_name.' = :'.$column_name;
12             $param = ":" . $column_name;
13             $to_bind[$param] = $column_value;
14         }
15         // $codition_string = implode(" and ", $codition_array);
16         if (count($operators) == 1 ) {
17             $condition_string = implode(' '.$operators[0].' ', $condition_array); 
18         }
19         // the operators should less one than condition_array
20         else if (count($condition_array) - count($operators) == 1){
21             $result = cross_merge_array($condition_array, $operators);
22             $condition_string = implode(' ', $result);
23         }   
24 
25         $fields_string = implode(", ", $fields);
26         
27         $sql = "SELECT $fields_string FROM `$table_name` WHERE ".$condition_string;    
28        
29         echo $sql;
30         //Prepare our PDO statement.
31         $pdo_statement = $pdo->prepare($sql);
32         foreach($to_bind as $param => $val){
33             $pdo_statement->bindValue($param, $val);
34         }
35         $pdo_statement->execute();
36         $result = $pdo_statement->fetchAll();
37         print_r($result);
38         //return $pdo_statement->rowCount();
39     }

View Code

为了合理起见，关系数组应该是要比字段数组少 1，如：

idpayment = :idpayment and name = :name or age = :age and sex = :sex
 关系数组是： array('and', 'or', 'and')  长度为3
 字段数组是： array('idpayment', 'name', 'age', 'sex') 长度为 4

数组交叉合并的方法： 也可参考 http://www.cnblogs.com/tommy-huang/p/9050161.html

 1  function cross_merge_array($arr1, $arr2)
 2     {
 3         $arr1 = array_values($arr1);
 4         $arr2 = array_values($arr2);
 5         $count = max(count($arr1), count($arr2));
 6         $arr = array();
 7 
 8         for ($i = 0; $i < $count; $i++) {
 9             if ($i < count($arr1)) {
10                 $arr[] = $arr1[$i];
11             }
12 
13             if ($i < count($arr2)) {
14                 $arr[] = $arr2[$i];
15             }
16         }
17         return $arr;
18     }

View Code

用法：

    $pdo = get_mysql_connect(); // 自定义 获取pdo实例的方法
    $table_name = 'order'; // 表名
    $fields = array("idproduct", "email", "customer", "address");// 需要查询的字段
    $data = array(
        "idpayment"=>"1270117360-13027-963488",
        "name"=>"John",
        "age"=>"28",
        "sex"=>"boy",
    ); // 查询的条件
    $operators = array(
        'and',
        'or',
        'and',
    );

    pdo_array_query($pdo, $table_name, $data, $fields, $operators);