Insert with Where Exists Statement

Question

I'm trying to insert a row into the Friend table if the username being entered already exists in table User. My code works if I remove the WHERE EXISTS statement but does not work with it.

$username = $_POST["username"];
$friendname = $_POST["friendname"];
$friend = $friendname;

$statement = mysqli_prepare($con, "INSERT INTO Friend (username, friend_username)
VALUES(?,?) WHERE EXISTS (SELECT * FROM User WHERE username = ?)");

mysqli_stmt_bind_param($statement, "sss", $username, $friendname, $friend);   
mysqli_stmt_execute($statement); 
mysqli_stmt_close($statement);
mysqli_close($con);

Debug:

PHP Notice: Undefined index: username in G:\PleskVhosts\playchesswithbrandon.net\httpdocs\UpdateFriendName.php on line 3 PHP Notice: Undefined index: friendname in G:\PleskVhosts\playchesswithbrandon.net\httpdocs\UpdateFriendName.php on line 4 PHP Warning: mysqli_stmt_bind_param() expects parameter 1 to be mysqli_stmt, boolean given in G:\PleskVhosts\playchesswithbrandon.net\httpdocs\UpdateFriendName.php on line 8 PHP Warning: mysqli_stmt_execute() expects parameter 1 to be mysqli_stmt, boolean given in G:\PleskVhosts\playchesswithbrandon.net\httpdocs\UpdateFriendName.php on line 9 PHP Warning: mysqli_stmt_close() expects parameter 1 to be mysqli_stmt, boolean given in G:\PleskVhosts\playchesswithbrandon.net\httpdocs\UpdateFriendName.php on line 10

There is nothing inside `$_POST`, or at least not a `username` and `friendname`. Print out all request vars (`print_r($_REQUEST)`), maybe you used get? — giorgio, Dec 09 '15 at 09:49

score 0 · Answer 1 · answered Dec 09 '15 at 09:40

before you use where exists you should select result which compare it with query inside the exists like this:

$statement = mysqli_prepare($con, "INSERT INTO Friend (username, friend_username)

    SELECT username, friend_username
    FROM Friend 
    WHERE EXISTS (SELECT * FROM User WHERE username = ?)");

this link may help you

score 0 · Answer 2 · answered Dec 09 '15 at 10:14

Ok, so your query should look something like this:

$statement = mysqli_prepare($con, "INSERT INTO Friend (username, friend_username)
VALUES(?,?) WHERE COUNT(SELECT * FROM User WHERE username = ?)=1");

You do this to make sure, that the user exists.

P.S. Using strings as PK is a really bad idea

Insert with Where Exists Statement

2 Answers2