問題描述

$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>";

是否有一些 SQL 查詢將檢查和插入或什么?我需要它來檢查用戶輸入的內(nèi)容是否已經(jīng)在數(shù)據(jù)庫(kù)中，所以如果用戶輸入的 imdbid 已經(jīng)存在，那么它就不會(huì)繼續(xù)插入任何內(nèi)容.我該怎么做?我知道我可以做一個(gè) fetch_all 并為它做一個(gè) foreach 但這不是只有在你執(zhí)行后才有效嗎?

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?

推薦答案

最好在列上設(shè)置約束以防止重復(fù)數(shù)據(jù)，而不是檢查和插入.

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

只需在 imdbid 上設(shè)置一個(gè) UNIQUE 約束:

Just set a UNIQUE constraint on imdbid:

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

這樣做的原因是您不會(huì)遇到競(jìng)爭(zhēng)條件.

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

在完成檢查和實(shí)際插入數(shù)據(jù)之間有一個(gè)小窗口，在那個(gè)小窗口中，可能插入的數(shù)據(jù)會(huì)與要插入的數(shù)據(jù)發(fā)生沖突.

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.

解決方案?使用約束并檢查 $DBH->error() 是否存在插入錯(cuò)誤.如果有任何錯(cuò)誤，您就知道存在重復(fù)項(xiàng)，然后您可以通知您的用戶.

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.

我注意到你正在使用這個(gè)，$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);.在這種情況下，您不需要檢查 ->error() 因?yàn)?PDO 會(huì)拋出異常.只需像這樣用 try 和 catch 包裹你的執(zhí)行:

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>";

這篇關(guān)于插入前檢查數(shù)據(jù)庫(kù)中是否存在行的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！