CODEWITHSUNDEEP
phpmysqlarraysmysqliprepared-statement
Brucie Alpha
Brucie Alpha

Reputation: 1216

Using bind_param with arrays and loops

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?

Upvotes: 4

Views: 2256

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272086

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.

Upvotes: 3

bharat savani
bharat savani

Reputation: 339

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....

Upvotes: -1

Dharman
Dharman

Reputation: 33238

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();
}

Upvotes: 4

Related Questions