How to insert multiple values in a single query

Question

Please somebody help me. In below code the query will execute 3 times , means query execution will depend on number of elements in array. Please guide me how to run this query with inserting all data at once

 $products = array("shirt" , "paint" , "socks");
    $price = array("200" , "600" , "50");
    $quantity = array("3" , "2" , "2");

        $num = 0; while($num <= count($products))
        {
            $mysqli->query("insert into new_order set 
                            product = '".$products[$num]."' ,
                            price = '".$price[$num]."' , 
                            quantity = '".$quantity[$num]."'
                          ");

                          $num++;
        }

check http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query — Aman Rawat, May 16 '15 at 10:48
You can also use single transaction for multiple queries, which is much more faster than a big query, checkout this : http://stackoverflow.com/a/30018150/3799829 — Bobot, May 16 '15 at 10:50

n-dru · Answer 1 · 2015-05-16T10:54:55.080

2

Iterate over each item in $products to build $sql string:

$sql = "insert into new_order(product, price, quantity) values ";
for($i=0;$i<count($products);$i++){
    $sql .= "({$products[$i]}, {$price[$i]}, {$quantity[$i]}),";
}
$sql = substr($sql,0,-1); //cut off the trailing comma
$mysqli->query($sql);

// insert into new_order(product, price, quantity) values (shirt, 200, 3),(paint, 600, 2),(socks, 50, 2)

edited May 16 '15 at 10:54

answered May 16 '15 at 10:52

n-dru

9,285
2
29
42

don't forget to secure your data ^^ – Bobot May 16 '15 at 10:53
Thank u so much , i get the idea how to do so ,,, Thanks once again – Aftab Ahmad May 16 '15 at 10:57
1

@Bob0t yup dear , i will secure it but i was confused for such idea,, now it solved :) – Aftab Ahmad May 16 '15 at 10:58

Narendrasingh Sisodia · Accepted Answer · 2015-05-16T14:02:06.600

2

It won't throw any error untill you'll be getting same number of values within an array

$counts = count($products);
$query = "insert into new_order (product,price,quantity) values ";
foreach($products as $key => $value){
    $query .= "('$value','$price[$key]','$quantity[$key]')";
    $query .= (++$key == $counts) ? '' : ',';
}
$mysqli->query($query);

Query looks like:

//insert into new_order (product,price,quantity) values('shirt','200','3'),('paint','600','2'),('socks','50','2')

edited May 16 '15 at 14:02

answered May 16 '15 at 10:57

Narendrasingh Sisodia

21,247
6
47
54

@Uchiha pretty, but you do the `count($products)` in loop, should save it before looping ;p – Bobot May 16 '15 at 14:00

How to insert multiple values in a single query

2 Answers2