Inserting Data into 2 tables ( associating PK)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sree78
Forum Commoner
Posts: 39
Joined: Tue May 20, 2003 11:38 am

Inserting Data into 2 tables ( associating PK)

Post by sree78 »

Hi everybody!!

I am newbie in PHP. We are running postgresql in our office. I am trying to figure out how I can insert information submited from a form into 2 different tables.

Its a employee information database, where the first table will hold emp Fname, lname and the 2nd table will hold the rest of the info. The DB is designed such a way because some employees work in 2 or more departments.

I want the ID which was created automatically in the emp table to be inserted in table 2 as a foreign key.

This is a one transaction insert.

I have been surfing a lot of sites to see if they have good eg.. but nothing came close..

Any help will be very much appreciated. :cry:
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

While its not the cleanest way, one method
1. Insert into the first table
2. Query the first row back to get the id
3. Insert into the second table

Some databases have row IDs which are returned by the insert, this sometimes makes step 2 a little easier, but makes the resulting code
less portable.

If you database supports transacation, you probably should wrap the three queries into a transaction.
sree78
Forum Commoner
Posts: 39
Joined: Tue May 20, 2003 11:38 am

Post by sree78 »

How do I query the last inserted Id in postgresql. I did the same exact thing while I was coding in coldfusion and the query looked something like this.

**********************************************
<cftransaction>
<!--To get the New ID for the Insert-->
<cfquery name="GetEmpID" DATASOURCE="LE2">
SELECT MAX(EmpID)+1 as NEWID
FROM Employee
</cfquery>

<!--To get the New ID for the Insert-->
<cfquery name="GetEID" DATASOURCE="LE2">
SELECT MAX(Emp_Info_ID)+1 as EID
FROM Emp_Info
</cfquery>

<!-- Adds Employee Information -->
<cfquery name="AddEmployee" DATASOURCE="LE2">
INSERT INTO Employee (EmpID, Fname, Lname)
VALUES (#GetEmpID.NEWID#, '#Form.Fname#', '#Form.Lname#')
</cfquery>

<!-- Adds Information to the other subsequent Table-->
<cfquery name="AddEmp_Info" DATASOURCE="LE2">
INSERT INTO Emp_Info (Emp_Info_ID, Emp_ID, Dept_ID, Title, Phone, Email, Homepage, Room, Rank)
VALUES(#GetEID.EID#, #GetEmpID.NEWID#, '#Form.Name#', '#Form.Title#', '#Form.Phone#',
'#Form.Email#', '#Form.Homepage#', '#Form.Room#', '#Form.Rank#')
</cfquery>
</cftransaction>
***********************************************

I believe postgresql support transaction. I am just confused how to call the last inserted ID and how to use the ID to insert into the next insert statement. Must I define a variable like for and example $result ... and query the last ID and use $result as a value in the insert statement?

Really appreciate any feedback.
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

sree78 wrote:How do I query the last inserted Id in postgresql. I did the same exact thing while I was coding in coldfusion and the query looked something like this.
Yay! another PostGreSQL user! :)

Here's the basic way of doing it

Code: Select all

pg_query($db,"BEGIN TRANSACTION;");
$query = <<<END_QUERY
INSERT INTO "Employee" ("Fname","Lname")
  VALUES ('{$_POST["Fname"]}','{$_POST["Lname"]}');
END_QUERY;
pg_query($db,$query);
$query =<<<END_QUERY
SELECT "EmpID" FROM "Employee"
   WHERE "Fname"='{$_POST["Fname"]}' AND
              "Lname"='{$_POST["Lname"]}';
END_QUERY;
$result = pg_query($db,$query);
$numrows = $pg_num_rows($result);
if ($numrows!=1)
{
 // handle error case
 pg_query("ROLLBACK;");
}
else
{
    list($empID) = pg_fetch_array($result,0);
    $query =<<<END_QUERY
INSERT INTO "Emp_Info" ("Emp_ID", "Dept_ID", "Title", "Phone","Email", 
                                      "Homepage","Room", "Rank")
     VALUES('$empID','{$_POST["Name"]}', '{$_POST["Title"]}', '{$_POST["Phone"]}',				'{$_POST["Email"]}', '{$_POST["Homepage"]}', '{$_POST["Rank"]}'));
END_QUERY;
   pg_query($query);
   pg_query("COMMIT WORK;");
}
There may be some typos om the above, so please take it with a grain of salt. I double quoted all columns and table names because your example was mixed-case and PostGreSQL "folds" to lowercase by default. I single quoted all values as I didn't know if any were numbers instead of text and it doesn't hurt numbers to be quoted. I did assume that Emp_ID and the primary key of the second table were defined as type "SERIAL" aka MySQL's auto-increment.
I haven't shown complete error checking, because I'm used to my own private library that wraps database access and handles most of the error cases for me.
sree78
Forum Commoner
Posts: 39
Joined: Tue May 20, 2003 11:38 am

Post by sree78 »

Hey Eric..

Thanks a lot it really helped me to figure out and I managed to do it.. there a few things I had to change from the codes you gave but it was excellent.. i understood it.. very easily. The final piece looks something like this:-

<?
if ($post == "yes") {
//THEN WE ARE ADDING A NEW RECORD SUBMITTED FROM THE FORM
//REPLACE THE FIELD CONTENTS SO THEY DON'T MESS UP YOUR QUERY

pg_query("BEGIN TRANSACTION;");
$query =
"INSERT INTO employee (fname,lname)
VALUES ('{$_POST["fname"]}','{$_POST["lname"]}')";

pg_query($query);
$query =
"SELECT empid FROM employee
WHERE fname ='{$_POST["fname"]}'
AND lname ='{$_POST["lname"]}'";


$result = pg_query($query);
$numrows = pg_numrows($result);
if ($numrows!=1)
{
// handle error case
pg_query("ROLLBACK;");
}

else

{
list($empID) = pg_fetch_array($result,0);
$query =
"INSERT INTO emp_info (empid, deptid, title, phone, email, link, room, rank)
VALUES('$empID','{$_POST["department"]}', '{$_POST["title"]}', '{$_POST["phone"]}', '{$_POST["email"]}',
'{$_POST["link"]}', '{$_POST["room"]}', '{$_POST["rank"]}')";

pg_query($query);
pg_query("COMMIT TRANSACTION;");
}
if (!pg_last_error())
{
echo "The employee <b>$fname, $lname</b> has been added to the database successfully";
} else {
echo "Error inserting record (9994SQL)".pg_last_error();
}
//***** END INSERT SQL *****


} //END IF POST = YES FOR ADDING NEW RECORDS

if (!$post) {
//THEN WE ARE ENTERING A NEW RECORD
//***** BEGIN ADD NEW FORM*****
/*-- SECTION: 9994FORM --*/
?>

<?php
} //END if post=""
//***** END ADD NEW ENTRY FORM*****
?>

Thanks Man :)
Post Reply