mysqli bind_params for an insert query

Question

I'm trying to use call_user_func_array and mysqli_stmt::bind_param like so:

# A. prepare an insert query statement
$this->_stmt = $db_link->prepare('INSERT INTO foo (col1, col2) VALUES (?,?)');

# B. bind a placeholder array to the statement
$bound = array('col1' => null, 'col2' => null);
call_user_func_array(array($this->_stmt, 'bind_param'), 
  array($types_string, &$bound));

# C. while there are records, retrieve, munge, and insert
while ($row = $res->fetch_assoc()) {
  $bound = transform($row); # $bound remains an array indexed as 'col1', 'col2'
  $this->_stmt->execute();  # PHP Notice:  Array to string conversion
}

I'm getting confused by PHP references, leading to the array to string conversion. Either I'm not binding the placeholder array correctly under step B, or I'm not assigning to the placeholder correctly in step C.

(Similar questions have been asked before, but I haven't found an answer to mine.)

Answer 1

You're passing

array(
  types,
  &array(
    a, b, c
  )
)

to call_user_func_array() but it has to be

array(
  types, &a, &b, &c
)

One way to accomplish that is to use another (temporary) array with all the elements of the "original" array as references. http://docs.php.net/call_user_func_array:

Note: Referenced variables in param_arr are passed to the function by a reference, others are passed by a value. [...]

E.g.

$mysql = new mysqli('localhost', 'localonly', 'localonly', 'test');
// test table
$mysql->query('CREATE TEMPORARY TABLE foo (a int, b int, c int)') or die($mysql->error);

$params = array('a'=>null, 'b'=>null, 'c'=>null);
$types = 'iii';

$stmt = $mysql->prepare('INSERT INTO foo (a,b,c) VALUES (?,?,?)');
// make an array of references to the original array
$tmp = array($types);
foreach( $params as &$p ) {
  $tmp[] = &$p;
}
call_user_func_array( array($stmt, 'bind_param'), $tmp);

// test insert
for($i=0; $i<10; $i++) {
  $params['a'] = $i;
  $params['b'] = $i+100;
  $params['c'] = $i+1000;
  $stmt->execute();
}
unset($stmt);

// test select
$result = $mysql->query('SELECT * FROM foo');
while( null!==($row=$result->fetch_row()) ) {
  echo join(',', $row), "\n";
}