Inserting Foreign key to primary key

Question

futhonguy 0 Junior Poster in Training

14 Years Ago

Hi,

Currently i have 3 Tables - A, B and C. All 3 tables have their unique IDs (which are auto increment) and Table B and C are a foreign keys to Table A.
says:

Table A = A_id, A_fname, B_id, C_id
Table B = B_id, B_add, B_pcode
Table C = C_id, C_state

My application is set up with a form to insert records of a persons name, their address, postal codes and state simultaneously into these three tables.

my code is like this:

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

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

$A_insert = "INSERT INTO A (A_fname, B_id, C_id)
	    VALUES ('$val_fname', 'mysql_insert_id()', 'mysql_insert_id ()')";
$result = mysql_query($A_insert) or die (mysql_error());

When i checked on my display table, the foreign keys to Table A for Table B and C return the value '0'.
Anyone can help me?? thanks

php

5 Contributors
9 Replies
173 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by futhonguy

All 9 Replies

leviathan185 14 Junior Poster

14 Years Ago

Hi,
Currently i have 3 Tables - A, B and C. All 3 tables have their unique IDs (which are auto increment) and Table B and C are a foreign keys to Table A.
says:
Table A = A_id, A_fname, B_id, C_id
Table B = B_id, B_add, B_pcode
Table C = C_id, C_state
My application is set up with a form to insert records of a persons name, their address, postal codes and state simultaneously into these three tables.
my code is like this:
$B_insert = "INSERT INTO B (B_add, B_pcode)
	        VALUES ('$val_add', '$val_pcode')";
$result = mysql_query($B_insert) or die (mysql_error()); 

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

$A_insert = "INSERT INTO A (A_fname, B_id, C_id)
	    VALUES ('$val_fname', 'mysql_insert_id()', 'mysql_insert_id ()')";
$result = mysql_query($A_insert) or die (mysql_error());
When i checked on my display table, the foreign keys to Table A for Table B and C return the value '0'.
Anyone can help me?? thanks

It could be that you are using single quotes around the values that are meant to be integers, the single quotes are converting them to strings. try removing the single quotes but wrap the variable in {}.

I have never used a function inside an sql query string so i am not sure if this will cause issues or not. Try assigning it to a variable outside the query string and use the variable inside it.

leviathan185 14 Junior Poster

14 Years Ago

Thanks leviathan185.. It's work!!
I would like to check with you whether if i can, for example, on Table B, inserting a repetitive words and write a code where php is able to check through the table and extract the ID to Table A ?

Im not too sure what you are asking? can you provide an example?

nav33n 472 Purple hazed!

14 Years Ago

It could be that you are using single quotes around the values that are meant to be integers, the single quotes are converting them to strings.

You are almost right. Since its a function, there is no need for single quotes for mysql_insert_id.
Secondly, Even integers can have single quotes around them. It will not cause any problems. The main thing is, integers can work without single quotes around them, but strings will not work.
@futhonguy,
You can query the table with that word and if it returns more than 1 row, then use that id.
Eg.

$b_add = $_REQUEST['b_add'];
$query = "select id from tableB where B_add = '$b_add'";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0) {
   $row = mysql_fetch_assoc($result);
   $id = $row['id'];
 //insert $id to tableA
} else {
 //insert a record to tableB
//insert mysql_insert_id to tableA
}

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

futhonguy 0 Junior Poster in Training · Answer 1 · 2009-11-12T11:16:49+00:00

Thanks leviathan185.. It's work!!

I would like to check with you whether if i can, for example, on Table B, inserting a repetitive words and write a code where php is able to check through the table and extract the ID to Table A ?

futhonguy 0 Junior Poster in Training · Answer 2 · 2009-11-12T12:13:56+00:00

Ok thanks Nav33n.
leviathan185, below is the table i drew out for my query. hope this will help me to explain clearly than what i type out in words.

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

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

Table C	
C_id	C_state
1	CA
2	FA
3	DC

So what i meant earlier was that from Table A, A_id=3 and 5 both shared the same B_id. So for the user insert, is there any script that i can prevent duplication or use php to extract the b_id=2 for A_id = 3 and A_id = 5 ?

leviathan185 14 Junior Poster · Answer 3 · 2009-11-12T12:38:24+00:00

You are almost right. Since its a function, there is no need for single quotes for mysql_insert_id.
Secondly, Even integers can have single quotes around them. It will not cause any problems. The main thing is, integers can work without single quotes around them, but strings will not work.

Thanks nav33n, I was tought that integers could not have the single quotes. perhaps I confused it with best pracice at the time.

So the issue was with the fucntion and the quotes around it?

urtrivedi 276 Nearly a Posting Virtuoso · Answer 4 · 2009-11-12T14:45:23+00:00

try following code

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

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

$A_insert = "INSERT INTO A (A_fname, B_id, C_id)	    VALUES ('$val_fname', [B]'{$bid}', '{$cid}'[/B])";
$result = mysql_query($A_insert) or die (mysql_error());

venkat0904 1 Junior Poster · Answer 5 · 2009-11-12T14:48:49+00:00

exactly... mysql_insert_id() returns the id for last insert statement.. as soon as u execute another insert, the previous values is gone... so u need to save it before executing the next insert
read the notes here http://php.net/manual/en/function.mysql-insert-id.php

try following code

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

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

$A_insert = "INSERT INTO A (A_fname, B_id, C_id)	    VALUES ('$val_fname', [B]'{$bid}', '{$cid}'[/B])";
$result = mysql_query($A_insert) or die (mysql_error());

futhonguy 0 Junior Poster in Training · Answer 6 · 2009-11-13T09:05:15+00:00

Ok.
Will these help if i am to do some referencing between these tables? (see the table). I.E. inserting Table A with i similar id from Table B and C?

Inserting Foreign key to primary key

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers