Can you do a "INSERT SELECT WHERE" between two tables?

Question

So I've been trying to create a simple friend system. When you register, you get randomized numbers and chars of 8 in length. I save this number in a column to the user. I have been trying to insert the currently sessioned user(PHP), $SessionUser together with the friends' username, uidUsers using an "INSERT SELECT WHERE" statement, but something goes wrong. Heres something I have tried:

$sql = mysqli_query($conn, "INSERT into friends (uid1, uid2) 
    values($sessionUser, (SELECT  uidUsers FROM users WHERE idFriendCode = $idFriendCode)");

Inside the table, friends, I have two columns, uid1 (the sessioned user/sender) and uid2 (the reciever, name of specified $idFriendCode). I want to insert the $sessionUser to the uid1 and whatever username (uidUsers) that matches with the $idFriendCode to the uid2. This does not seem to work and I don't know why. I imagine the problem is that I can't use a PHP variable like this.

I know that I don't use prepared statements. I have tried to implement it, but I think it's much harder than just using a basic mysqli_query().

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Sep 05 '20 at 17:18
I figured. I eventually added the prepared statement from Tims answer. — Mark, Sep 06 '20 at 07:11

score 1 · Accepted Answer · answered Sep 05 '20 at 08:45

You may phrase your insert as an INSERT INTO ... SELECT:

INSERT into friends (uid1, uid2) 
SELECT $sessionUser, uidUsers
FROM users
WHERE idFriendCode = $idFriendCode;

Note that you should ideally be using a prepared statement here, so the above should look like:

INSERT into friends (uid1, uid2) 
SELECT ?, uidUsers
FROM users
WHERE idFriendCode = ?;

score 0 · Answer 2 · answered Sep 05 '20 at 08:49

Try having a new variable for select and use it in the insert query

example:

$select_qr='SELECT  uidUsers FROM users WHERE idFriendCode = $idFriendCode'

$sql = mysqli_query($conn, "INSERT into friends (uid1, uid2) 
    values($sessionUser, $select_qr)");

Can you do a "INSERT SELECT WHERE" between two tables?

2 Answers2