检查 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?

推荐答案

这里有几件事...

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

  1. 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
    }
    

  • 相关文章