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?.