php - 将数据数组插入数据库的多行

Question

我正在尝试插入数据数组

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>

这里invE_item[]、invE_description[]、invE_unit_cost[]等是数组，我的意思是动态地可以添加为许多项目及其详细信息。

在我的order_submit.php页面

<?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());  
  }
%>

我想使用 $last_id 将每个项目插入到不同的行中，如下所示:

Answer 1

当您使用 PDO 时，您可以 prepare()陈述和时间 exec()使用它们，您可以提供所需的参数。

因此插入订单可能如下所示

$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'],
    ...
));

这可能看起来比转义+查询更复杂，但是一旦你理解了它，它就更容易维护。

现在lastInsertId()将会准确地满足您的要求，并再次使用 prepare() 和 exec() 将执行其他插入操作。通过将后者包装在循环中，您可以管理各种(未知)项目的处理。您唯一需要注意的是所有按键都安装在一起。含义 invE_item[x] 与 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],
        ...
    ));
}