sql update multiple column in a foreach loop using prepared statement

Question

Im studying this PHP script on how to prepare for multiple execution of a UPDATE statement. The script below shows update for 1 column using prepared statement.

Example from PHP manual https://www.php.net/manual/en/function.sqlsrv-prepare.php

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array( "Database"=>"dbName", "UID"=>"username", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false) {
    die( print_r( sqlsrv_errors(), true));
}

$sql = "UPDATE Table_1
        SET OrderQty = ?
        WHERE SalesOrderID = ?";

// Initialize parameters and prepare the statement. 
// Variables $qty and $id are bound to the statement, $stmt.
$qty = 0; $id = 0;
$stmt = sqlsrv_prepare( $conn, $sql, array( &$qty, &$id));
if( !$stmt ) {
    die( print_r( sqlsrv_errors(), true));
}

// Set up the SalesOrderDetailID and OrderQty information. 
// This array maps the order ID to order quantity in key=>value pairs.
$orders = array( 1=>10, 2=>20, 3=>30);

// Execute the statement for each order.
foreach( $orders as $id => $qty) {
    // Because $id and $qty are bound to $stmt1, their updated
    // values are used with each execution of the statement. 
    if( sqlsrv_execute( $stmt ) === false ) {
          die( print_r( sqlsrv_errors(), true));
    }
}
?>

What if I have multiple column to update, how do I create an array to bound multiple variables to a prepared statement in foreach?

New update SQL statement with 3 columns.

$sql = "UPDATE Table_1
        SET OrderQty = ?,
        SET ProductName = ?,
        SET ProductPRice = ?
        WHERE SalesOrderID = ?";

Answer 1

You may try to build the array with the actual parameters values differently. And fix the syntax of the UPDATE statement:

<?php
$serverName = "serverName\sqlexpress";
$connectionInfo = array("Database" => "dbName", "UID" => "username", "PWD" => "password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if ($conn === false) {
    die( print_r( sqlsrv_errors(), true));
}
$sql = "
    UPDATE Table_1
    SET OrderQty = ?, ProductName = ?, ProductPrice = ?
    WHERE SalesOrderID = ?
";

// Initialize parameters and prepare the statement. 
// Variables $qty and $id are bound to the statement, $stmt.
$qty = 0; $name = ""; $price = 0.00; $id = 0;
$stmt = sqlsrv_prepare($conn, $sql, array(&$qty, &$name, &$price, &$id));
if ($stmt === false) {
    die( print_r( sqlsrv_errors(), true));
}

// Set up the SalesOrderDetailID and OrderQty information. 
// This array maps the order ID to order quantity in key=>value pairs.
$orders = array(
    array("qty" => 10, "name" => "Product1", "price" => 10.01, "id" => 1),
    array("qty" => 20, "name" => "Product2", "price" => 10.02, "id" => 2),
    array("qty" => 30, "name" => "Product3", "price" => 10.03, "id" => 3)
);

// Execute the statement for each order.
foreach ($orders as $order) {
    // Because $id and $qty are bound to $stmt1, their updated
    // values are used with each execution of the statement. 
    $qty   = $order["qty"];
    $name  = $order["name"]; 
    $price = $order["price"];
    $id    = $order["id"];
    if (sqlsrv_execute($stmt) === false) {
        die( print_r( sqlsrv_errors(), true));
    }
}

// End
sqlsrv_free_stmt($stmt);  
sqlsrv_close($conn); 
?>