PHP - check if a string exists before inserting
$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 some kind of SQL query that will check and insert or what? I need to check if any user typed in the db, so if the user typed in imdbid that already exists, it will not continue to insert anything. How should I do it? I know I can do fetch_all and do foreach for it, but doesn't that only work after you execute?
source to share
Better to set a constraint on your columns to prevent duplicate data instead of validating and inserting.
Just set the UNIQUE constraint to imdbid
:
ALTER TABLE `requests` ADD UNIQUE `imdbid_unique`(`imdbid`);
The reason for this is so that you don't run into race conditions .
There is a small window between the end of the validation and the actual insertion of data, and in this small window, data can be inserted that will conflict with the data to be inserted.
Decision? Use constraints and check $DBH->error()
for insert errors. If there are any errors, you know there is a duplicate and then you can notify your user.
I noticed that you are using this $DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
,. In this case, you do not need to check ->error()
, because PDO will throw an exception. Just terminate the execution 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>";
source to share
Just run the query before inserting.
If die script is found:
$DBH = new PDO($dsn, $username, $password, $opt);
$DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$sql = 'SELECT COUNT(*) from requests WHERE imdbid = :imdbid';
$stmt = $DBH->prepare($sql);
$stmt->execute(array(':imdbid' => $_POST['imdbid']));
if($stmt->fetchColumn()){ die('Already exist');}
$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>";
or, conversely, make the field msg
unique.
Using a stored procedure:
DELIMITER //
CREATE PROCEDURE insert_request_msg(IN `p_imbd`, IN `p_msg`)
IF NOT EXISTS (SELECT COUNT(*) from requests WHERE imdbid = p_imbd)
BEGIN
INSERT INTO requests (id,imdbid,msg) VALUES ('',p_imbd,p_msg)
END
END IF; //
DELIMITER ;
You call it in one request like this:
$STH = $DBH->prepare('
call insert_request_msg(:imdbid,:msg)
');
$STH->bindParam(':imdbid', $_POST['imdbid']);
$STH->bindParam(':msg', $_POST['msg']);
source to share
try it
$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) WHERE NOT EXISTS(SELECT imdbid FROM requests WHERE imdbid =:imdbid)");
$STH->bindParam(':imdbid', $_POST['imdbid']);
$STH->bindParam(':msg', $_POST['msg']);
$STH->execute();
echo "<p>Successfully Requested ".$_POST['imdbid']."! Thanks!</p>";
source to share