SQL statement in while loop

Question

For some reason my SQL UPDATE statement is not working.

    $con=mysqli_connect("localhost","admin","password","db");
// Check connection
if (mysqli_connect_errno())
    {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }

$items = array();
$desc = array();
$price = array();
$quantity = array();
$p=0;
    while($inventorynumber>$p)
    {
    $invid[$p] = $_POST['invid'.$p.''];
    $items[$p] = $_POST['item'.$p.''];
    $desc[$p] = $_POST['desc'.$p.''];
    $price[$p] = $_POST['price'.$p.''];
    $quantity[$p] = $_POST['quantity'.$p.''];
    $sql =  "UPDATE `inventory_db` SET `item`='$items[$p]',`description`='$desc[$p]',`quantity`='$quantity[$p]',`price`='$price[$p]' WHERE `invid` = '$invid[$p]'";
    $p++;
    }

Is there a more efficient way of doing this? Or do I need to add in a mysqli_close($con); after?

When I use print_r to check the array values, I get. Array ( [0] => 1132 [1] => 1133 [2] etc. So the php variables are definitely populated. — user3607800, May 10 '14 at 05:53
Your code is vulnerable to SQL injections. You should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). — Gumbo, May 10 '14 at 05:56

score 0 · Answer 1 · answered May 10 '14 at 05:54

I would run the sql query in the loop itself. Unless you're using the array somewhere later, you can simplify this. It's not clear where $inventorynumber is being set. Also, remember to set autocommit.

mysqli_autocommit($con, TRUE);
$p=0;
while($inventorynumber > $p) {
    $invid = $_POST['invid'.$p.''];
    $item = $_POST['item'.$p.''];
    $desc = $_POST['desc'.$p.''];
    $price = $_POST['price'.$p.''];
    $quantity = $_POST['quantity'.$p.''];
    $sql =  "UPDATE `inventory_db` SET `item`='$items[$p]',`description`='$desc[$p]',`quantity`='$quantity[$p]',`price`='$price[$p]' WHERE `invid` = '$invid[$p]'";
    mysqli_query($conn, $sql);
    $p++;
}
mysqli_close($conn);

Inventory number is being set in $_SESSION['inventorynumber'] but on second thought I might change it to $_POST — user3607800, May 10 '14 at 05:59

SQL statement in while loop

1 Answers1