Using bind_param with arrays and loops

Question

according to this example for prepared statements I first bind parameters and then set values for the parameters.

Let's assume I have a 2-dim array $alias

$array1 = [
    'id' => 1,
    'tstamp' => 123456789,
    'alias' => '$alias',
];

$array2 = [
    'id' => 1,
    'tstamp' => 123456789,
    'alias' => '$alias2',

];

$alias = [$array1, $array2];

Why is this code working

$insert = 'INSERT INTO abcdef VALUES (?,?,?)';
$insertStmt = $conn->prepare($insert);
foreach ($alias as $array) {
    $insertStmt->bind_param('iis', $array['id'], $array['tstamp'], $array['alias']);
    $insertStmt->execute();
}

and this not?

$insert = 'INSERT INTO abcdef VALUES (?,?,?)';
$insertStmt = $conn->prepare($insert);
$insertStmt->bind_param('iis', $array['id'], $array['tstamp'], $array['alias']);
foreach ($alias as $array) {
   $insertStmt->execute();
}

If I have to bind the parameters all the time there's a lot more trafic, isn't it?

binding basically sets up a reference between the placeholder and the bound variable. when you do a `foreach()`, `$array` will be deleted/recreated on each loop iteration, so whatever you bound to outside of the loop will no longer exist inside the loop. — Marc B, Apr 21 '16 at 18:46
ok thx. Is there a better way to connect if I have an array like $alias? — Brucie Alpha, Apr 21 '16 at 19:30

score 4 · Answer 1 · edited Nov 12 '19 at 09:03

bind_param() binds by reference to a specific zval container. On each iteration of the loop a new array symbol table is allocated with its own zval containers. If at the time of binding the zval containers do not exist they will be created. This can be shown with the following code:

$insertStmt = $conn->prepare('INSERT INTO abcdef VALUES (?,?,?)');
$insertStmt->bind_param('sss', $array['id'], $array['tstamp'], $array['alias']);
var_dump($array);

outputs:

array (size=3)
  'id' => null
  'tstamp' => null
  'alias' => null

Even though we didn't declare the $array anywhere, the binding implicitly created it with null values. Bindings will keep on pointing to this empty array.

Of course when we start iterating the alias array, the $array will be created anew each time. The old array symbol table, to which we bound the parameters is now gone. We haven't bound anything to the new array.

To solve this you can simply move the bind_param() inside the foreach loop as:

$insertStmt = $conn->prepare('INSERT INTO abcdef VALUES (?,?,?)');
foreach ($alias as $array) {
    // bind to the new zval containers every iteration
    $insertStmt->bind_param('sss', $array['id'], $array['tstamp'], $array['alias']);
    $insertStmt->execute();
}

Salman A · Answer 2 · 2019-11-14T18:50:42.567

If I have to bind the parameters all the time there's a lot more traffic, isn't it?

The traffic is generated on prepare and execute events.

Having said that, you can re-arrange your a little to move ->bind_param() outside the loop. The simpler approach is to bind variables instead of array:

$array1 = ['id' => 1, 'tstamp' => 123456789, 'alias' => '$array1'];
$array2 = ['id' => 1, 'tstamp' => 123456789, 'alias' => '$array2'];
$arrays = [$array1, $array2];

$insert = 'INSERT INTO abcdef VALUES (?,?,?)';
$insertStmt = $conn->prepare($insert);

$bv_id      = NULL;
$bv_tstamp  = NULL;
$bv_alias   = NULL;
$insertStmt->bind_param('iis', $bv_id, $bv_tstamp, $bv_alias);

foreach ($arrays as $array) {
    // you could use extract($array, ...) below
    // but its use is highly discouraged
    $bv_id     = $array['id'];
    $bv_tstamp = $array['tstamp'];
    $bv_alias  = $array['alias'];
    $insertStmt->execute();
}

If you must use arrays then use this approach:

$array = ['id' => null, 'tstamp' => null, 'alias' => null];
$insertStmt->bind_param('iis', $array['id'], $array['tstamp'], $array['alias']);
foreach ($arrays as $copy) {
    foreach ($copy as $k => $v) {
        $array[$k] = $v;
    }
    $insertStmt->execute();
}

Notice that I used a different name for the loop variable inside the foreach construct (we need to keep track of the array that is bound to the statement and loop variables get overwritten on each iteration). You will have to manually copy the values from the loop variable to the bound array.

score -1 · Answer 3 · answered Nov 13 '19 at 09:24

In your case

this code is working

$insert = 'INSERT INTO abcdef VALUES (?,?,?)';
$insertStmt = $conn->prepare($insert);
foreach ($alias as $array) {
    $insertStmt->bind_param('iis', $array['id'], $array['tstamp'], $array['alias']);
    $insertStmt->execute();
}

because for array iteration we use for loop or foreach to extract key by key array values so in this code value in your $array variable will set one by one value from $alias array

And in this code

$insert = 'INSERT INTO abcdef VALUES (?,?,?)';
$insertStmt = $conn->prepare($insert);
$insertStmt->bind_param('iis', $array['id'], $array['tstamp'], $array['alias']);
foreach ($alias as $array) {
   $insertStmt->execute();
}

You have used $array variable which is not intialized or declared in any upper area scope so it will get null values so every time execute function will get null value....

You don't have to intialize or declare a variable to be used in bind_param. — Your Common Sense, Nov 13 '19 at 09:38

Using bind_param with arrays and loops

3 Answers3

Linked

Related