WHERE NOT EXISTS

user_2238083 0
Viewed 63

I have a simple table with two columns "referralID" & "studentID"

I can add like this

$stmt = $pdo->prepare('INSERT INTO referralStudents (referralID,studentID) VALUES (?, ?)');
$stmt->execute([$myID,$studentID]);

I'm trying to get the WHERE NOT EXISTS statement to work. If there is already a row with both "referralID" & "studentID" don't add.

Both of these don't work can you show me where I'm going wrong?

$stmt = $pdo->prepare('INSERT INTO referralStudents (referralID,studentID) VALUES (?, ?) WHERE NOT EXISTS (SELECT * FROM referralStudents WHERE referralID = ? and studentID = ?")');
$stmt->execute([$myID,$studentID,$myID,$studentID]);
        
$stmt = $pdo->prepare('INSERT INTO referralStudents (referralID,studentID) VALUES (?, ?) WHERE NOT EXISTS (referralID,studentID) VALUES (?, ?)');
$stmt->execute([$myID,$studentID,$myID,$studentID]);
mysql php
2 Answers

You need a SELECT statement and not VALUES to apply the conditions of the WHERE clause:

INSERT INTO referralStudents (referralID,studentID) 
SELECT ?, ? 
FROM dual
WHERE NOT EXISTS (SELECT * FROM referralStudents WHERE referralID = ? and studentID = ?)

You may remove FROM dual if your version of MySql is 8.0+.

See a simplified demo.

user_104988280

If the fields are empty i bet they are NULL. A field filled with NULL is existing but its content is NULL (NULL doesnt mean empty or non existing). Maybe this is what you need:

INSERT INTO refferalStudents (refferalID, studentID) VALUES (?,?) WHERE refferalID IS NULL AND studentID IS NULL
user_68228450
Related