php - check when PDO Fetch select statement returns null

Question

I have the following code:

  $check = $dbh->prepare("SELECT * FROM BetaTesterList WHERE EMAIL = ?");
                $check->execute(array($email));
                $res = $check->fetchAll();

                if (!($res['EMAIL'])){
                        $stmt = $dbh->prepare("INSERT INTO BetaTesterList(EMAIL) VALUES (?)");
                        $stmt->execute(array($email));
                } else {
                        $return['message'] = 'exists';
                }

However this still inserts the value although the record already exists in the DB. How do I prevent this?

Answer 1

Couple of things here...

1. PDOStatement::fetchAll() returns an array of arrays. To check for a record, try

   if (count($res) == 0) {
       // no records found
   }
   

2. Turn on E_NOTICE errors. You would have known that $res['EMAIL'] was an undefined index. At the top of your script...

   ini_set('display_errors', 'On');
   error_reporting(E_ALL);
   

3. I'd recommend creating a unique constraint on your EMAIL column. That way, you would not be able to insert a duplicate record. If one was attempted, PDO would trigger an error or throw an exception, depending on how you configure the PDO::ATTR_ERRMODE attribute (see http://php.net/manual/en/pdo.setattribute.php)

   If you're not inclined to do so, consider using this query instead...

   $check = $dbh->prepare("SELECT COUNT(1) FROM BetaTesterList WHERE EMAIL = ?");
   $check->execute(array($email));
   $count = $check->fetchColumn();
   
   if ($count == 0) {
       // no records found
   } else {
       // record exists
   }