inserting multiple data with a same id in database

[mythri]

Hi,

 

I have a form like this

<tr>

   
    <td><a href="edit_sales_invoice_details.php?id=<?php echo $row['dispatch_id']?>"><?php echo $row['oid'] ?></a></td>
	
	  
	<td><?php echo $row['dispatch_id'] ?></td>
    <td><?php echo $row['cname'] ?></td>
    <td><?php echo $row['iname'] ?></td>
    <td><?php echo $row['quantity'] ?></td>
    <td><?php echo $row['dispatch_quantity'] ?></td>
    <td><select name="tax[<?php echo $row['dispatch_id']?>]" class="span9">
    <option value="<?php echo $row['rate'] ?>"><?php echo $row['tname'].'-'.$row['rate'];?> %</option>
	<option value="">Tax</option>
    <?php
	$s1 = mysql_query("select * from taxes");
	while($s2 = mysql_fetch_array($s1))
	{ ?>
    <option value="<?php echo $s2['rate'] ?>"><?php echo $s2['name'].'-'.$s2['rate'] ?>%</option>
    <?php } ?>
    </select>
	</td>
    <td><input type="text" name="freight[<?php echo $row['dispatch_id']?>]" class="span6" /></td>
    <td><input type="text" name="discount[<?php echo $row['dispatch_id']?>]" class="span6" /></td>
     <input type="hidden" name="order_id[<?php echo $row['dispatch_id']?>]" value="<?php echo $row['oid']; ?>" />
	
    
   <td><input type="checkbox" name="selector[]" value="<?php echo $row['dispatch_id']; ?>" /></td>

</tr>

<?php 
}


?>
<tr><td colspan="11"><input type="submit" name="submit" value="SAVE INVOICE" class="btn btn-info" /></td></tr>

Here dispatch_id will be different but the order_id will be same. I want to generate a single invoice id for this and store it in this table 

 

and by taking that invoice_id, i want to insert rest of the multiple line items to this table

 

I tried doing like this

 

if(isset($_POST['submit']))
{
foreach ($_POST['selector'] as $k) {
$tax = $_POST['tax'][$k];
$freight = $_POST['freight'][$k];
$discount = $_POST['discount'][$k];
$order_id = $_POST['order_id'][$k];

$s1 = "SELECT stock_dispatch.dispatch_id, stock_dispatch.sales_order_id, stock_dispatch.customer_id, stock_dispatch.line_item_id, stock_dispatch.item, stock_dispatch.dispatch_quantity, stock_dispatch.delivery_method, stock_dispatch.date_dispatched, stock_dispatch.comments, before_order_line_items.id as bid, before_order_line_items.order_id, before_order_line_items.item, before_order_line_items.uom, before_order_line_items.selling_price FROM stock_dispatch INNER JOIN before_order_line_items ON stock_dispatch.line_item_id=before_order_line_items.id WHERE stock_dispatch.dispatch_id=".$k."";

$s2 = mysql_query($s1) or die (mysql_error());

while($row = mysql_fetch_array($s2))
{
$customer_id = $row['customer_id'];
$price = $row['selling_price'];
$quantity = $row['dispatch_quantity'];
$total_price = $price * $quantity;

$ac_discount = ($total_price - (($total_price)*($discount/100)));

$tax_amount = ($ac_discount * ($tax/100));

$total_amount = $ac_discount + $freight;

$query1 = "INSERT into sales_invoice (invoice_id, order_id, customer_id, date_invoiced) VALUES ('', ".$order_id.", ".$customer_id.", NOW())";
//echo $query1;
$query2 = mysql_query($query1) or die (mysql_error());

$id = mysql_insert_id($query2);


$query3 = "INSERT INTO sales_invoice_line_items(id, invoice_id, dispatch_id, tax_rate, discount, freight, sub_total, tax_amount) VALUES ('', ".$id.", ".$k.", ".$tax.", ".$discount.", ".$freight.", ".$total_amount.", ".$tax_amount.")";
$query4 = mysql_query($query3);

 

But it generates invoice_ids depending on the line items, not single. I mean if the selected line items are 2 it generates 2 invoice_ids. 

Can somebody please help me to resolve this

Edited November 25, 2014 by mythri

[Barand]

Insert into the invoice table before the while loop (exclude invoice id field)

$query1 = "INSERT into sales_invoice (order_id, customer_id, date_invoiced) VALUES ( ".$order_id.", ".$customer_id.", NOW())";

Use $invoice_id = mysql_insert_id() to get the new id and use that when you insert the invoice lines

[mythri]

Thanks barad, I was supposed to place this statement not only before while loop but also before my for each loop.