How to update multiple rows in php mysql

Question

I have some code to update multiple row in php mysql like this.

<?php 

$idOrder = $_GET['idOrder'];

$conn = new MySQLi('localhost','root','','project_ecommerce');

$query_select = "SELECT status FROM order_product WHERE id_order='".$idOrder."'";
$sql_select = $conn->query($query_select);
$result_select = $sql_select->fetch_assoc();
$status ='';

if ($result_select['status'] == 0) {
    $status .= 1;
}else{
    $status .= 0;
}

$query_update = "UPDATE order_product SET status='".$status."' WHERE id_order='".$idOrder."'";
$sql_update = $conn->query($query_update);

if ($sql_update == TRUE) {

    $query_select_product = "SELECT order_product.id_product AS ID_PRD, order_product.status AS STATUS, order_product.qty AS QTY_ORD, products.stock AS STOCK FROM order_product JOIN products ON order_product.id_product = products.id_product WHERE order_product.id_order ='".$idOrder."'";
    $sql = $conn->query($query_select_product);
    $result = $sql->fetch_all(MYSQLI_ASSOC);
    $stock_update='';

    for ($i=0; $i < count($result); $i++) { 

        if ($result[$i]['STATUS'] == 0) {

            $stock_update .= ($result[$i]['STOCK']+$result[$i]['QTY_ORD']);

        }else{

            $stock_update .= ($result[$i]['STOCK']-$result[$i]['QTY_ORD']);

        }

        $update_product = "UPDATE products SET stock='".$stock_update."' WHERE id_product='".$result[$i]['ID_PRD']."'";
        $sql_update_product = $conn->query($update_product);

    }   

}

echo $idOrder;
?>

if status updated to 1 i give result like this:

+----------------+-------+
| id_product     | stock |
+----------------+-------+
| PRD-0416-17-1  |   100 |
| PRD-0416-17-10 |   100 |
| PRD-0416-17-11 |    98 |
| PRD-0416-17-12 |  9898 |
+----------------+-------+

And if status updated to 0 i give result like this:

+----------------+---------+
| id_product     | stock   |
+----------------+---------+
| PRD-0416-17-1  |     100 |
| PRD-0416-17-10 |     100 |
| PRD-0416-17-11 |     100 |
| PRD-0416-17-12 | 1009900 |
+----------------+---------+

How i can fix it ?

Your code is vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection). You should use [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Alex Howansky, Apr 17 '17 at 15:29
You're using `.=` in your assignments, so you're concatenating the value from one row to the value from the previous row. — Barmar, Apr 17 '17 at 15:30
BTW, this whole thing can be done in one `UPDATE+JOIN` query, you don't need the PHP loop. — Barmar, Apr 17 '17 at 15:32
Why do you have the same thing in both the `if` and `else` blocks of `if($result[$i]['STATUS'] == 0)`? — Barmar, Apr 17 '17 at 15:36
@Barmar because I want to sum the values in the column ' stock ' if ' status ' == 0 and decrease the value in the column ' stock ' if ' status ' = 1 — Imam Ali Mustofa, Apr 17 '17 at 15:43
@BettaDev I understand that, I said never mind. I didn't see the difference earlier. — Barmar, Apr 17 '17 at 15:45

score 2 · Answer 1 · answered Apr 17 '17 at 15:39

You're using .= in these assignments, which concatenates the results from the previous row to the previous value of $stock_update. They should just be =.

    if ($result[$i]['STATUS'] == 0) {
        $stock_update = ($result[$i]['STOCK']+$result[$i]['QTY_ORD']);
    }else{
        $stock_update = ($result[$i]['STOCK']-$result[$i]['QTY_ORD']);
    }

This entire thing could be done in a single query:

UPDATE order_product AS o
JOIN product AS p ON o.id_product = p.id_product
SET o.status = NOT o.status,
    p.stock = IF(o.status = 0, p.stock + o.qty_ord, p.stock - o.qty_ord)
WHERE o.id_order = $idOrder

Thanks very much @Barmar – Imam Ali Mustofa Apr 17 '17 at 15:49 — Imam Ali Mustofa, Apr 17 '17 at 15:49

How to update multiple rows in php mysql

1 Answers1