从字符串中制作PDO

Question

I'm in the process of procedurally making a PDO statement, so far i've got

    $sql = "UPDATE users SET ";
    $values_array = array();
    foreach($non_empty_fields as $key => $value){
        $sql .= $key;
        $sql .= " = :".$key.", ";
        $values_array[':'.$key] = $value;
    }
    $sql = substr($sql, 0, -2);
    $sql .= " WHERE id = :id";
    $values_array[':id'] = $user_id;

    $sth = $this->conn->prepare($sql);
    print_r($non_empty_fields);
    print_r($values_array);
    echo($sql);
    $sth -> execute($values_array);
    $num_affected_rows = $sth -> affected_rows;
    $sth -> close();

and when i run it, i get

 Array
(
    [gender] => female
    [device_id] => 1213423489ydasxas98y76
)
Array
(
    [:gender] => female
    [:device_id] => 1213423489ydasxas98y76
    [:id] => 35
)
UPDATE users SET gender = :gender, device_id = :device_id WHERE id = :id<br />
<b>Fatal error</b>:  Call to a member function execute() on a non-object in <b>/Sites/api/include/DbHandler.php</b> on line <b>280</b><br />

I suspect its because I'm not setting the prepared statement properly, so my question is: how do you make a prepared statement from a string?

UPDATE:

call_user_func so the code now looks like:

    $sql = "UPDATE users SET ";
    $values_array = array();
    foreach($non_empty_fields as $key => $value){
        $sql .= $key;
        $sql .= " = ?, ";
        $values_array[] = &$value;
    }
    $sql = substr($sql, 0, -2);
    $sql .= " WHERE id = ?";
    $values_array[] = &$user_id;

    $sth = $this->conn->prepare($sql);
    $params = array_merge(array(str_repeat('s', count($values_array))), array_values($values_array));
    call_user_func_array(array(&$sth, 'bind_param'), $params);
    $sth -> execute();

now the error I'm getting isn't represented, pdo just doesn't update the table.

Answer 1

Oh Mysqli? i thought you said it was PDO. Mysqli doesnt support named markers like :id, you have to use question marks like ?

This parameter can include one or more parameter markers in the SQL statement by embedding question mark (?) characters at the appropriate positions.

Reference

And for PDO you can use either

Prepares an SQL statement to be executed by the PDOStatement::execute() method. The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed. You cannot use both named and question mark parameter markers within the same SQL statement; pick one or the other parameter style. Use these parameters to bind any user-input, do not include the user-input directly in the query.

Manual