使用PHP PDO扫描MySql表中重复项的正确方法是什么

Question

我应该如何正确编写PHP脚本，以便它在数据库中执行尽可能少的查询以检查重复项？我想知道哪一列是重复的并得到它。我试图使用PDO异常，但我只得到了重复的错误代码、错误消息和跟踪。我想要一种使用PDO更经济高效的方式。(原谅我英语不好(

这是我的功能

<?php
require 'Connection.php';
class Test extends Connection {
public function checkForDuplicatedUsernames($username) : String {
try {
$statement = $this->connect()->prepare("SELECT username FROM users WHERE username = ?");
} catch (PDOException $exception){
return "error code 1"; //Connection error
}
$statement->execute([$username]);
$username = $statement->fetch();
if ($username !== false) {
return $username['username'];
}
return "go on";
}
public function checkForDuplicatedEmails($email) : String {
try {
$statement = $this->connect()->prepare("SELECT username FROM users WHERE email = ?");
} catch (PDOException $exception){
return "error code 1"; //Connection error
}
$statement->execute([$email]);
$username = $statement->fetch();
if ($username !== false) {
return $username['email'];
}
return "go on";
}
public function setUsersFromStatement() : int {
if ($this->checkForDuplicatedUsernames('test') === "go on"){
if ($this->checkForDuplicatedEmails('test') === "go on"){
try {
$sqlSelectString = "INSERT INTO users(username,password,email,firstname,lastname,birthday) VALUES (?,?,?,?,?,?)";
//PRIMARY KEY username, UNIQUE email
$statement = $this->connect()->prepare($sqlSelectString);
} catch (PDOException $exception){
return -1;
}
$statement->execute(['test','test','test','test','test','1984-04-03']);
$result = $statement->rowCount();
$statement = null;
return $result;
}
return 3;
}
return 4;
}
}

连接类

<?php
class Connection {
private string $host = "localhost";
private string $username = "test";
private string $password = "test";
private string $dbname = "testdb";
public function connect(): PDO
{
$dataSourceName = 'mysql:host=' . $this->host . ';dbname=' . $this->dbname;
$options = [
PDO::ATTR_EMULATE_PREPARES   => false,
PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
return new PDO($dataSourceName, $this->username, $this->password, $options);
}
}

Answer 1

您可以使用一个查询来检查重复的电子邮件或用户名。

<?php
require 'Connection.php';
class Test extends Connection {
public function checkForDuplicatedUsernameOrEmail($username, $email) : string {
try {
$statement = $this->connect()->prepare("
SELECT MAX(username = ?) AS usernameDuplicated, MAX(email = ?) AS emailDuplicated 
FROM users 
WHERE username = ? OR email = ?");
$statement->execute([$username, $email, $username, $email]);
$row = $statement->fetch(PDO::FETCH_ASSOC);
if (!$row) {
return '';
} elseif ($row['usernameDuplicated']) {
return 'username';
} else {
return 'email';
}
} catch (PDOException $exception){
return false;
}
}
public function setUsersFromStatement() : int {
$dup = $this->checkForDuplicatedUsernameOrEmail('test', 'test');
if ($dup == 'username') {
return 4;
} elseif ($dup == 'email') {
return 3;
}
try {
$sqlSelectString = "INSERT INTO users(username,password,email,firstname,lastname,birthday) VALUES (?,?,?,?,?,?)";
//PRIMARY KEY username, UNIQUE email
$statement = $this->connect()->prepare($sqlSelectString);
} catch (PDOException $exception){
return -1;
}
$statement->execute(['test','test','test','test','test','1984-04-03']);
$result = $statement->rowCount();
$statement = null;
return $result;
}
}