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++;
        }

You can also use single transaction for multiple queries, which is much more faster than a big query, checkout this : stackoverflow.com/a/30018150/3799829 — Bobot
– Bobot, Commented May 16, 2015 at 10:50

Narendrasingh Sisodia · Accepted Answer · 2015-05-16 14:02:06Z

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, 2015 at 14:02

answered May 16, 2015 at 10:57

Narendrasingh Sisodia

21.4k6 gold badges51 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bobot Over a year ago

@Uchiha pretty, but you do the count($products) in loop, should save it before looping ;p

n-dru · Accepted Answer · 2015-05-16 10:54:55Z

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, 2015 at 10:54

answered May 16, 2015 at 10:52

n-dru

9,4402 gold badges31 silver badges43 bronze badges

3 Comments

Bobot Over a year ago

don't forget to secure your data ^^

Aftab Ahmad Over a year ago

Thank u so much , i get the idea how to do so ,,, Thanks once again

Aftab Ahmad Over a year ago

@Bob0t yup dear , i will secure it but i was confused for such idea,, now it solved :)

Collectives™ on Stack Overflow

How to insert multiple values in a single query

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related