插入前检查数据库表中的重复项

I need to create a form that gathers a first name, last name, and an email address. I have created a table in MySQL called guestbook. The table looks something like the following:

我需要创建一个收集名字,姓氏和电子邮件地址的表单。我在MySQL中创建了一个名为guestbook的表。该表看起来如下所示:

CREATE TABLE guestbook (
    id int unsigned NOT NULL AUTO_INCREMENT,
    firstName varchar(50) NOT NULL,
    lastName varchar(50) NOT NULL,
    email varchar(250) NOT NULL,
    status int NOT NULL,
    sort int NOT NULL
);

And here is my code thus far:

到目前为止,这是我的代码:

<?php

Global $Conn;
$Conn = new mysqli("localhost","151_millet","2gZMXYGC","GUESTBOOK");

if(!$Conn) {
    $ErrorMsg = "Couldn't connect to the database";
}

$FName = $_POST["fname"];
$LName = $_POST["lname"];
$Email = $_POST["email"];

// The series of ifs below tests to see if each field is blank
// If it is blank it will output an error message for each that is blank
if($FName == "") {
    $ErrorMsg .= "First Name Was Left Blank<br>";
}

if($LName == "") {
    $ErrorMsg .= "Last Name Field Was Left Blank.<br>";
}

if($Email == "") {
    $ErrorMsg .="Email field was left blank.<br>";
}

I have no idea how to check for duplicates in the email field. Any help would be appreciated. I think I need to come up with a way to test $_POST against whats already in the db.

我不知道如何检查电子邮件字段中的重复项。任何帮助,将不胜感激。我想我需要想出一种方法来测试$ _POST以防止数据库中已有的内容。

1 个解决方案

#1

Alrighty, now we've cleaned up this question a bit. Let's get down to business.

好的,现在我们已经清理了一下这个问题。我们开始谈正事吧。

You want to check for duplicates in your database. There are a couple of things you need to do for this. Let's try to keep this as simple as possible while we're working on it too.

您想检查数据库中的重复项。你需要做几件事。让我们尽可能地保持这一点,同时我们也在努力。

First off, it's good to see you're using MySQLi and not the deprecated mysql_* functions. But we can handle MySQLi in a more object oriented manner. Also, using globals is frowned upon (and is actually not necessary, even in your current code!)

首先,很高兴看到你使用的是MySQLi而不是被弃用的mysql_ *函数。但是我们可以以更加面向对象的方式处理MySQLi。另外,使用全局变量是不受欢迎的(实际上没有必要,即使在你当前的代码中也是如此!)

One other thing to note is that there are slightly different styles of code used in modern PHP, like camel case, and how you format if statements. If you're interested in learning PHP, then you should take a look at the PHP-FIG PSRs.

还有一点需要注意的是,在现代PHP中使用的代码风格略有不同,例如camel case,以及如何格式化if语句。如果您对学习PHP感兴趣,那么您应该看一下PHP-FIG PSR。

<?php

$conn = new mysqli("localhost", "151_millet", "2gZMXYGC", "GUESTBOOK");

if ($conn->connect_errno) {
    // This is an error that will stop us from continuing, so assigning
    // the error message to a string, doesn't really help us in this case
    // The application NEEDS to stop
    throw new RuntimeException("Unable to connect to MySQL database.");
}

$firstName = $_POST["fname"];
$lastName  = $_POST["lname"];
$email     = $_POST["email"];

if (!$firstName || !$lastName || !$email) {
    echo "Please make sure to fill in all of your details.";

    // You may want to handle this differently, this is just to keep things
    // Very simple
    exit;
}

$query = "SELECT COUNT(1) FROM guestbook WHERE email = ?";
$stmt = $conn->prepare($query);
$count = 0;

if ($stmt) {
    $stmt->bind_param("s", $email);
    $stmt->execute();
    $stmt->store_result();

    $count = $stmt->num_rows;

    $stmt->close();
}

if ($count > 0) {
    echo "A user with that email address already exists.";
    exit;
}

// Do other stuff

That should help you out with what you need to do to check if an email address already exists. But you should also enforce this in your database by adding a unique key to the email column. That will mean that even if your code fails and would allow a duplicate email address entry, your database won't.

这应该可以帮助您完成检查电子邮件地址是否已存在所需的操作。但是,您还应该通过向电子邮件列添加唯一键来在数据库中强制执行此操作。这意味着即使您的代码失败并允许重复的电子邮件地址输入,您的数据库也不会。

#1