php PDO bindParam（）width 2维数组 - foreach循环

Question

can I ask somebody is this good solution (value is passed to bindParam() in foreach as follows: $var_value = &$row['val'];) or is it better solution for it ?

I have array for example:

$array['city']['val']        = $city;  //city value passed to bindParam()
$array['city']['type']       = 'string'; //type passed to bindParam()
$array['city_id']['val']     = $city_id;   
$array['city_id']['type']    = 'int';     

$query = "update cities set city=:city where city_id=:city_id"; 

function to bind parameters:

function BindParameters($array,$query) {

        $st = $this->dbc->prepare($query);   

        if (is_array($array) && count($array)>0) {

            foreach ($array as $key=> $row) {

                if (isset($row['type'])) {
                    $var_type  = $row['type'];
                } else {
                    $var_type  = '';
                }

                $var_value =  &$row['val']; 

                if ($var_type=='int') {
                    $var_type = PDO::PARAM_INT;
                } else if ($var_type=='string') {
                    $var_type = PDO::PARAM_STR;
                } else if ($var_type=='null') {
                    $var_type = PDO::PARAM_NULL;
                } else {
                    $var_type = PDO::PARAM_STR;
                }
                $st->bindParam(':'.$key,$var_value,$var_type);
            }
        }
         $st->execute();
         return $st;
    }

Thanks in advance

Answer 1

Nope, this solution is not good. It's inconvenient and error-prone.
Yes, there is a much better solution for running regular queries:

Just have your data in array like this

$array['city']    = $city;
$array['city_id'] = $city_id;   

and then send it directly to execute():

$query = "update cities set city=:city where city_id=:city_id";
$pdo->prepare($query)->execute($array);

which is all the code you need to run this query, no clumsy BindParameters function ever needed.

As long as you keep PDO::ATTR_EMULATE_PREPARES as false, this approach won't make any trouble for you.

For the regular SQL query you never need neither bindParam() nor it's third parameter.

So, make this function as this

function run($query, $array = NULL) {

    $st = $this->dbc->prepare($query);   
    $st->execute($array);
    return $st;
}