build prepared statement from array and pass variable to execute

Question

How to pass variable in loop to execute ??? example from one answer here...

$placeholders = array_fill(0, count($array), '?');

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

$stmt = $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();

but what about multiple array. I want add to db 10000 values but not build and bind statement every pass.. Is it possible ? So I want build statement from array, bind params (i don't know how). Than in loop pass variable (identificated by key) and execute...

something universal if I don't wont write statement for every table (just make array of column names and variables)

PDO makes life *soooo* much easier in this respect. Is it available on your server? — DaveRandom, Sep 04 '12 at 11:43
Hope yes... can you help me.... I know it is similar but I don't have experience with it.... — Robert Mulinsky, Sep 04 '12 at 12:25
Well with PDO you could just pass `$array` to `execute()` as an argument, no need to mess about with `bind_param()` — DaveRandom, Sep 04 '12 at 12:32

score 0 · Answer 1 · answered Oct 31 '12 at 18:04

When using prepared statements and mysqli all you need to do is change the values stored in our bound parameters and run execute a second time. This is the point of bound parameters, to allow multiple execution of the statement without the overhead of re-sending the query (also a great security plus by nature but that is beside the point.)

Take for example:

$param1 = '';
$param2 = '';
$param3 = '';
$param4 = '';
$query = 'INSERT INTO table VALUES (?, ?, ?, ?)';
$format = 'ssss';
$params = array(&$param1, &$param2, &$param3, &$param4);
$stmt = $mysqli->stmt_init();
$stmt->prepare($query);
$params = array_merge(array($format), $params); #set up format and parameters for binding
call_user_func_array(array($stmt, 'bind_param'), $params); #parameters specified so bind them


foreach($data as $data_point){
    $param1 = $data_point[0];
    $param2 = $data_point[1];
    $param3 = $data_point[2];
    $param4 = $data_point[3];
    $stmt->execute();
}

This will prepare your statement and then loop through the data stored in $data taking each of the values, putting it into your prepared parameters and then execute the statement with the new parameters.

Mileage will vary on this particular code because it doesn't do any checking on the sql error statement and it may be much more efficient to do several rows in one insert for many applications.

build prepared statement from array and pass variable to execute

1 Answers1