Inserting multiple row in database using php form

Question

I created a form which can add more input field for product_name and quantity of the product with the help of jquery, this is the demo where you can add more input field in the form.

the problem is when i submit the form only the last product will submit into my database the rest of the product will not submitted.

this is my query

<?php
  if(isset($_POST['submit'])){
     //process the form

     $date = $_POST["date"];
     $customer_name = $_POST["customer_name"];
     $product_description = $_POST["product_description"];
     $quantity = $_POST["quantity"];
     $status = $_POST["status"];

     $query  = "
     INSERT INTO orders (
     date, customer_name, product_description, quantity, status   
     ) VALUES (
     '$date', '$customer_name', '$product_description',$quantity,$status
     )";

     $order_set = mysqli_query($connection, $query);
     if($order_set){
       redirect_to("index.php");
     }

  } else {
    // failed

  }


?>

My Form

<form action="order.php" method="post">
  <div class="newOrder">
    <p><span>Date</span><input type="date" value="2014-12-01" name="date" /></p>
    <p><span>Name</span>
    <select name="customer_name">




    <?php
      while($customer = mysqli_fetch_assoc($customers_set)){  ?>

        <option><?php echo $customer['customer_name']; ?></option>

      <?php } ?>
      <?php mysqli_free_result($customers_set); ?>

    </select>

    </p>  


    <div id="input_fields">

    <p><span>Product Description</span>
    <select name="product_description">
     <?php
      while($product = mysqli_fetch_assoc($product_set)){  ?>

        <option><?php echo $product['product_description']; ?></option>

      <?php } ?>
      <?php mysqli_free_result($product_set); ?>
    </select>
    <input value="0" type="text" name="quantity" />
    </p>

    </div>
    <a href="#" class="more">Add More Product</a>


    <p class="radio">
      <input type="radio" name="status" value="0" checked />For delivery&nbsp;&nbsp;
      <input type="radio" name="status" value="1" />For payment confirmation&nbsp;&nbsp;
      <input type="radio" name="status" value="2" />Reserved items&nbsp;&nbsp;
    </p>


    <input type="submit" name="submit" value="Create Order" />   
  </div>
</form>

any body have any idea how to submit all product and quantity input in input field will be save in database.

I think you just need to build the list of values in a loop, and then build and execute the whole query — Strawberry, Dec 04 '14 at 01:10
thank's for your suggestion, but I have no idea where to start because my php learning is so limited.. but thank's anyway — jhunlio, Dec 04 '14 at 01:23

score 0 · Answer 1 · answered Dec 04 '14 at 01:20

Wrap your values inside your database connection. Consider this from one of my old course. Notice is a different code however working perfectly.

 $first_name = $_POST['firstname'];
 $last_name = $_POST['lastname'];
 $when_it_happened = $_POST['whenithappened'];
 $how_long = $_POST['howlong'];
 $how_many = $_POST['howmany'];
 $alien_description = $_POST['aliendescription'];
 $what_they_did = $_POST['whattheydid'];
 $fang_spotted = $_POST['fangspotted'];
 $email = $_POST['email'];
 $other = $_POST['other'];

 $dbc = mysqli_connect('data.aliensabductedme.com', 'owen', 'aliensrool', 'aliendatabase')
  or die('Error connecting to MySQL server.');

 $query = "INSERT INTO aliens_abduction (first_name, last_name, when_it_happened, how_long, " .
"how_many, alien_description, what_they_did, fang_spotted, other, email) " .
"VALUES ('$first_name', '$last_name', '$when_it_happened', '$how_long', '$how_many', " .
"'$alien_description', '$what_they_did', '$fang_spotted', '$other', '$email')";

$result = mysqli_query($dbc, $query)
or die('Error querying database.');

mysqli_close($dbc);

jeastogg · Answer 2 · 2014-12-04T01:41:42.393

0

The input fields have the same name? So I guess thats why only the last one get inserted.

You have to loop the INSERT query foreach product you add, this includes quantity.

You should allso sanitize the input value before you inserting it to a query.

edited Dec 04 '14 at 01:41

answered Dec 04 '14 at 01:31

jeastogg

80
6

dont loop the insert; just the values – Strawberry Dec 04 '14 at 08:06

score 0 · Answer 3 · edited May 23 '17 at 12:00

When you inserting multiple queries, you shouldn't do that from the php loop. Is not efficient because you are executing multiple queries instead one or two. You can loop trough the results sent from the form, clean it and prepare it for database insertion, and after that build a query based on that results. Look at here for the inserting multiple rows at once into a database :

(Insert multiple rows with one query MySQL)

score -1 · Answer 4 · answered Dec 04 '14 at 01:36

-1

you need to use foreach

foreach ($_POST['quantity'] as $quantity) {
//insert code
}

answered Dec 04 '14 at 01:36

Tom

432
2
9

Inserting multiple row in database using php form

4 Answers4