In my previous post, you know how to insert single record in database table using MySQLi preapred statements. Now i am going to tell you how to insert multiple record at once in MySQLi database tables.
To insert multiple records at once in MySQLi database table, you will have to include multiple lists of column values enclosed within parentheses and must be separated by (,) commas.
You can also know how many rows are affected by using mysqli_affected_rows.
Please have a look on given example :
- if (!$db->query("INSERT INTO products(id) VALUES (1), (2), (3), (4)")) {
- echo "Error: (" . $db->errno . ") " . $db->error;
- }else{
- print 'Successfully inserted and Total ' .$db->affected_rows .' rows added.';
- }
- ?>
- //product 1
- $product_name1 = '"'.$db->real_escape_string('ExpertPHP').'"';
- $product_price1 = '"'.$db->real_escape_string('60000').'"';
- $product_details1 = '"'.$db->real_escape_string('Provide Online Tutorials').'"';
- //product 2
- $product_name2 = '"'.$db->real_escape_string('Demo').'"';
- $product_price2 = '"'.$db->real_escape_string('60000').'"';
- $product_details2 = '"'.$db->real_escape_string('Demo code').'"';
- //Insert multiple rows
- $insert = $db->query("INSERT INTO products(product_name, product_price, product_details) VALUES
- ($product_name1, $product_price1, $product_details1),
- ($product_name2, $product_price2, $product_details2)");
- if($insert){
- //know total inserted records using mysqli_affected_rows
- print 'Successfully inserted and Total ' .$db->affected_rows .' rows added.';
- }else{
- echo "Error: (" . $db->errno . ") " . $db->error;
- }
- $sql = "INSERT INTO products (product_name, product_details)
- VALUES ('ExpertPHP', 'Online PHP Tutorials');";
- $sql .= "INSERT INTO products (product_name, product_details)
- VALUES ('Demo','Demo code');";
- if ($db->multi_query($sql) === TRUE) {
- echo "records inserted successfully";
- } else {
- echo "Error: " . $sql . "
" . $db->error;- }
- $db->close();
