如果在字段中找到指定值，是否拒绝PDO MySQL语句? [英] Reject PDO MySQL statement if a specified value is found in a field?

问题描述

我已经搜索过Google，但是运气不佳，所以也许SO可以帮上忙.我正在为几个项目开发一个用户身份验证系统，并且需要确保电子邮件地址(也就是用户名)是唯一的.

I've searched Google but not had much luck with this, so maybe SO can help. I am developing a user authentication system for a couple of projects and need to ensure that the email address, which is also the username, is unique.

我在MySQL查询中将该字段设置为唯一，但是如果提供的电子邮件与现有记录匹配，我需要一种方法来取消SQL查询并通知用户指定的电子邮件已在使用中".

I set the field as unique in the MySQL query, but I need a way to cancel an SQL query and notify the user that "the specified email is already in use" if the supplied email matches an existing record.

我可以在插入之前进行选择查询，但是我想知道是否有一种方法可以编写SQL查询，以便在电子邮件中不存在现存的example.com或类似内容的情况下执行插入操作.

I could do a select query before the insert, but I am wondering if there is a way to write the SQL query so that an insert is performed IF NOT EXISTS [e-mail] IN email or something like that.

然后，如果未执行插入操作，我需要知道，并且除了其他错误之外，我还必须能够告诉它.

Then if the insert is not performed, I need to know, and I need to be able to tell it apart from another error.

这可能吗?怎么样?

推荐答案

如果您使用的是PDO，则可以捕获异常并检查状态代码，例如

If you're using PDO, you can just catch the exception and check the status code, eg

// make sure you're set to throw exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('INSERT INTO `user` (`email`) VALUES (?)');
try {
    $stmt->execute([$email]);
} catch (PDOException $e) {
    $errorInfo = $stmt->errorInfo(); // apparently PDOException#getCode() is pretty useless
    if ($errorInfo[1] == 1586) {
        // inform user, throw a different exception, etc
    } else {
        throw $e; // a different error, let this exception carry on
    }        
}

请参见 http://dev.mysql .com/doc/refman/5.5/zh-CN/error-messages-server.html#error_er_dup_entry_with_key_name

如果使用MySQLi，过程将类似

The process would be similar if using MySQLi

$stmt = $mysqli->prepare('INSERT INTO `user` (`email`) VALUES (?)');
$stmt->bind_param('s', $email);
if (!$stmt->execute()) {
    if ($stmt->errno == 1586) {
        // inform user, throw a different exception, etc
    } else {
        throw new Exception($stmt->error, $stmt->errno);
    }
}

这篇关于如果在字段中找到指定值，是否拒绝PDO MySQL语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！