Inserting data into a table using foreign keys in MySQL

Question

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

score 0 · Accepted Answer · answered Nov 15 '20 at 15:43

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

Inserting data into a table using foreign keys in MySQL

1 Answers1