Inserting array of data into multiple rows of database

Question

I am trying to insert an array of data

form.php

<form name="users" method="post" action="order_submit.php" enctype="multipart/form-data" onSubmit="return validate();" id="inv_form">
    <div class="formSep">
        <select name="company" onChange="showSubcat(this);">
            <option value="">Company</option>
            <?php
              $s1 = mysql_query("select * from leads where lead_customer='Lead' ")
                      or die (mysql_error());
              while($s2 = mysql_fetch_array($s1))
              {
            ?>
            <option value="<?php echo $s2['id']; ?>"><?php echo $s2['company']; ?></option>
            <?php
              }
            ?>
        </select>
    </div>

    <div class="formSep">

       <table class="table invE_table">
           <thead>
               <tr>
                   <th></th>
                   <th>Item</th>
                   <th>Unit</th>
                   <th>Unit Cost ($)</th>
                   <th>Qty</th>
                   <th>Tax (%)</th>
                   <th>Total ($)</th>
               </tr>
           </thead>
           <tbody>
               <tr class="inv_row">
                   <td class="inv_clone_row"><i class="icon-plus inv_clone_btn"></i></td>
                   <td><input type="text" class="span12" name="invE_item[]" /></td>
                   <td><input type="text" class="span12" name="invE_description[]" /></td>
                   <td><input type="text" class="span12 jQinv_item_unit" name="invE_unit_cost[]" /></td>
                   <td><input type="text" class="span12 jQinv_item_qty" name="invE_qty[]" /></td>
                   <td><input type="text" class="span12 jQinv_item_tax" name="invE_tax[]" /></td>
                   <td><input type="text" readonly class="span12 jQinv_item_total" name="invE_total[]" /></td>
               </tr>
               <tr class="last_row">
                   <td colspan="5">&nbsp;</td>
                   <td colspan="2">
                       <p class="clearfix">Subtotal: <span class="invE_subtotal">$<span>0.00</span></span></p>
                       <p>Tax: <span class="invE_tax">$<span>0.00</span></span></p>
                       <p>Discount: <span class="invE_discount">$<span>0.00</span></span></p>
                       <p><strong>Balance: <span class="invE_balance">$<span>0.00</span></span></strong></p>
                   </td>
               </tr>
           </tbody> 
       </table>
   </div>
</form>

Here invE_item[], invE_description[], invE_unit_cost[], etc. are the array, I mean dynamically one can add as many as items and its details.

In my order_submit.php page

<?php

  error_reporting(0);
  include("connect.php");
  include("admin_auth.php"); 

  if(isset($_POST['save']))
  {

      $company = $_POST['company'];
      $contact_person = $_POST['contact_person'];
      $billing = $_POST['billing_address'];
      $shipping = $_POST['shipping_address'];
      $reference = $_POST['reference'];
      $t_c = $_POST['t_c'];
      $payment = $_POST['payment'];
      $ship_in = $_POST['ship_inst'];
      $validity = $_POST['validity'];
      $currency = $_POST['currency'];
      $order_for = $_POST['order_for'];
      $assigned_to = $_POST['assigned_to'];
      $item = $_POST['invE_item'];
      $unit = $_POST['invE_description'];
      $price = $_POST['invE_unit_cost'];    
      $qty= $_POST['invE_qty']; 
      $tax = $_POST['invE_tax'];    
      $total = $_POST['invE_total'];    

      $sql = mysql_query("insert into orders (order_id, company_id, contact_person, billing_address, shipping_address, reference, t_c, payment, shipping_inst, validity, order_for, currency, assigned_to, last_modified, order_quote) values ('', ".$company.", '".$contact_person."', '".$billing."', '".$shipping."', '".$reference."', '".$t_c."', '".$payment."', '".$ship_in."', ".$validity.", '".$order_for."', '".$currency."', '".$assigned_to."', NOW(), 'Order')");

      $last_id = mysql_insert_id();

      $msql = "insert into order_line_items (id, order_id, company_id, item, unit, unit_cost, quantity, tax, total) values ('', ".$last_id.", ".$company.", '".$item."', '".$unit."', ".$price.", ".$qty.", ".$tax.", ".$total.")";


      $l1 = mysql_query($msql) or die (mysql_error());  
  }
%>

I want to insert each item in different row with $last_id like this:

enter image description here

Your code is vulnerable to SQL injection and mysql_* is deprecated. You should use PDO or mysqli_* with prepared statements. — Lkopo
– Lkopo, Commented Sep 7, 2014 at 9:50
Actually I am correcting my code using PDO and mysql_real_escape_string — user2823107
– user2823107, Commented Sep 7, 2014 at 10:02
@user2823107: I think this is what Mr. Smith is hinting at: php.net/manual/en/mysqli.prepare.php — matt_jay
– matt_jay, Commented Sep 7, 2014 at 10:04

kero · Accepted Answer · 2014-09-07 10:16:19Z

When you are using PDO, you can prepare() statements and when exec()uting them you can supply the needed parameters.

So inserting into orders could look like this

$insertOrder = $pdo->prepare('INSERT INTO orders (company_id, contact_person, ...) 
    VALUES (:company_id, :contact_person, ...)');
$insertOrder->execute(array(
    ':company_id' => $_POST['company'],
    ':contact_person' => $_POST['contact_person'],
    ...
));

This might look more complex than your escaping + query but once you understand it, it is quite more easy to maintain.

Now lastInsertId() will get you exactly that and again using prepare() and exec() will do the other inserts. By wrapping the latter in a loop you can manage the handling of various (unknown) items. The only thing you need to watch for is that all keys fit together. Meaning invE_item[x] is for the same row as invE_unit[x]

$orderId = $pdo->lastInsertId();

$insertOrderItem = $pdo->prepare('INSERT INTO order_line_items (order_id, item, unit, ...) VALUES (:order_id, :item, :unit, ...)');
foreach ($_POST['invE_item'] as $i=>$item) {
    $insertOrderItem->execute(array(
        ':order_id' => $orderId,
        ':item' => $item,
        ':unit' => $_POST['invE_unit'][$i],
        ...
    ));
}

Collectives™ on Stack Overflow

Inserting array of data into multiple rows of database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related