View unanswered posts | View active topics It is currently Sat Jun 25, 2022 7:45 pm



Reply to topic  [ 13 posts ]  Go to page 1, 2  Next
 Inserting a foreign key 
Author Message
Junior Member

Joined: Sat Dec 06, 2008 5:41 am
Posts: 3
Post Inserting a foreign key
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


Sat Dec 06, 2008 6:16 am
Report this post
Profile
Junior Member

Joined: Sat Dec 06, 2008 5:41 am
Posts: 3
Post mysql_insert_id()
[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]


Sat Dec 06, 2008 7:14 am
Report this post
Profile
Junior Member

Joined: Sat Apr 04, 2009 1:38 am
Posts: 2
Post 
Did this work for you?

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


Sat Apr 04, 2009 5:12 am
Report this post
Profile
Junior Member

Joined: Sat Dec 06, 2008 5:41 am
Posts: 3
Post 
It did work for me


Mon Apr 06, 2009 7:34 am
Report this post
Profile
Junior Member

Joined: Tue Feb 10, 2009 5:26 am
Posts: 1
Post mysql_insert_id()
mysql_insert_id() give the last inserted id


Mon May 11, 2009 2:17 pm
Report this post
Profile
Junior Member

Joined: Wed Nov 11, 2009 8:31 pm
Posts: 26
Post 
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??


Wed Nov 11, 2009 8:35 pm
Report this post
Profile
Junior Member

Joined: Wed Nov 11, 2009 8:31 pm
Posts: 26
Post 
Xnuiem wrote:
Sure, just store it in variables instead. So..

mysql_query("INSERT #1");
$insert_id = mysql_insert_id();

mysql_query("INSERT #2");
$insert_id_2 = mysql_insert_id();

mysql_query("INSERT #3 with $insert_id and $insert_id_2");
$insert_id_3 = mysql_insert_id();


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?


Thu Nov 12, 2009 9:10 pm
Report this post
Profile
Junior Member

Joined: Wed Nov 11, 2009 8:31 pm
Posts: 26
Post 
Code:

Table A   
A_id    A_fname   B_id  C_id
1          Joe       1   2
2          Kelly     1   2
3          Mary      2   1
4          Peter     3   3
5          Paul      2   1

Code:
Table B 
B_id B_add               B_pcode
1 georgetown               12345
2 Ellaville               22334
3 Montague                 55467

Code:
Table C
C_id C_state
1       CA
2       FA
3       DC


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]


Mon Nov 16, 2009 2:13 am
Report this post
Profile
Junior Member

Joined: Wed Nov 11, 2009 8:31 pm
Posts: 26
Post 
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]


Mon Nov 16, 2009 2:45 am
Report this post
Profile
Junior Member

Joined: Wed Nov 11, 2009 8:31 pm
Posts: 26
Post 
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:
Code:
list($B_insert) = mysql_fetch_row(mysql_query("select B_id from B where B_add = '$val_add' and B_pcode = '$val_pcode' limit 1"));

if ($B_insert == ''){
$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());   
}

list($D_insert) = mysql_fetch_row(mysql_query("select D_id from D where D_country = '$val_country' and C_location = '$val_loc' limit 1"));

if ($D_insert == ''){
$D_insert = "INSERT INTO D (D_country, D_location)
    VALUES ('$val_country', '$val_loc')";
$result = mysql_query($D_insert) or die (mysql_error());
$D_insert = mysql_insert_id() or die (mysql_error());   
}

// and so on..

list($C_insert) = mysql_fetch_row(mysql_query("select C_id from C where C_state = '$val_state' limit 1"));


$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());


Mon Nov 16, 2009 8:46 pm
Report this post
Profile
Display posts from previous:  Sort by  
Reply to topic   [ 13 posts ]  Go to page 1, 2  Next

Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
© Copyright 2003-2008 www.php-editors.com. The ultimate PHP Editor and PHP IDE site.