How to insert data into two tables in one post?

Question

How to insert one row in the first table and many rows in the 2nd table in one post?

I've tow tables the first one is the main table and the 2nd is the sub-table, for example:

1st table -->> invoices 2nd table -->> sub_invoices

each invoice is linked to one or many sub-invoices.

so, I need to insert the data from one post into the tow tables, one row into invoices and one or many into sub_invoices.

invoices :: invoice_id, date, total, drvr_name, mobile, car_no, to, from, username

sub_invoices :: invoice_id, contract_no, inv_sub_no, dest, contract_vlu, advncd_sum, late_sum, discount_extra, rcvd_sum, notes, job_no

if(isset($_POST['addinvoice2']) and $_POST['addinvoice2'] == 'addinvoice2'){
            //invoices::
            $drvr_name = $_POST['drvr_name'];
            $mobile = $_POST['mobile'];
            $car_no = $_POST['car_no'];
            $to = $_POST['to'];
            //sub_invoices::
             foreach($_POST['contract_no'] as $index => $contractno) {

                    $contract_no = $contractno;
                    $inv_sub_no = $_POST['inv_sub_no'][$index];
                    $dest = $_POST['dest'][$index];
                    $contract_vlu = $_POST['contract_vlu'][$index];
                    $advncd_sum = $_POST['advncd_sum'][$index];
                    $late_sum = $_POST['late_sum'][$index];
                    $discount_extra = $_POST['discount_extra'][$index];
                    $rcvd_sum = $_POST['rcvd_sum'][$index];
                    $notes = $_POST['notes'][$index];
                    $job_no = $_POST['job_no'][$index];
             } 


                $date=date('d-m-y h:iA');

                $MAIN = mysql_query("INSERT INTO `invoices`(`invoice_id`, `date`, `total`, `drvr_name`, `mobile`, `car_no`, `to`, `from`, `username`)
                 VALUES
                 ('','$date','','$drvr_name','$mobile','$car_no','$to','','') 
                ") or die(mysql_error());



                $last_id_in_invoices = mysql_insert_id();

                $SUB = "INSERT INTO `sub_invoices`
                (`invoice_id`, `contract_no`, `inv_sub_no`, `dest`, `contract_vlu`, `advncd_sum`, `late_sum`,
                 `discount_extra`, `rcvd_sum`, `notes`, `job_no`) 
                VALUES ($last_id_in_invoices,$contract_no,$inv_sub_no,$dest,$contract_vlu,$advncd_sum,$late_sum,$discount_extra,$rcvd_sum,$notes,$job_no)";



                $RESULT_SUB = mysql_query($SUB);

                if (isset($MAIN, $RESULT_SUB)){
                    die ("
                        <center>Done Successfully!</center>
                        ");
                }
            }

Check the acepted answer in http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization — inhan, May 16 '12 at 00:53

score 0 · Accepted Answer · answered May 15 '12 at 18:04

0

Pretty sure you'll to use two insert statements, why wouldn't you do something like this?

INSERT INTO INVOICES....  and INSERT INTO SUB_INVOICES...(perhaps in a loop)

answered May 15 '12 at 18:04

GDP

8,109
6
45
82

I tried many times with loops but failed, only the first row in the loop inserted – Ali Hamdi May 15 '12 at 18:18
Then I'd suggest you look closer at the SQL statement that is failing - it appears to be the culprit with an error - do you know what that error is? – GDP May 15 '12 at 18:28
1

Another alternative, though it's beyond basic mySQL is to use a stored procedure that would update both. – GDP May 15 '12 at 18:29
I updated the post with latest code which I could reach, the problem now is loop only insert the last row, for example I posted 4 rows , only the 4th one would be inserted, How could I solve it? – Ali Hamdi May 16 '12 at 07:46
Look at your loop - you're looping through the values/variables, but only executing the INSERT...one time - AFTER the loop, so the only time you insert is when the loop is finished (i.e. the 4th one) - Put the SQL statement and execution into the loop. – GDP May 16 '12 at 12:37

How to insert data into two tables in one post?

1 Answers1