PHP Dynamic update function error?

Question

I've made this dynamic update function, based on the fetch function, (same method, just another array for SET).

But it appears like there is an error with it.

    public function update($table, $columns = array(), array $criteria = null)
    {
        // The query base
        $query = "UPDATE $table";

        $query .= ' SET ' . implode(', ', array_map(function($column) {
            return "$column = ?";
        }, array_keys($columns)));

        // Start checking
        if ($criteria) {
            $query .= ' WHERE ' . implode(' AND ', array_map(function($column) {
                return "$column = ?";
            }, array_keys($criteria)));
        }

        $update = $this->pdo->prepare($query) or die('An error has occurred with the following message:' . $query);
        $update->execute(array_values($criteria));
    }

And that is the error I am getting (Line 179 = `$update->execute(array_values($columns, $criteria));):

What is wrong with it? why am I getting that error?

EDIT NEW ERROR:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens' in C:\xampp\htdocs\argonite\includes\class\MYSQL\Database.class.php:179 Stack trace: #0 C:\xampp\htdocs\argonite\includes\class\MYSQL\Database.class.php(179): PDOStatement->execute(Array) #1 C:\xampp\htdocs\argonite\index.php(8): Database->update('argonite_server...', Array, Array) #2 {main} thrown in C:\xampp\htdocs\argonite\includes\class\MYSQL\Database.class.php on line 179

@YourCommonSense Only if the column names are user-provided. — Barmar, Jul 24 '13 at 12:35
This question appears to be off-topic because it is about reading the d*%# error message — PeeHaa, Jul 24 '13 at 12:50

score 2 · Answer 1 · edited May 23 '17 at 11:43

this approach with helper functions is wrong.

what you really need is a generic query() function that supports a few extended placeholder types. Or, at least, a function to create SET statement out of array and a list of allowed fields.

this way you will have something like this:

$allowed = array("name","surname","email","password"); // allowed fields

$sql = "UPDATE users SET ".pdoSet($allowed,$values)." WHERE id = :id";
$stm = $dbh->prepare($sql);
$values['id'] = $_POST['id'];
$stm->execute($values);

score 1 · Answer 2 · answered Jul 24 '13 at 12:34

1

You have the wrong syntax here:

array_values($columns, $criteria)

As the array_values() function is called by 1 parameter and you are passing two. So remove any one and pass the array in this function.

For more read here: array_values().

answered Jul 24 '13 at 12:34

Code Lღver

15,573
16
56
75

Without $columns I will only get missing parameters SQL error, why? – Junatanm Degraded Jul 24 '13 at 12:49
@JunatanmDegraded because of array_values make the index of value 0,1,2 and its need the parameters so I think you should not use this function. – Code Lღver Jul 24 '13 at 12:55

Barmar · Answer 3 · 2013-07-24T12:54:27.480

0

It should be:

$update->execute(array_merge(array_values($columns), array_values($criteria)));

array_merge will concatenate the values from $columns and $criteria.

edited Jul 24 '13 at 12:54

answered Jul 24 '13 at 12:36

Barmar

741,623
53
500
612

Does it still say "No parameters were bound"? – Barmar Jul 24 '13 at 12:49
number of bound variables does not match number of tokens – Junatanm Degraded Jul 24 '13 at 12:51

PHP Dynamic update function error?

3 Answers3