mysql使用pdo简单封装select语句

最终代码：

function pdo_array_query($pdo, $table_name, $data, $fields=array('*')){
    //Will contain SQL snippets.
    $rows_sql = array();
    //Will contain the values that we need to bind.
    $to_bind = array();
    //Get a list of column names to use in the SQL statement.
    $codition_array = array();

    $codition_string = "";
    
    foreach($data as $column_name => $column_value){
        $codition_array[] = $column_name.' = :'.$column_name;
        $param = ":" . $column_name;
        $to_bind[$param] = $column_value;
    }
    $codition_string = implode(" and ", $codition_array);
    $fields_string = implode(", ", $fields);

    $sql = "SELECT $fields_string FROM `$table_name` WHERE ".$codition_string;    
    
    //Prepare our PDO statement.
    $pdo_statement = $pdo->prepare($sql);

    foreach($to_bind as $param => $val){
        $pdo_statement->bindValue($param, $val);
    }
    // return $pdo_statement->execute();
    $pdo_statement->execute();
    $result = $pdo_statement->fetchAll();
    return $result;
    //print_r($result);
}

用法：

    $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 的情况，所以新方法增加了能添加 逻辑数组的 参数，具体代码如下：

function pdo_array_query($pdo, $table_name, $data, $fields=array('*'), $operators=array('and')){
        //Will contain SQL snippets.
        $rows_sql = array();
        //Will contain the values that we need to bind.
        $to_bind = array();
        //Get a list of column names to use in the SQL statement.
        $condition_array = array();
        $condition_string = "";
        
        foreach($data as $column_name => $column_value){
            $condition_array[] = $column_name.' = :'.$column_name;
            $param = ":" . $column_name;
            $to_bind[$param] = $column_value;
        }
        // $codition_string = implode(" and ", $codition_array);
        if (count($operators) == 1 ) {
            $condition_string = implode(' '.$operators[0].' ', $condition_array); 
        }
        // the operators should less one than condition_array
        else if (count($condition_array) - count($operators) == 1){
            $result = cross_merge_array($condition_array, $operators);
            $condition_string = implode(' ', $result);
        }   

        $fields_string = implode(", ", $fields);
        
        $sql = "SELECT $fields_string FROM `$table_name` WHERE ".$condition_string;    
       
        echo $sql;
        //Prepare our PDO statement.
        $pdo_statement = $pdo->prepare($sql);
        foreach($to_bind as $param => $val){
            $pdo_statement->bindValue($param, $val);
        }
        $pdo_statement->execute();
        $result = $pdo_statement->fetchAll();
        print_r($result);
        //return $pdo_statement->rowCount();
    }

      为了合理起见，关系数组 应该是要比 字段数组 少 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

 function cross_merge_array($arr1, $arr2)
    {
        $arr1 = array_values($arr1);
        $arr2 = array_values($arr2);
        $count = max(count($arr1), count($arr2));
        $arr = array();

        for ($i = 0; $i < $count; $i++) {
            if ($i < count($arr1)) {
                $arr[] = $arr1[$i];
            }

            if ($i < count($arr2)) {
                $arr[] = $arr2[$i];
            }
        }
        return $arr;
    }

 用法：

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