Inserting Foreign key to primary key

[futhonguy]

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

[leviathan185]

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.

[futhonguy]

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 ?

[leviathan185]

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]

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
}

[futhonguy]

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]

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]

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]

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]

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?