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?

Dave Chen · Accepted Answer · 2015-07-05 00:08:51Z

7

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

edited Jul 5, 2015 at 0:08

answered Jul 4, 2015 at 23:01

Dave Chen

11k8 gold badges42 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Kyubeh2435436 Over a year ago

Hey again dave :P Anyway, why did u add imdbid, and msg together then _unique? I only want it to stop if the imdbid is already in the db but if the message is the same i dont mind. So lets say in my db i have: imdbid = 123 msg = Hey they enter in imdbid = 1234 msg = Hey it should still allow it to be inserted

Kyubeh2435436 Over a year ago

Also when i add the unique what exactly happens? And do I then use INSERT INTO requests (id,imdbid_unique,msg_unique) ?

Dave Chen Over a year ago

No problem, I've edited the code above to only set a UNIQUE constraint on imdbid alone. What happens next is that you just insert like you normally would, except you check errors from your database. If there is an error from $DBH->error(), then you know that you inserted duplicate data.

Kyubeh2435436 Over a year ago

When i try to do the Alter Table SQL i get this: MySQL said: #1170 - BLOB/TEXT column 'imdbid' used in key specification without a key length

Dave Chen Over a year ago

Can you change your datatype to an INT or VARCHAR? You can't place UNIQUE constraints on BLOB or TEXT. Also usually for an ID like an IMDB id, you wouldn't need TEXT or BLOB to hold it. TEXT is usually used for posts or comments.

|

meda · Accepted Answer · 2015-07-05 00:50:03Z

2

Simply run a query prior to inserting.

If found die the script:

$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 alternatively make the msg field 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 query like this:

$STH = $DBH->prepare('
call insert_request_msg(:imdbid,:msg)
');
$STH->bindParam(':imdbid', $_POST['imdbid']);
$STH->bindParam(':msg', $_POST['msg']);

edited Jul 5, 2015 at 0:50

answered Jul 5, 2015 at 0:16

meda

45.5k14 gold badges97 silver badges123 bronze badges

12 Comments

Kyubeh2435436 Over a year ago

Which would be faster, Your script or Daves? Im all about page load speed :P

meda Over a year ago

It would not be a big speed difference you can notice but the issue with daves it creates an execption because it is an error to insert a duplicate, I added on more example to use a stored procedure to check of the row exist or not all in one query

Kyubeh2435436 Over a year ago

Ok, I dont understand, Is the 2nd code block the WHOLE query in one? where would I place it that query if im putting call insert_request_msg(:imdbid,:msg) in the prepare()? Also do note, Im trying to get it to check if the imdbid is already in the database I could care less if the same message was used either on the same imdb or different ones.

meda Over a year ago

oh ok I made it simpler now check my edit, you would run this whole block in mysql so that the stored procedure gets created then you will be able to run it in one query

Kyubeh2435436 Over a year ago

Ok but remember, As I said, I dont care if the msg is unique or not I only care if the imdbid is already in the database Pleas can u update your code?

|

Alaksandar Jesus Gene · Accepted Answer · 2015-07-04 23:25:43Z

1

Try this

$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

answered Jul 4, 2015 at 23:25

Alaksandar Jesus Gene

6,89113 gold badges61 silver badges92 bronze badges

2 Comments

Kyubeh2435436 Over a year ago

Wouldnt that just add it and then just check if its not there?

Alaksandar Jesus Gene Over a year ago

I couldnt check its working. But it wont add with respect to SELECT, because that could return false. Can you check in realtime. If not, let me know.

Collectives™ on Stack Overflow

Check if row exists in the database before inserting

3 Answers 3

15 Comments

12 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

15 Comments

12 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related