php - Check if row exists in the database before inserting

Question

$DBH = new PDO($dsn, $username, $password, $opt);

$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$STH = $DBH->prepare("INSERT INTO requests (id,imdbid,msg) VALUES ('',:imdbid,:msg)");
$STH->bindParam(':imdbid', $_POST['imdbid']);
$STH->bindParam(':msg', $_POST['msg']);

$STH->execute();
echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";

Is there either some SQL Query that will check and insert or what? I need it to check if whatever the user typed is already in the db so if the user typed in a imdbid that is already there then it wont continue inserting anything. How would I do this? I know I can do a fetch_all and make a foreach for it but doesnt that only work after you execute?

Answer 1

It's better to set a constraint on your columns to prevent duplicate data instead of checking and inserting.

Just set a UNIQUE constraint on imdbid:

ALTER TABLE `requests` ADD UNIQUE `imdbid_unique`(`imdbid`);

The reason for doing this is so that you don't run into a race condition.

There's a small window between finishing the check, and actually inserting the data, and in that small window, data could be inserted that will conflict with the to-be-inserted data.

Solution? Use constraints and check $DBH->error() for insertion errors. If there are any errors, you know that there's a duplicate and you can notify your user then.

I noticed that you are using this, $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);. In this case, you don't need to check ->error() because PDO will throw an exception. Just wrap your execute with try and catch like this:

$duplicate = false;

try {
    $STH->execute();
} catch (Exception $e) {
    echo "<p>Failed to Request ".$_POST['imdbid']."!</p>";
    $duplicate = true;
}

if (!$duplicate)
    echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";