Check if row exists in the database before inserting

by

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

More Related Contents:

  1. mysqli or PDO – what are the pros and cons? [closed]
  2. PDOException SQLSTATE[HY000] [2002] No such file or directory
  3. PHP PDO: charset, set names?
  4. How to get numeric types from MySQL using PDO?
  5. How can I use PDO to fetch a results array in PHP?
  6. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  7. Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
  8. Use bound parameter multiple times
  9. MySQL check if a table exists without throwing an exception
  10. how safe are PDO prepared statements
  11. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  12. PHP PDO – Num Rows
  13. How to solve General error: 2006 MySQL server has gone away
  14. Replacing mysql_* functions with PDO and prepared statements
  15. PDO + MySQL and broken UTF-8 encoding
  16. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  17. php code to test pdo is available?
  18. Calling stored procedure with Out parameter using PDO
  19. When *not* to use prepared statements?
  20. PDO fetchAll group key-value pairs into assoc array
  21. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  22. Setting a connect timeout with PDO
  23. What is the best way to insert multiple rows in PHP PDO MYSQL?
  24. PDO MySQL: Insert multiple rows in one query
  25. How to bind LIKE values using the PDO extension?
  26. Binding parameters for WHERE IN clause with PDO [duplicate]
  27. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  28. PHP 7 RC3: How to install missing MySQL PDO
  29. PHP: mysql v mysqli v pdo [closed]
  30. PDO and MySQL ‘between’

Leave a Comment