save data into 2 tables in sql

Question

I have 2 tables namely sales and sales_item. The values that will be saved in these tables are coming from a PHP form. Here's my table structure:

Sales table:

srfno
clientname
address
contactnumber

sales_item table:

itemid
srfno
description
quantity
serialno
description

My problem is that I can't insert the values to sales_item. I can't get the srfno from sales so that they are referenced. I can only save data to sales table. Please, help thanks!

My code for inserting to sales_item:

$retitem= "Select `srfno` from sales_item"; 
$psql= mysql_query($retitem,$con);  
$reti = mysql_num_rows($psql);
$reti = $reti + 1;
while($return = mysql_fetch_assoc($psql))
{
if(isset($return['srfno']))
{
$retid=$return['srfno'];
$retid=mysql_insert_id();
$addretex="Insert into `sales_item` (`sitemid`,`srfno`, `retqty`, `retdesc`,`retserial`, `exqty`, `exdesc`,`exserial`,)
VALUES (' ','$retid', '$qty', '$desc', '$serialno', ' ', ' ', ' ')";
        $ret=mysql_query($addretex);                    
                }


            }

Inserting to sales table:

 $sql="INSERT INTO `sales`(`srfno`, `datecreated`, `clientid`, `referenceno`, `returnreason`, `explanation`, `status`, `dateupdated`, `dateclosed`, `resolution`)
     VALUES('$srfno','$datecreated','$clientid','$refno','$returnreason', '$explanation', 'OPEN ', ' ', ' ', 'NULL')";
    $query=mysql_query($sql);

After insert record in to `sales` table try `srfno = mysql_insert_id();` — Sadikhasan, Jul 25 '14 at 03:49
What do you mean, you wrote a form that is supposed to do an insert into sales_item and it isn't working? If so can we see the code? — Brian DeMilia, Jul 25 '14 at 03:50
By the way, having the item's description in the sales_item table is not good design; the data is redundant. You should have an ITEMS table with (ITEMID, DESCRIPTION), your SALES_ITEM table should have just (SRFNO, ITEMID) — Brian DeMilia, Jul 25 '14 at 03:51
possible duplicate of [SQL Server: Is it possible to insert into two tables at the same time?](http://stackoverflow.com/questions/175066/sql-server-is-it-possible-to-insert-into-two-tables-at-the-same-time) — jned29, Jul 25 '14 at 03:55
@BrianDeMilia yes. It is supposed to save data to sales and sales_item. But right now it just save data to sales table. the quantity, serialno and description don't save in sales_item table — fly_ninja, Jul 25 '14 at 03:56
We would need to see the code. Copy the portion related to the insert so that we can see if the problem is there, and if not there, the form. — Brian DeMilia, Jul 25 '14 at 04:04

score 0 · Answer 1 · answered Jul 25 '14 at 03:50

0

If you need to get just inserted srfno into the first table, use

mysql(i)_insert_id

answered Jul 25 '14 at 03:50

pavel

26,538
10
45
61

score 0 · Answer 2 · answered Jul 25 '14 at 04:02

0

You haven't mentioned what extension you are using in php.

If you're using PDO, use PDO::lastInsertId.

If you're using Mysqli, use mysqli::$insert_id.

Then save inserted_id in sales_item table.

answered Jul 25 '14 at 04:02

Manwal

23,450
12
63
93

save data into 2 tables in sql

2 Answers2