inserting foreign keys into mysql tables using php

[Mish]

Could someone Please help me this is driving me mad......

I have 3 tables, one for storing user registration details (reg_details), one that lists their skills (skills) and one that lists their certifications (cert)….

And the primary key from the reg_details table, will be a foreign key in the skills table and the cert table.

All tables have their own auto_incremented id’s…but where I start to have a problem is when I want to take the last auto_increment id() from the reg_details table and insert this id into both the cert and skills table as foreign keys.

The last_insert_id() function works in the skills insert query when run straight after the insert statement for the reg_details table.



$regquery = "INSERT INTO reg_details (regNo, title, fname, lname, age, gender, addr1, addr2, addr3, county, town, postcode, email, passwd) VALUES (NULL, '" . $_POST['title'] . "', '" . $_POST['fname']. "', '" . $_POST['lname']. "', '" . $_POST['age']. "', '" . $_POST['gender']. "', '" . $_POST['addr1']. "', '" . $_POST['addr2']. "', '" . $_POST['addr3']. "', '" . $_POST['county']. "', '" . $_POST['town']. "', '" . $_POST['postcode']. "', '" . $_POST['email']."','". $_POST['pword'] . "');";

$skillquery = "INSERT INTO skills (sk_id, regNo, php, cpp, java, perl, javascript, xhtml,xml) VALUES (NULL,last_insert_id(),'".$_POST['php']."','".$_POST['cpp']."', '".$_POST['java']."','".$_POST['perl']."','".$_POST['javascript']."','".$_POST['xhtml']."','".$_POST['xml']."');";

$certquery = "INSERT INTO cert (certid, regNo, cisco, java, microsoft, certother) VALUES(NULL,NULL,'".$_POST['cisco']."','".$_POST['java']."','".$_POST['microsoft']."','".$_POST['certother']."');";

But how do I get that same regNo id from the previous tables to be inserted into the skills table.

I have tried to use the mysql_insert_id(), but at present can only get it to echo the value, but I cant get it to insert this value into other tables.

Any help will be great.

Thanks in advance

Mish

[Rawhide]

last_insert_id() isn't intended to be used in teh manner you are trying to use it. It only works when immediately queried after the insert. Set a PHP variable = last_insert_id(). Then use the variable for the other inserts.

[Mish]

Thanks for replying, i have finally got it to work doing exactly what you said but with the mysql_insert_id() funtion instead.

Thanks alot