Insert multiple rows in sql via $stmt->bind_param

Question

I want to insert in my database multiple rows from an array.

Currently even though my code is looping over an array it inserts only a single row.

This is my code:

$counter = count($the_order);
for($i = 0;$i<$counter;$i++){
    list($quantity, $name_prod, $vial_type, $price) = $the_order[$i];
    $priceitem = $price/$quantity;

    $sql_query = "INSERT INTO tbl_order_items(Prod_name, Vial_type, Prod_price, Prod_qty, Prod_total) 
                VALUES (?, ?, ?, ?, ?)";

    $stmt = $connect->stmt_init();
    if($stmt->prepare($sql_query)) {    
        // Bind your variables to replace the ?s
        $stmt->bind_param('sssss', 
                $name_prod,
                $vial_type, 
                $priceitem, 
                $quantity,  
                $price
                );
        // Execute query
        $stmt->execute();
        $result = $stmt->affected_rows;
        // store result 
        //$result = $stmt->store_result();
        $stmt->close();
    }
}

the updated code (print_r):

Array ( 
  [0] => Array ([0] => 5 [1] => Gascure Sirop [2] => [3] => 72.5 RON ) 
  [1] => Array ( [0] => 5 [1] => Graviola Star [2] => 100 Tablete [3] =>      277.5 RON ) 
  [2] => Array ( [0] => 1 [1] => Graviola Star [2] => 100 Tablete [3] => 0.0 RON ) )

Do you mean even though the INSERT is in a loop, it only stores one row? — RiggsFolly
– RiggsFolly, Commented Aug 21, 2018 at 11:09
Yes, even though the INSERT is in a loop. I don't know where is the problem :( — Iulian Nistor
– Iulian Nistor, Commented Aug 21, 2018 at 11:13
Not sure of the actual issue - but there is code that definitely shouldn't be in the loop - creating the prepared statement repeatedly is expensive and should only be done once. — L McClean
– L McClean, Commented Aug 21, 2018 at 11:14

RiggsFolly · Accepted Answer · 2018-08-21 11:46:50Z

First you should remember that one of the main benefits of using a prepared statement is that you can prepare the statement ONCE and execute it many times. Each time changing the values of the parameters. This speeds things up as the database only has to compile and optimize the query once. So move the prepare outside the loop

Secondly, if you want to do many database changes in one go, a transaction wrapped round those things is a grate idea, so you get all the INSERT/UPDATE or none of them if something goes wrong in the middle, so you dont leave your database in a nonsense state.

Finally, when testing it is a good idea, specially if you are developing on a live server where errors are probably being supressed to set error reporting On so you see any errors on the browser. But remember to take this out once the script is tested, you dont want user to see this much information about any error as it only helps hackers.

ini_set('display_errors', 1); 
ini_set('log_errors',1); 
error_reporting(E_ALL); 
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);


$sql = "INSERT INTO tbl_order_items
                (Prod_name, Vial_type, Prod_price, Prod_qty, Prod_total) 
            VALUES (?, ?, ?, ?, ?)";
$stmt = $connect->prepare($sql);
if ( !$stmt) {
    // report error and exit, you can do this
    exit;
}

// begin Transaction
$connect->begin_transaction();

$counter = count($the_order);
for($i = 0;$i<$counter;$i++){
    list($quantity, $name_prod, $vial_type, $price) = $the_order[$i];
    $priceitem = $price/$quantity;

    $stmt->bind_param('sssss', 
                $name_prod,
                $vial_type, 
                $priceitem, 
                $quantity,  
                $price);
        // Execute query
    $res = $stmt->execute();
    if ( !$res ) {
        echo $connect->error;
        $connect->rollback();
        exit;
    }
    $result = $stmt->affected_rows;

}
$connect->commit();

Finally you may like to check the Data Types of the columns. You are using 'sssss' and so treating all as text, but possibly for example the quantity and price and priceitem may not be text.

But if have set the error reporting at least you should see the errors if there are any related to the data types.

I also notice that your price field contains RON, a Currency code. It would be a much better idea to NOT put that in the price field. If everything is in RON on your site its unnecessary, if not everything is in RON then I would suggest a seperate column in the table to hold that information. Then your price field could be changed to a numeric which would allow you to do simple calulations in SQL wher as it stands using a char datatype would mean you cannot do simple aritmetic because of the RON meaning the column type has to be char of some sort

Collectives™ on Stack Overflow

Insert multiple rows in sql via $stmt->bind_param

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related