0

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>";   
  } 
}
?>
Improve this question
4
  • what do you get? what error is shown? how many rows get saved? Commented Feb 15, 2017 at 7:44
  • 1
    insert into table (column1, column2) values ('x', 'z'), ('v', 'r'), ..... and so on Commented Feb 15, 2017 at 7:44
  • You should have a look at this stackoverflow.com/questions/452859/… Commented Feb 15, 2017 at 7:46
  • it only saves one row, and others are created blank not with the values that they have Commented Feb 15, 2017 at 7:52

3 Answers 3

Reset to default
1

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

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

2 Comments

But this still doesn't explain the observations.
Did you try replacing your code with the one I've posted? Does it still cause the same problem?
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');";
Improve this answer

6 Comments

i just tried the above solution you proposed, it only inserts a single row and ignores others
Then you have a problem with your PHP code. The query is fine.
yes the query is actually fine, but thanks i will look into my code again
Are you sure the variables/parameters used in the second and third query have actual values?
yes 100% sure, because when i play around with it, and put the last query first, i get its values
|
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

Improve this answer

2 Comments

thanks, but i have already came across it, and its the one that i used to make those queries
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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.