检查 PDO Fetch select 语句何时返回 null

2021-12-26 00:00:00 php pdo

我有以下代码:

  $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?

推荐答案

这里有几件事...

PDOStatement::fetchAll() 返回一个数组数组.要检查记录，请尝试

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

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

开启 E_NOTICE 错误.您应该知道 $res['EMAIL'] 是一个未定义的索引.在脚本的顶部...

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

我建议为您的 EMAIL 列创建唯一约束.这样，您将无法插入重复的记录.如果尝试，PDO 将触发错误或抛出异常，具体取决于您如何配置 PDO::ATTR_ERRMODE 属性(请参阅 http://php.net/manual/en/pdo.setattribute.php)

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 }

相关文章