PHP MySQL 检查重复条目？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5378427/
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
PHP MySQL Check for duplicate entry?
提问by Jay Wit
How can I check if there already is a duplicate entry in the database? I don't want to prevent it, just a warning. So if it already exists it simply gives a warning, it's up to the user to ignore it or not.
Thanks in advance!
如何检查数据库中是否已经存在重复条目？我不想阻止它，只是一个警告。因此，如果它已经存在，它只会发出警告，由用户决定是否忽略它。
提前致谢！
回答by Saul
$key_id = 123;
$result = mysql_query("SELECT * FROM table WHERE key_id='$key_id'");
$num_rows = mysql_num_rows($result);
if ($num_rows) {
trigger_error('It exists.', E_USER_WARNING);
}
回答by John
I made a friendlier way to handle duplicates for something like a user ID. For example, if a user puts in an id john and that ID already exists, it will automatically be changed to john1. If john1 exists it will automatically be changed to john2, etc.
我做了一种更友好的方式来处理用户 ID 之类的重复项。例如，如果用户输入一个 id john 并且该 ID 已经存在，它将自动更改为 john1。如果 john1 存在，它将自动更改为 john2 等。
// Force the while statement to execute once
$usercount = 1;
// The number to append to the duplicate value
$incrementcount = 0;
// Store the original value
$origuserid = $userid;
while ($usercount != 0) { // Query the database for the current ID
$query = "SELECT COUNT(*) FROM userlist WHERE userid = '$userid'";
if ($stmt = $con->prepare($query)) {
$stmt->execute();
$stmt->close();
$stmt->bind_result($usercount);
$stmt->fetch();
} else {
die('Query error');
}
if ($usercount != 0) {
// if count is anything other than zero, it's a duplicate entry
$incrementcount++; // value to append
$userid = $origuserid . $incrementcount; // append value to original user id
// the while loop will execute again and keep incrementing the value appended
// to the ID until the value is unique
}
}
回答by bensiu
read about ON DUPLICATE KEY UPDATE
阅读关于重复密钥更新
回答by RandomCoder
Another option is to use a SELECT COUNT() query which will tell you the number of duplications instead of just checking for any rows returned.
另一种选择是使用 SELECT COUNT() 查询，它会告诉您重复的数量，而不仅仅是检查返回的任何行。
SELECT COUNT(*) FROM table WHERE field = '$field'
SELECT COUNT(*) FROM table WHERE field = '$field'
If you use the num rows method, it will be more efficient if you don't select all fields when you aren't going to use them - don't make the MySQL engine work harder than it needs to.
如果您使用 num rows 方法，如果您在不打算使用它们时不选择所有字段，效率会更高——不要让 MySQL 引擎工作得比它需要的更努力。
回答by Synposis
I realize this is an OLD post, but i had errors with the selected answer, as it would always return true when it was false, copied exactly from the answer above, so i wrote a slight variation of it.
我意识到这是一个旧帖子，但是我选择的答案有错误，因为它总是在错误时返回 true，完全从上面的答案复制，所以我写了它的一些变化。
function checkValid($ipCheck){
$con=mysqli_connect("localhost","******","******","$DB_NAME");
$result = mysqli_query($con,"SELECT * FROM $TBL_NAME");
$end = true;
while($row = mysqli_fetch_array($result) && $end)
{
if(strcmp($row['IP'],$ipCheck) == 0)
$end = false;
}
return $end;
}
What this does, is it scans all until it reaches a false statement. If it doesn't, then it will remain true, meaning no duplicates, and move on, otherwise, it finds a duplicate and returns false, and triggers the counter statement.
它的作用是扫描所有内容，直到遇到错误的陈述。如果没有，那么它将保持为真，意味着没有重复，然后继续，否则，它会找到重复并返回假，并触发计数器语句。
So Assume i am trying to compare IP's from my database, and the column is IP:
因此，假设我正在尝试比较数据库中的 IP，并且该列是 IP：
if (!empty($_SERVER['HTTP_CLIENT_IP'])){
$ip=$_SERVER['HTTP_CLIENT_IP'];
//Is it a proxy address
}elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
$ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
}else{
$ip=$_SERVER['REMOTE_ADDR'];
}
if(checkValid($ip)){
$sql = "INSERT INTO $TBL_NAME (IP) VALUES ('$ip')";
if (!mysqli_query($con,$sql))
{
die('Error: ' . mysqli_error($con));
}
}
else
echo "IP Already Exists In Database";
I am extremely new to PHP, but this works just fine for my purposes, and it doesn't seem to affect my speed when loading the page.
我对 PHP 非常陌生，但这对我的目的来说很好用，而且在加载页面时它似乎不会影响我的速度。
