Prepared statements with an array

Question

I have a function to do a simple insert, but am trying to make the method more robust by passing an array. And this is the array I pass into it:

        $form_data = array(
        "sort_order"=>$_POST['sort_order'],
        "name"=>$_POST['page_name'],
        "text"=>$_POST['page_text'],
        "image"=>$_POST['page_image'],
        "meta_desc"=>$_POST['meta_desc'],
        "meta_kw"=>$_POST['meta_kw'],
        "meta_author"=>$_POST['meta_author'],
        "image_thumb"=>"NULL",
    );

Here is the function code:

public function insert_data($array){
        $keys = array();
        $values = array();
        
        foreach($array as $k => $v){
            $keys[] = $k;
            if(!empty($v)){
                $values[] = $v;
            } else {
                $values[] = "NULL";
            }
        }

        $stmt = self::$mysqli->stmt_init();
        $query = "INSERT INTO `".DB_TABLE_PAGES."` (".implode(",",$keys).") VALUES (?,?,?,?,?,?,?,?)";
        
        
        $stmt->prepare($query);
        $stmt->bind_param('ssssssss',implode(",",$values));

        //$stmt->execute();
    }

But I get this error:

Number of elements in type definition string doesn't match number of bind variables.

I know what the problem is, but I don't understand how I can achieve it.

do you mean the array that i pass into the function to build the query? — Adamski, May 30 '11 at 17:03
No. I mean `$query`! As in, what do you get if you `echo $query`? Is it valid SQL? — Oliver Charlesworth, May 30 '11 at 17:05
to my eye yes: INSERT INTO `pages` (sort_order,name,text,image,meta_desc,meta_kw,meta_author,image_thumb) VALUES (?,?,?,?,?,?,?,?) — Adamski, May 30 '11 at 17:10

score 5 · Accepted Answer · answered May 30 '11 at 17:14

Try this:

public function insert_data($array){
    $placeholders = array_fill(0, count($array), '?');

    $keys = $values = array();
    foreach($array as $k => $v) {
        $keys[] = $k;
        $values[] = !empty($v) ? $v : null;
    }

    $stmt = self::$mysqli->stmt_init();
    $query = 'INSERT INTO `'.DB_TABLE_PAGES.'` '.
             '('.implode(',', $keys).') VALUES '.
             '('.implode(',', $placeholders).')';
    $stmt->prepare($query);

    call_user_func_array(
        array($stmt, 'bind_param'), 
        array_merge(
            array(str_repeat('s', count($values))),
            $values
        )
    );

    $stmt->execute();
}

Or better yet, use PDO instead:

public function insert_data($array){
    $placeholders = array_fill(0, count($array), '?');

    $keys = $values = array();
    foreach($array as $k => $v){
        $keys[] = $k;
        $values[] = !empty($v) ? $v : null;
    }

    // assuming the PDO instance is $pdo
    $query = 'INSERT INTO `'.DB_TABLE_PAGES.'` '.
             '('.implode(',', $keys).') VALUES '.
             '('.implode(',', $placeholders).')';
    $stmt = $pdo->prepare($query);

    $stmt->execute($values);
}

Note: I've used the null constant because the "NULL" string will be escaped as a string (not as a null value).

Why do you cast an empty value to NULL? If I have a zero in my data, I want to insert a zero, not null. — Your Common Sense, Jun 29 '13 at 07:38
The code works excellently except that PHP 7 gives a warning "mysqli_stmt::bind_param() expected to be a reference, value given in..". I resolved it be replacing call_user_func_array with $stmt->bind_param($param_type, ...$params); where param_type is set to str_repeat('s', count($values)); params is set to $values. This fixes the warning. — Naveen Chand K, Jul 01 '22 at 10:26

score 3 · Answer 2 · edited Jul 28 '21 at 13:34

3

I found something a little more concise.

Disclaimer, this works only since PHP 5.6 using the unpacking (splat) operator:

public function genericQueryWithParams($query, $params, $types)
{
    $sql = $this->db->prepare($query));
    $sql->bind_param($types, ...$params);
    $sql->execute();
    return $sql->get_result();
}

edited Jul 28 '21 at 13:34

Dharman

30,962
25
85
135

answered Dec 19 '16 at 21:01

crunchy

705
1
13
35

score 0 · Answer 3 · answered May 30 '11 at 17:03

0

Instead of bind_param (which in my mind is confusing at all times), just do:

$stmt->execute($values);

You can also get rid of your loop by using array_keys() and array_values()

answered May 30 '11 at 17:03

Evert

93,428
18
118
189

when i do that it give me this error: mysqli_stmt::execute() expects exactly 0 parameters, 1 given. thankyou for the tip on the array functions :-) – Adamski May 30 '11 at 17:07
For some reason I thought you were using PDO, where this is possible. I'm totally suprised MySQLi does not have the feature to pass all parameters at once though, but I can't seem to find it in the docs either. In that case you'll need to bind all parameters separately. – Evert May 30 '11 at 17:19
1

This will be supported only as of PHP 8.1. You were ahead of your time with this answer. – Dharman Jul 28 '21 at 13:35

Prepared statements with an array

3 Answers3

Linked