使用 PHP 防止重复记录到表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19714341/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Prevent duplicate records to a table using PHP
提问by Andy R
I want to prevent duplicate values into a database table from a form using PHP.
我想防止使用 PHP 的表单中的重复值进入数据库表。
I have created the following:
我创建了以下内容：
A database with a table named clients:
具有名为clients的表的数据库：
CREATE TABLE clients(
firstName varchar(20),
lastName varchar(20),
primary key(firstName, lastName));
A simple form named form.html
一个名为form.html的简单表单
<h2>Enter your First and Last Name</h2>
<form action="frm_script.php" method="post">
<p><strong>First Name:</strong><br /> <input type="text" name="firstName" /></p>
<p><strong>Last Name:</strong><br /> <input type="text" name="lastName"/></p>
<input type="submit" name="submit" value="Add Customer" />
</form>
A form processing script named frm_script.php
一个名为frm_script.php 的表单处理脚本
<?php
if(isset($_POST['submit']))
{
//get the name and comment entered by user
$firstName = $_POST['firstName'];
$lastName = $_POST['lastName'];
//connect to the database
$dbc = mysqli_connect('host', 'username', 'password', 'dbname') or die('Error connecting to MySQL server');
//insert results from the form input
$query = "INSERT IGNORE INTO clients(firstName, lastName) VALUES('$firstName', '$lastName')";
$result = mysqli_query($dbc, $query) or die('Error querying database.');
mysqli_close($dbc);
}
echo "Customer Added";
?>
So far with my frm_script.phpfile the above works and for a unique record displays customer added. However, for a duplicate record it throws "Error Querying Database".
到目前为止，使用我的frm_script.php文件，上述工作和唯一记录显示客户添加。但是，对于重复记录，它会抛出“错误查询数据库”。
How can I update the frm_script.phpscript for the following?
如何为以下内容更新frm_script.php脚本？
If a duplicate row is found on entering a First / Last name combination it should display the message "Client already listed" along with that record.
如果在输入名字/姓氏组合时发现重复行，则应显示消息“客户已列出”以及该记录。
If no duplicate row is found on entering a First / Last name combination on the form it should insert the entry to the database and display the message "customer added"
如果在表单上输入名字/姓氏组合时未发现重复行，则应将条目插入数据库并显示消息“已添加客户”
I have read that a SELECT should be run first then an INSERT but I am not sure how to implement this into my existing code.
我读过应该先运行 SELECT 然后运行 INSERT 但我不确定如何将其实现到我现有的代码中。
回答by Sudheer
<?php
if(isset($_POST['submit'])) {
//get the name and comment entered by user
$firstName = $_POST['firstName'];
$lastName = $_POST['lastName'];
//connect to the database
$dbc = mysqli_connect('host', 'username', 'password', 'dbname') or die('Error connecting to MySQL server');
$check=mysqli_query($dbc,"select * from clients where firstname='$firstname' and lastname='$lastname'");
$checkrows=mysqli_num_rows($check);
if($checkrows>0) {
echo "customer exists";
} else {
//insert results from the form input
$query = "INSERT IGNORE INTO clients(firstName, lastName) VALUES('$firstName', '$lastName')";
$result = mysqli_query($dbc, $query) or die('Error querying database.');
mysqli_close($dbc);
}
echo "Customer Added";
};
?>
just check for rows in your db for firstname and lastname if exists echo- your message else insert
只需检查您的数据库中的名字和姓氏的行（如果存在）回显 - 您的消息否则插入
回答by sofa
Since your primary key is (firstName, lastName), you don't need php to prevent insertion of duplicate values. Mysql does that for you, because primary keys have to be unique.
(if it was'nt your primary key you could use a unique constraint)
由于您的主键是 (firstName, lastName)，您不需要 php 来防止插入重复值。Mysql 为你做这件事，因为主键必须是唯一的。
（如果它不是您的主键，您可以使用唯一约束）
To display your error message "Client already listed" on duplicate entries, you can use mysqli_errno($dbc)and check for the error code 1062.
Quick fix that should do what you want: replace or die('Error querying database.');with or die(mysqli_errno($dbc) == 1062 ? "Client already listed" : 'Error querying database.');
要在重复条目上显示错误消息“客户端已列出”，您可以使用mysqli_errno($dbc)并检查错误代码1062。
应该做你想做的快速修复：替换or die('Error querying database.');为or die(mysqli_errno($dbc) == 1062 ? "Client already listed" : 'Error querying database.');
回答by Charles
After you process the $_POSTdata, use a SELECTquery followed by an ifstatement to check if there are any rows in the table with the same first name and last name as the $_POSTfirst name and last name data.
处理完$_POST数据后，使用SELECT查询后跟if语句来检查表中是否有任何行的名字和姓氏与$_POST名字和姓氏数据相同。
If there is, display a message that this data has already been inserted.
如果有，则显示一条消息，表明该数据已被插入。
If there isn't, use an INSERTquery to add this data to the database table, and display a message that this data has been added to the table.
如果没有，请使用INSERT查询将此数据添加到数据库表中，并显示一条消息，表明此数据已添加到表中。
回答by Gabe Spradlin
I have this problem all the time. I've gone from using an auto-incremented primary to using an md5 hash most of the time. So while I haven't implemented this myself yet - no opportunity/need presently - you could do create a primary key (VARCHAR 32) that is an md5 hash of the first and last name. Like this:
我一直有这个问题。大多数情况下，我已经从使用自动递增的主函数转变为使用 md5 散列。所以虽然我自己还没有实现这个 - 目前没有机会/需要 - 你可以创建一个主键（VARCHAR 32），它是名字和姓氏的 md5 哈希。像这样：
$key = md5(strtolower($firstName . $lastName));
Make the column this key goes into te primary key. Then you use an INSERT IGNOREand you can ignore any errors.
使这个键进入主键的列。然后您使用 anINSERT IGNORE并且您可以忽略任何错误。
Then you run a SELECTon the $key to return everything related to $keyand display any info you have on the user in a friendly format. You don't have to show the user an error at that point, simply provide them with the info already captured. If you are worried about users with the same name seeing each other's data then the data can be obfuscated. For example instead of showing a phone number (333) 444-5555 you show (333) 444-5*5 or (333) **-5555. Something that gives the user enough info to know if it is them or someone else with the same name.
然后SELECT在 $key 上运行 a以返回与用户相关的$key所有信息，并以友好的格式显示用户的任何信息。此时您不必向用户显示错误，只需向他们提供已捕获的信息即可。如果您担心同名用户看到彼此的数据，那么数据可能会被混淆。例如，不是显示电话号码 (333) 444-5555，而是显示 (333) 444-5* 5 或 (333) **-5555。为用户提供足够信息以了解是他们本人还是同名其他人的东西。
Typically I've used the SELECT, if empty INSERTand SELECTagain, method. And it works but it is significantly slower than a simple INSERT IGNOREand then SELECT. I use a DAO I've built on top of PDO and the INSERT IGNOREworks fine when a single column is the primary key. I have not tried it on a primary key based on more than 1 column nor have I tried it with the mysqli_* function.
通常，我使用SELECT, if empty INSERTand SELECTagain, 方法。它可以工作，但它比简单的INSERT IGNORE然后慢得多SELECT。我使用我在 PDO 之上构建的 DAO，INSERT IGNORE当单列是主键时工作正常。我没有在基于 1 个以上列的主键上尝试过它，也没有用 mysqli_* 函数尝试过它。
