PHP for loop Insert SQL

Question

can you please explain me why this code doesn't insert int the database?

    //INSERT VALUES IN ORDERS
    $sqlInsert = "";
        for($i = 0; $i < count($_SESSION['cart']); $i++)
        {
            $resSelect = mysqli_fetch_assoc($sqlContent);

            $prodID = $resSelect['ProdID'];
            $price = $resSelect['Price'];
            $quantity = $_SESSION['cart'][$resSelect['ProdID']];
            $sum = ($_SESSION['cart'][$resSelect['ProdID']] * 
            $resSelect['Price']);
            $sqlInsert .= "INSERT into Order (ProdID, 
            Quantity, Price,  Sum, OrderID) 
            VALUES ($prodID, $quantity, $price, $sum, $userID);";

        }
        mysqli_query($dbLink, $sqlInsert);

that's the output of var_dump($sqlInsert):

       INSERT INTO Order (ProdID, quantity, 
       Price, Sum, OrderID) VALUES (1, 4, 200, 800, 10);
       INSERT INTO Order (ProdID, quantity, 
       Price, Sum, OrderID) VALUES (7, 3, 200, 600, 10);
       INSERT INTO Order (ProdID, quantity, 
       Price, Sum, OrderID) VALUES (9, 3, 200, 600, 10);

this works in the database. and the output of var_dump(mysqli_query($dbLink, $sqlInsert)) is always false.

Many thanks in Advance

You should check for mysqli errors after the insert to get more information. You're using numbers so quoting isn't a big deal, but you may want to use prepared statements and bind_param just to avoid SQL injection issues and any possible future quoting issues. — aynber
– aynber, Commented Jun 5, 2018 at 16:11
Also, ORDER is a keyword in MySQL and must be surrounded with backticks: dev.mysql.com/doc/refman/5.7/en/keywords.html — aynber
– aynber, Commented Jun 5, 2018 at 16:13
mysqli_query will execute just ONE query but you are passing to it multiple queries. You should use (mysqli_multi_query())[w3schools.com/php/func_mysqli_multi_query.asp] — user2399035
– user2399035, Commented Jun 5, 2018 at 16:13
DO NOT use mysqli_multi_query. It doesn't support placeholder values which makes it extremely dangerous. — tadman
– tadman, Commented Jun 5, 2018 at 16:39

Bill Karwin · Accepted Answer · 2018-06-05 16:38:12Z

As other comments above have mentioned, you should always check for errors returned by mysqli_query(). See example code: http://php.net/manual/en/mysqli.error.php

The mysqli_query() function doesn't support executing multiple statements.

I do NOT recommend using mysqli_multi_query(). There is little or no benefit to using it, and it introduces new potential SQL injection vulnerabilities (like the famous Little Bobby Tables cartoon). I spoke with the former Director of Engineering for MySQL, and he said (paraphrasing): "There's no reason for multi-query to exist, it can only do harm."

You should execute the INSERT statements one at a time. There's no reason to append multiple statements together.

If you're concerned about performance overhead of multiple statements, you can append multiple rows to a single INSERT statement. Or you can wrap a series of individual INSERT statements in a transaction.

You might like to read my presentation Load Data Fast! where I compare the performance of various strategies of inserting many rows of data.

tadman · Accepted Answer · 2018-06-05 16:45:32Z

This is exactly what prepared statements are for:

// Note that ORDER is a MySQL reserved keyword and needs special escaping
$stmt = $dbLink->prepare("INSERT into `Order` (ProdID, 
        Quantity, Price, Sum, OrderID) VALUES (?,?,?,?,?)");
$stmt->bind_param('iiddi', $ProdID, $Quantity, $Price, $Sum, $OrderID);

for($i = 0; $i < count($_SESSION['cart']); $i++)
{
    $resSelect = $sqlContent->fetch_assoc();

    $ProdID = $resSelect['ProdID'];
    $Quantity = $_SESSION['cart'][$resSelect['ProdID']];
    $Price = $resSelect['Price'];
    $Sum = $_SESSION['cart'][$resSelect['ProdID']] * $resSelect['Price'];
    $OrderID =  $userID;

    $stmt->execute();
}

There's an alarmingly high number of errors in that original code that would prevent it from working at all, so you'll need to be more careful in the future and work more methodically towards solutions. Build up incrementally, testing as you go, to be sure you don't get in too deep into a solution you don't fully understand.

Collectives™ on Stack Overflow

PHP for loop Insert SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related