How can I insert multiple rows into database with single submit

Question

Can anyone here help me on how can I use PHP to insert multiple rows into a database with a single submit? I have tried doing it, but it only inserts one row.

Here is my code:

<?php
if(isset($_POST['insertData']))
{
   $pred1 =$_POST['pre']; 
    $np1 =$_POST['nap']; 
    $sd101 =$_POST['tdisease'];
    $pr1 =$_POST['pric1'];  
    $ivd =$_POST['invd'];
    $id =$_POST['user'];

     $pred1 =$_POST['pre1']; 
    $np1 =$_POST['nap1']; 
    $sd101 =$_POST['tdisease1'];
    $pr1 =$_POST['pric1'];

     $pred2 =$_POST['pre2']; 
    $np2 =$_POST['nap2']; 
    $sd102 =$_POST['tdisease2'];
    $pr2 =$_POST['pric2'];



      $insert_user="INSERT INTO invoices(id, icd10, nappi_code, prescription, price, invoice_date, pid) VALUES ('','$sd10', '$np' ,'$pred','$pr','$ivd','$id');";
       $insert_user .="INSERT INTO invoices(id, icd10, nappi_code, prescription, price, invoice_date, pid) VALUES ('','$sd101', '$np1' ,'$pred1','$pr1','$ivd','$id');";
        $insert_user .="INSERT INTO invoices(id, icd10, nappi_code, prescription, price, invoice_date, pid) VALUES ('','$sd102', '$np2' ,'$pred2','$pr2','$ivd','$id')";


    if(mysqli_query($con,$insert_user))
    {  
        echo"<script>alert(' Invoice Details successfuly added to database')</script>";
        echo '<meta content="1;generate-invoive-results-date-report.php?id='.$id.'" http-equiv="refresh" />';// redirects user view page after 3    
    }else{  
        echo"<script>alert('Unknown error occured')</script>";   
  } 
}
?>

what do you get? what error is shown? how many rows get saved? — Rotimi, Feb 15 '17 at 07:44
`insert into table (column1, column2) values ('x', 'z'), ('v', 'r'), ..... ` and so on — hassan, Feb 15 '17 at 07:44
You should have a look at this http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query — Dass Prakash, Feb 15 '17 at 07:46
it only saves one row, and others are created blank not with the values that they have — Morena, Feb 15 '17 at 07:52

score 1 · Answer 1 · answered Feb 15 '17 at 07:43

1

My guess is that the first insert completes, and then the subsequent two are being ignored. But why don't you insert all data in a single statement?

$insert_user="INSERT INTO invoices(id, icd10, nappi_code, prescription, price, invoice_date, pid) VALUES ('','$sd10', '$np' ,'$pred','$pr','$ivd','$id'),";
$insert_user .="('','$sd101', '$np1','$pred1','$pr1','$ivd','$id'),";
$insert_user .="('','$sd102', '$np2' ,'$pred2','$pr2','$ivd','$id');";

answered Feb 15 '17 at 07:43

Tim Biegeleisen

502,043
27
286
360

i just tried the above solution you proposed, it only inserts a single row and ignores others – Morena Feb 15 '17 at 07:52
Then you have a problem with your PHP code. The query is fine. – Tim Biegeleisen Feb 15 '17 at 07:53
yes the query is actually fine, but thanks i will look into my code again – Morena Feb 15 '17 at 07:57
Are you sure the variables/parameters used in the second and third query have actual values? – Tim Biegeleisen Feb 15 '17 at 08:00
yes 100% sure, because when i play around with it, and put the last query first, i get its values – Morena Feb 15 '17 at 08:08
This unstable/insecure querying technique should no be used by anyone. A prepared statement with placeholders should be used. – mickmackusa Aug 15 '20 at 13:39

Ashil John · Accepted Answer · 2017-02-15T08:53:50.697

You should try this perhaps:

$insert_user="INSERT INTO invoices(id, icd10, nappi_code, prescription, price, invoice_date, pid) VALUES ('','$sd10', '$np' ,'$pred','$pr','$ivd','$id'),('','$sd101', '$np1' ,'$pred1','$pr1','$ivd','$id'), ('','$sd102', '$np2' ,'$pred2','$pr2','$ivd','$id')";

And also, This:

$pred1 =$_POST['pre']; 
$np1 =$_POST['nap']; 
$sd101 =$_POST['tdisease'];
$pr1 =$_POST['pric1'];  
$ivd =$_POST['invd'];
$id =$_POST['user'];

$pred1 =$_POST['pre1']; 
$np1 =$_POST['nap1']; 
$sd101 =$_POST['tdisease1'];
$pr1 =$_POST['pric1'];

$pred2 =$_POST['pre2']; 
$np2 =$_POST['nap2']; 
$sd102 =$_POST['tdisease2'];
$pr2 =$_POST['pric2'];

Should've been:

$ivd =$_POST['invd'];
$id =$_POST['user'];

$pred =$_POST['pre']; 
$np =$_POST['nap']; 
$sd10 =$_POST['tdisease']; <== 
$pr =$_POST['pric1'];  


$pred1 =$_POST['pre1']; 
$np1 =$_POST['nap1']; 
$sd101 =$_POST['tdisease1'];
$pr1 =$_POST['pric1'];

$pred2 =$_POST['pre2']; 
$np2 =$_POST['nap2']; 
$sd102 =$_POST['tdisease2'];
$pr2 =$_POST['pric2'];

Because the first two sets have the same variable names and they'll be overwritten.

====================== Edit ===========================

The reason why it didn't work for you before was probably because:

1) The variables '$sd10', '$np' ,'$pred','$pr' didn't exist in the 1st query:

$insert_user="INSERT INTO invoices(id, icd10, nappi_code, prescription, price, invoice_date, pid) VALUES ('','$sd10', '$np' ,'$pred','$pr','$ivd','$id');";

2) There was no ; at the end of the 3rd query:

VALUES ('','$sd102', '$np2' ,'$pred2','$pr2','$ivd','$id')<no-semi-colon-here>";

As they are being inserted as 3 separate queries.

A query need not be given all on a single line, so lengthy queries that require several lines are not a problem. mysql determines where your statement ends by looking for the terminating semicolon, not by looking for the end of the input line. (In other words, mysql accepts free-format input: it collects input lines but does not execute them until it sees the semicolon.)

Refer: https://dev.mysql.com/doc/refman/5.7/en/entering-queries.html

Did you try replacing your code with the one I've posted? Does it still cause the same problem? — Ashil John, Feb 15 '17 at 08:21

score 0 · Answer 3 · answered Feb 15 '17 at 07:44

0

You should have a look at the function mysqli_multi_query. See example here : https://www.w3schools.com/php/php_mysql_insert_multiple.asp

Hope this helps

answered Feb 15 '17 at 07:44

chateaur

346
1
13

thanks, but i have already came across it, and its the one that i used to make those queries – Morena Feb 15 '17 at 07:53
This unstable/insecure querying technique should no be used by anyone. `mysqli_multi_query()` does not offer a secure interface. A prepared statement with placeholders should be used. – mickmackusa Aug 15 '20 at 13:40

How can I insert multiple rows into database with single submit

3 Answers3