Inserting array data into multiple rows of database

[mythri]

I am trying to array data to database against same id.

Here is code.

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"> </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>

Here invE_item[], invE_description[], invE_unit_cost[].... 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 , as in the attached image .

 

Please somebody help me in this

[Ch0cu3r]

You need to loop over the values for each of the invE_* fields and get the value. Then build the insert query from the values you have gathered. Example (untested) code

$items = array();

// loop over the invE_item field grabbing its value and also the values that correspond to that field
foreach($_POST['invE_item'] as $row_key => $value)
{
    $item  = $valuel
    $unit  = $_POST['invE_description'][$row_key]; // unit field
    $price = $_POST['invE_unit_cost'][$row_key];   // price field
    $qty   = $_POST['invE_qty'][$row_key]; // qty field
    $tax   = $_POST['invE_tax'][$row_key]; // tax field 
    $total = $_POST['invE_total'][$row_key]; // total field
    
    // formatted SQL string for row values, Example format: (1, 2, 'Item Name', 'each', 1.09, 1, 17.50, 1.28) 
    $items[] = sprintf("(%d, %d, '%s', '%s', %01.2f, %d, %01.2f, %01.2f)", $insert_id, intval($company),
                                                                           mysql_real_escape_string($item), 
                                                                           mysql_real_escape_string($unit),
                                                                           floatval($price), 
                                                                           intval($qty), 
                                                                           floatval($tax), 
                                                                           floatval($total));
}

// Dynamically build the insert query from the $items array
$msql = 'INSERT INTO order_line_items (order_id, company_id, item, unit, unit_cost, quantity, tax, total) VALUES '.implode(', ', $items);

Edited September 7, 2014 by Ch0cu3r

[jazzman1]

@Ch0cu3r, a little correction if you'll allow me Don't forget that php implode function join array elements with a string values, so all values in the example are actually strings with not escaped values. You need to use mysqli_real_escape_string and the others function after the values to be imploded not before. 

Edited September 7, 2014 by jazzman1

[mac_gyver]

that's not correct jazzman. the posted code is escaping/casting each data value as it is building the string being put into each array element. each array element is one complete value section of the query - (1, 'a string', 2.34) (with the surrounding ()). the implode is just combining all the array elements into the VALUES section of a multi-value insert query. any escaped data in the array elements will still be escaped in the resulting sql statement. you cannot escape any of that afterwards since that would change the quotes that are part of the sql syntax.

 

if there was a string data value - this contains a ' in it, the posted code will produce - (1, 'this contains a \' in it' ,2.34)

 

your suggestion to apply the escape function after or as part of the implode would produce -  (1, \'this contains a \' in it\', 2.34), which is not correct.

 

the posted code produces the following actual sql query statement for some test data - 

INSERT INTO order_line_items (order_id, company_id, item, unit, unit_cost, quantity, tax, total) VALUES (0, 0, '123', 'this contains a \' in it', 0.00, 1, 1.00, 2.22), (0, 0, '456', 'this contains a \' in it', 0.00, 2, 2.00, 4.44)

the only problem with the posted code, outside of any typo's, may be that the $item value is likely an id, not a string.

[jazzman1]

Yeah, got it. You are right. Each data value is escaped properly before the query to be built