Using arrays/strings for mysqli prep statement

Question

I'm running into an issue with a mysqli prepared statement. It's probably some rookie mistake; I'm not too familiar with things like this. Any help is appreciated.

I have two arrays, $alpha[] and $bravo[]. Both have 20 pairings, but to simplify:

$alpha = array('name', 'age', 'color');
$bravo = array(
    'name'  => 'John Doe',
    'age'   => 22,
    'color' => 'blue',
);

I want to insert the data from $bravo[] into my table by defining the column headers with $alpha[] and then binding the data from $bravo[] to the query, and then executing the query. Here's an example of what I want to do:

$columns = implode(',', $alpha);
$query_values = '?,?,?';
$query   = "INSERT INTO table ($columns) VALUES ($query_values)";

$type = 'sis';
$real_values = implode(',', $bravo);

if($stmt = $mysqli->prepare($query)){
   $stmt->bind_param($type, $real_values);
   if($stmt->execute()){
      // success
   }
}

This is not working for me - any help or insight you guys can offer (including other ways to accomplish what I want to do) is much appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

change the bind_param to

$stmt->bind_param($type, $bravo[0], $bravo[1], $bravo[2]);

While the above works, it's not the perfect solution as it requires you to change the parameters for mysqli_bind_param every time you want to add/remove column data.

Here's the solution, in an example of the proper workflow:

// returns an array of associative values
function ref_array_keys($arr) {
  $refs = array();

  foreach ($arr as $key => $value)
  {
      $refs[$key] = &$arr[$key];
  }

  return $refs; 
}

$data = array('name' => 'John Doe', 'email' => 'user@domain.com', 'color' => 'red');
$type = 'sss';

$cols = implode(',', array_keys($data));
$vals = implode(',', array_fill(0, count($data), '?'));
$query = "INSERT INTO table_name ($cols) VALUES ($vals)";

if ($stmt = new mysqli($query)) 
{
  call_user_func_array(array($stmt, "bind_param"), array_merge(array($type), ref_array_keys($data)));
}

ref_array_keys() function source: https://stackoverflow.com/questions/3681262/...

edited May 23 '17 at 12:20

Community

1
1

answered May 31 '14 at 07:24

Fabricator

12,722
2
27
40

While this does accomplish my goal, it's not exactly what I'm looking for. Is there no way to just plop all of the values from the entire array in there instead of writing out all 20+ references? – May 31 '14 at 07:39
2

I've used call_user_func_array to accomplish this. be careful it needs slightly different treatment depending on the php version. see http://stackoverflow.com/questions/3681262/php5-3-mysqli-stmtbind-params-with-call-user-func-array-warnings – Fabricator May 31 '14 at 07:43
Thanks. I marked your answer as the correct one, and will edit your post to reflect that additional information lies in the comments. – May 31 '14 at 15:58

Using arrays/strings for mysqli prep statement

1 Answers1