Using bind_param with arrays and loops

php bind_param array
bind_param php
php bind_param date
php prepared statements select
mysqli bind_param multiple parameters
call_user_func_array bind_param
bind_param error
mysqli_prepare

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?

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

mysqli_stmt::bind_param - Manual, mysqli_stmt::bind_param -- mysqli_stmt_bind_param — Binds variables to a Re-assigning the array in the while()-head generates a new array, whereas This will iterate the given list of parameters and replace any null values in the query� the mysqli stmt bind param (mysqli_stmt_bind_param) function only takes one variable at a time, so its difficult to pass in a few variables to fill in the placeholder space. this allows mysqli prepared statements with variable arguments, one sql template with multiple placeholders to be prepared and excuted. hope this helps somone, Mahees.

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.

INSERT multiple records? - PHP, bind using an array instead of static values. This is also why I prefer PDO, because you can simply loop the bindParam calls, making it much more readable . Loops While Loop Do While Loop For Loop Foreach Loop Arrays Indexed Arrays Associative Arrays Multidimensional Arrays have a look at the bind_param() function:

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

Solved: Using Array Result from Prepared Statement as Para , loop through the results to store them in an array. $selected_positions = []; $ stmt->bind_param('i', $_GET[$position_id]);. // execute the query, and fetch the� Tag: php,loops,mysqli,foreach,prepared-statement. in my input text i just input. biografi, it's should only insert 1 row, but it's insert 5 times (maybe from this -> my category table contain 5 category) if i input. bigrafi,dictionary, its insert 10 times

PHP MySQLi Prepared Statements Tutorial to Prevent SQL Injection, The bind_param() method is where you attach variables to the dummy To get the entire array in one command, without a loop, you'd use� A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

Mysqli SELECT query with prepared statements, You must always use prepared statements for any SQL query that would using a while loop but rather collect it into array and then use this array for the output. Before I start, if you'd like to see an even easier way to use MySQLi prepared statements, check out my wrapper class. Also, here's a great resource to learn PDO prepared statements, which is the better choice for beginners and most people in general. A hack attempt has recently been discovered, and it appears they are trying to take down the entire database. An impromptu staff meeting has bee

BountyBot on Twitter: "Using bind_param with arrays and loops https , Keyboard Shortcuts. Keyboard shortcuts are available for common actions and site navigation. View Keyboard Shortcuts Dismiss this message. Definition and Usage. The fetch_array() / mysqli_fetch_array() function fetches a result row as an associative array, a numeric array, or both. Note: Fieldnames returned from this function are case-sensitive.

Comments
  • 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.
  • ok thx. Is there a better way to connect if I have an array like $alias?
  • @YourCommonSense This is amazing. Thank you so much. I really appreciate this. If there is anything I can improve in my answers, please let me know.
  • You don't have to intialize or declare a variable to be used in bind_param.