I am trying to insert data into a contacts table using foreign keys in mysql, the structure of tables are as follows
CONTACTS TABLE id contactname phone_number fk_id
USERS TABLE pers_id username email securecode
The fk_id in the contacts table is the FOREIGN KEY and it relates to the pers_id PRIMARY KEY in the users table, I am trying to insert a new contact with relates to a user in the users table
All is done in PHP with MySQl. I used this sql statement shown below in the code but it does not work
$name = $_GET['contactname']; $number = $_GET['phone_number']; $username = $_GET['username']; $sql = "INSERT INTO contacts SET contactname='$name', phone_number='$number',fk_id=(select pers_id from users where username='$username')"; $result = mysqli_query($conn, $sql);
Hope anyone could help
Advertisement
Answer
You can use insert ... select
:
insert into contacts(contactname, phone_number, fk_id) select :contactname, :phone_number, pers_id from users u where username = :username
For this to properly work, username
should be a unique key in the users
table.
You could also express this as follows:
insert into contacts(contactname, phone_number, fk_id) values ( :contactname, :phone_number, (select pers_id from users u where username = :username) );
Note that I rewrote the statement to use proper parameters. Never concatenate data coming from outside your program in a query string: this opens up your code to SQL injection. Recommended reading: How can I prevent SQL injection in PHP?.