Hi,

Imagine the following two tables in a database: -


Table1
----------
Person_ID_PRI (Auto Increment, Unique, Primary key)
Person_Last_Name

Table2
---------
Address_ID_PRI (Auto Increment, Unique, Primary key)
Person_ID_FRN (Foreign Key from Table1)
Person_Post_Code

So my application is set up with a form to insert records of a persons name and their postal codes simultaneously into these two tables. In table 2 there is a foreign key that must relate to the primary key in Table1.

The problem I am having is thinking through is how to insert the value of Person_ID_FRN into Table2. That value is auto generated by MySQL when the Person_Last_Name is inserted into table1.

I am sure I have done this in the past but seem to be having a mental blockage on it.

Cheers

Steve

[color=#0000BB]I suppose i could use: -

mysql_insert_id()

which will take the last inserted records id

so,

[/color][color=#0000BB]INSERT INTO Table1 (Person_Last_Name) values ('LastName')");
[/color][color=#DD0000]INSERT INTO Table2 (Person_Post_Code, Person_ID_FRN) values ('PersonPostCode',[/color][color=#0000BB]mysql_insert_id()[/color][color=#DD0000])");[/color]
[color=#007700]
I'll Give that go. Not sure if this will work with multiple inserts at onece
[/color]

Did this work for you?

I'm new to PHP and MySQL and having the same "problem" with a database I created.

It did work for me

mysql_insert_id() give the last inserted id

Hi there
im basically facing the same problem as what this thread is having. But instead of have 2 tables, i have 3 tables. Can i still be able to use the mysql_insert_id() function insert Table 2 id and Table 3 id into Table 1??

Thanks xnuiem. Having known this work, another question will be, I need to make some kind of relationship between these 3 tables. for example, when an insert is made on table 2 and table 3, there's already a value insert previous and i would like to extract the id (previously value inserted) to table 1. Can it be done or i have to set some parameter/relationship between these 2 tables?

Ok.. what i got here are 3 tables where table B and C are using a foreign keys to relate themselves to table A. This is the type of results i am looking for.

I wrote a php form to request user to enter in their particulars and table B and C are the common fields so whenever user enter the similar address and postcode, the related id from table B will reflect on table A. However, what i had here was whenever user enter the address and postcode, a new id is generated even though the same address and postcode had entered a few times. Below is my code.

[PHP]$B_insert = "INSERT INTO B (B_add, B_pcode)
VALUES ('$val_add', '$val_pcode')";
$result = mysql_query($B_insert) or die (mysql_error());
$B_insert = mysql_insert_id() or die (mysql_error());

$C_insert = "INSERT INTO C (C_state)
VALUES ('$val_state')";
$result = mysql_query($C_insert) or die (mysql_error());
$C_insert = mysql_insert_id() or die (mysql_error());


$A_insert = "INSERT INTO A (A_fname, A_orange, A_melon)
VALUES ('$val_fname', '$B_insert', '$C_insert')";
$result = mysql_query($apple_insert) or die (mysql_error()); [/PHP]

Oh, i forgot to state whether if i should use the SELECT method like
[PHP]
SELECT *
FROM A, B
WHERE A_id = B_id
[/PHP]

Thanks Xnuiem, it works well..
The results i m looking forward to see. Thank you so much.

Actually I have considered using a drop down list to populate the states before. However when i choose a state from the dropdown list (Table C of the database), it doesnt produce any relationship between table C and table A - as in the tableC.id does not reflect on tableA.Cid. I can now give it a try using the dropdown list using the code here.
Appreciate your help mate. :):)

By the way, if there are more than 2 tables i.e. Table B, Table C, Table D, Table E, etc. am i suppose to add more of the similiar code like this: