add mutiple rows in database php?

Question

I want to insert many rows in database through loops.( $product,$size, $quantity, $uom, $target_price) columns of one row.how to insert these type of mutilpe rows in database.and data.how to use for loop or other loop to enter detail record agasint serial no 1.

    if(isset($_POST['Submit_form']))
     {
     /*For Master Detail*/
     $serial_no=$_POST['serial_no'];
     $dated=$_POST['dated'];
     $ship_schedule=$_POST['ship_schedule'];
     $commission=$_POST['commission'];
     $customer_name=$_POST['customer_name'];
     $agnet_name=$_POST['agnet_name'];
     $remark=$_POST['remark'];
      /*For Detail*/
     $product=$_POST['product'];
     $size=$_POST['size'];
     $quantity=$_POST['quantity'];
     $uom=$_POST['uom'];
     $target_price=$_POST['target_price'];

    $sqli = "INSERT INTO inquiry_mst (dated,ship_schedule,commision,customer_id,agent_id,remarks)
VALUES ('$serial_no','$dated','$ship_schedule','$commission','$customer_name','$agnet_name','$remark')";
   $sqli=$mysqli->query($sqli) or die('Failed to connect'.$mysqli->error._LINE_);
   if($sqli)
   {
       foreach($choices as $choice => $values)
       {
         $sqli="INSERT INTO inquiry_dtl (serial_no, product_name,size,quantity,uom,target_price) 
         VALUES ($product,$size,$quantity,$uom,$target_price)";

       }
   }
}

First you need to explain in your code Why you need a loop to do multiple inserts. You are looping through a variable that isn't on the code. So you need to add further details to your question. How `$choices` variable is being created? Where it came from? And why a For if you didn't even use it (`$choices`) inside. Explaining your requirement and what your system must do would help us to help you! — Jorge Campos, Mar 04 '16 at 11:31
i have two form.Master form And detail form.in master table consist of serial no.that is primary key.and serial no is foriegn key in second form. 2nd form that are given on same page.in 2nd form multiple rows are avialable.these rows are save in database against serial no.one submit button of whole form.data is storee in both table on one submit click. — Rajo, Mar 04 '16 at 11:46
Add the code to your question It is important for whoever will try to answer you. — Jorge Campos, Mar 04 '16 at 11:47

score 0 · Accepted Answer · answered Mar 04 '16 at 11:29

You can use mysqli_multi_query for your request.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com');";
$sql .= "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')";

if ($conn->multi_query($sql) === TRUE) {
    echo "New records created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Source: http://www.w3schools.com/php/php_mysql_insert_multiple.asp

or using the SQL Syntax(If you want one Query):

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

score 0 · Answer 2 · answered Mar 04 '16 at 11:53

   I want this type of concept.that choices are save in database against the question number.in this code row consist of one column.but i need to insert multiple rows that are consist of multiple columns.
  if(isset($_POST['submit']))
   {
     //for question
    $question_number=$_POST['question_number'];
      $question_text=$_POST['question_text'];
     $correct_number=$_POST['correct_number'];
    //for choices
    $choices=array();
    $choices[1]=$_POST['choice1'];
    $choices[2]=$_POST['choice2'];
    $choices[3]=$_POST['choice3'];
    $choices[4]=$_POST['choice4'];

     //Insert query for question
     $sqli="INSERT INTO questions (question_number,question_text) VALUES ('$question_number','$question_text')";    
       $query=$mysqli->query($sqli) or die("falied to Query".$mysqli->error._LINE_);
       if($query)
       {
           foreach($choices as $choice => $values)
           {
               if($values !== "")
               {
                   if($correct_number == $choice )
                   {
                       $is_correct =1;
                   }
                   else
                   {
                       $is_correct=0;
                   }
           $sqli= "INSERT INTO choices (question_number,is_correct,text) VALUES ('$question_number','$is_correct','$values')";
               $query=$mysqli->query($sqli) or die('Failed to inserted'.$mysqli->error._LINE_);  
                 if($query)
                 {
                     continue;
                 }
                 else
                 {
                     die('Failed insertes in choices table');
                 }
              }

score 0 · Answer 3 · answered Mar 04 '16 at 12:06

        <?php
 include_once 'connection.php';
session_start();


$mysql="SELECT max(inquiry_dtl_id) FROM inquiry_dtl";
$query=$mysqli->query($mysql) or die('Failed to connect'.$mysqli->error._LINE_);
if($query)
{
  while($max=$query->fetch_array())
  { 
        $increment=$max[0]+1;
  }
}

if(isset($_POST['add']) == "ADD")
{  
if (isset($_POST['count'])) {$count = $_POST['count'] + 1;}
// set value for first load
    else {$count = 1;}
}
if(isset($_POST['btnremove']) == "REMOVE") {
// decrement the row counter
   $count = $_POST['count'] - 1;
// set minimum row number
    if ($count < 0) $count = 0;
}

   if(isset($_POST['Submit_form']))
{
  /*For Master Detail*/
     $serial_no=$_POST['serial_no'];
     $dated=$_POST['dated'];
     $ship_schedule=$_POST['ship_schedule'];
     $commission=$_POST['commission'];
     $customer_name=$_POST['customer_name'];
     $agnet_name=$_POST['agnet_name'];
     $remark=$_POST['remark'];


     /*for product table*/

     $product=$_POST['product'];
     $size=$_POST['size'];
     $quantity=$_POST['quantity'];
     $uom=$_POST['uom'];
     $target_price=$_POST['target_price'];


$sqli = "INSERT INTO inquiry_mst (dated,ship_schedule,commision,customer_id,agent_id,remarks)
VALUES ('$serial_no','$dated','$ship_schedule','$commission','$customer_name','$agnet_name','$remark')";
   $sqli=$mysqli->query($sqli) or die('Failed to connect'.$mysqli->error._LINE_);
   if($sqli)
   {
       foreach($choices as $choice => $values)
       {
         $sqli="INSERT INTO inquiry_dtl (serial_no, product_name,size,quantity,uom,target_price) 
         VALUES ($product,$size,$quantity,$uom,$target_price)";

       }
   }
}


?>
<html>
<head>
<meta charset="utf-8">
<title>Customer Inquiry</title>
 <link rel="stylesheet" type="text/css" href="css/bootstrap.min.css" />
 <link rel="stylesheet"  href="css/font-awesome.min.css"/>

  <script type="text/javascript">
   $("#add_row").click(function () {
    var form = jQuery('form');
    var row =  jQuery('input',form).length;
    var row_id = 'addr'+row;

      var tr=$("#increment,#product,#Product_size,#quantity
    ,#uom,#target_price,#mymodel").
    clone().attr('id',row_id);            
    tr.find('input').attr('id','parent'+row);
    jQuery('form').append(tr);
     });
   </script>

   </head>
  <body>
   <div style="background-image:url(images/bg.png);">
  <form class="form-horizontal" method="post">
  <fieldset>
   <!-- Form Name -->
  <legend style="text-align:center;">Customer Inquiry</legend>
  <!-- Text input-->
  <div class="form-group">
   <label class="col-md-1 control-label" for="">Serial #</label>  
   <div class="col-md-1">
    <?php
  $mysql="SELECT max(serial_id) FROM inquiry_mst";
  $query=$mysqli->query($mysql) or die('Failed to connect'.$mysqli->error._LINE_);
if($query)
{
  while($max_id=$query->fetch_array())
  { 
?>
  <input id="" name="serial_no" id="serial_no"
  type="text" placeholder="" 
   class="form-control input-md"  value="<?php                
  echo $max_id[0]+1; ?>" 
    readonly>  
   <?php 
    }
   } 
   ?> 
   </div>

  <label class="col-md-1 control-label" for="dated">Dated</label>  
<div class="col-md-2">
 <input id="dated" name="dated" 
   type="text" placeholder="" class="form-control input-md"> 
 </div>

 <label class="col-md-2 control-label"
   for="ship_schedule">Ship    Schedule</label>  
  <div class="col-md-2">
 <input id="ship_schedule"
   name="ship_schedule" type="text" 
 placeholder="" class="form-control input-   md"> 
  </div>

   <label class="col-md-1 control-label" for="commission">Commission</label>  
  <div class="col-md-2">
  <input id="commission" 
 name="commission" type="text" placeholder=""     
   class="form-control input-md"> 
    </div>

  </div>
  <!-- Text input-->
  <div class="form-group">
  <label class="col-md-1 control-label" for="customer">Customer :</label>  
 <div class="col-md-2">

   <select class="form-control input-md" name="customer_name">
   <option>Customer Name</option>
   <?php
     $query="SELECT * FROM company_info";
     $query=$mysqli->query($query) or 
   die('Failed to connect'.$mysqli-   >error._LINE_);
    if($query)
    {
        while ($row=$query->fetch_array()) 
     {
    ?>

    <option value="<?php 
  echo $row['company_id'];  ?>">
   <?php echo   $row['contact_person']; ?></option>
   <?php
    }
    }
 ?>
    </select>

   </div>

  <label class="col-md-4 control-label" for="agent">Agent</label>  
  <div class="col-md-2">
  <select class="form-control input-md" name="agnet_name">
   <option>Agent Name</option>

    <?php
     $query="SELECT * FROM agent";
   $query=$mysqli->query($query) or 
     die('Failed to connect'.$mysqli-  >error._LINE_);
    if($query)
     {
      while ($row=$query->fetch_array()) 
     {
       ?>  
   <option value="<?php echo $row['agent_id'];  ?>"> 
   <?php echo    $row['agent_name']; ?></option>
  <?php
    }
     }
    ?>
   </select>
  </div>
</div>

<script src="js/jquery-2.1.4.min.js"></script>
<script src="js/bootstrap.min.js"></script>
</body>
 </html>

add mutiple rows in database php?

3 Answers3