MySQL Prepared Statment: Updating multiple values

Question

I created a Form with 10 Inputs that gets there Default Value form a Database.

    <form class="updatePrices" action="includes\updateprices.inc.php" method="POST">

    <?php

    $sql = "SELECT * FROM price ";
    $results = mysqli_query($conn, $sql) or die();
    $name = "90";

    while($row = mysqli_fetch_assoc($results)) {
      echo " " . $row["name"]. " <input type='text' name=".$row['id']." value={$row['value']}><br>";
      echo " <input type='hidden' name=".$name." value={$row['id']}>";
      $name++;
    }
    echo  '<button type="submit" name="update-submit">Ok</button>';
?>
</form>

Now I want the Form to update the Database based on the new Input Values.

<?php
require 'dbh.inc.php';

$id = [$_POST['90'],$_POST['91'],$_POST['92'],$_POST['93'],$_POST['94'],$_POST['95'],$_POST['96'],$_POST['97'],$_POST['98'],$_POST['99']];
$price = [$_POST['1'],$_POST['2'],$_POST['3'],$_POST['4'],$_POST['5'],$_POST['6'],$_POST['7'],$_POST['8'],$_POST['9'],$_POST['10']];
$sql= "UPDATE price SET value= ? WHERE id = ?";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
    header("Location: ../manage.price.php?error=sqlerror");
    exit();
}else {

    mysqli_stmt_bind_param($stmt, "ii", $price, $id);

    for($i=0;$i<10;$i++){
        $id[$i];
        $price[$i];
        mysqli_stmt_execute($stmt);
    }
    header("Location: ../manage.price.php?success");
    exit();
}
mysqli_stmt_close($stmt);
mysqli_close($conn);

but for some reason my code only updates the first Value to 1(doesnt matter what the input value is)

You bind the parameters to your defined arrays, then inside of your for loop, you just execute the statement. `$id[$i]; $price[$i];` is doing nothing at all with those variable values. — aynber, Dec 19 '19 at 16:39
You call `mysqli_stmt_bind_param` with the array itself and not individual values. — Nigel Ren, Dec 19 '19 at 16:41
Somewhat relevant: https://stackoverflow.com/questions/36777813/using-bind-param-with-arrays-and-loops/58809686#58809686 — Dharman, Dec 19 '19 at 16:42

score 1 · Accepted Answer · answered Dec 19 '19 at 16:53

You have to set the new value for the 2 parameters into the prepared statement each time round the loop and then execute() the statement with the new values, and as suggested in comments you dont need to move the array value into scalars first, you can use the array directly

}else {

    for($i=0;$i<10;$i++){
        mysqli_stmt_bind_param($stmt, "ii", $price[$i], $id[$i]);
        mysqli_stmt_execute($stmt);
    }


}

And if you use the OO syntax i think its wasier to read

}else {

    for($i=0;$i<10;$i++){
        $stmt->bind_param('ii', $price[$i], $id[$i]);
        $stmt->execute();
    }

}

MySQL Prepared Statment: Updating multiple values

1 Answers1