CODEWITHSUNDEEP
phpmysqlsql
dhani
dhani

Reputation: 127

Database Insertion with foreign keys

I have the following tables
tbl_user - id, username, password (id is pk and auto-incremented)
tbl_userprofile - id, userid, name, surname, gender, nationality,address, mobile, department, email, question, answer.
userid is a foreign key in tbl_userprofile referencing id in tbl_user I'm trying insert a new user into tbl_userprofile. To get the userid in tbl_userprofile My approach is to use a select statement to get the id form tbl_user but i'm not sure how to structure the WHERE clause. 

$SQL = "INSERT INTO tbl_userprofile (userid, name, surname, gender, nationality, address, mobile, department, email, question, answer) VALUES

('(SELECT id from tbl_user WHERE *something*) ', '$name','$surname','$gender','$nationality','$address','$mobile','$department','$email','$question','$answer')"; How do i structure my WHERE clause? or can i approach this in a different way? NOTE: I thought i should mention that the insertion into tbl_user and tbl_userprofile is handled two different scripts because the forms are on two different pages.

Upvotes: 0

Views: 111

Answers (1)

Sebas
Sebas

Reputation: 21542

You can use an INSERT SELECT statement:

INSERT INTO tbl_userprofile (userid, name, surname, gender, nationality, address, mobile, department, email, question, answer) 
SELECT id, '$name', '$surname', '$gender', '$nationality', '$address','$mobile', '$department', '$email', '$question', '$answer'
FROM tbl_user 
WHERE *something*

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Upvotes: 1

Related Questions