php 插入前检查重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7719039/
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
Check for duplicates before inserting
提问by Mike
Before inserting into the database, I'm using the following code to check for duplicates. To me, a duplicate is only considered a duplicate when name, description, price, city, and enddatematch.
在插入数据库之前，我使用以下代码检查重复项。对我来说，重复的只考虑重复时name，description，price，city，和enddate比赛。
foreach($states_to_add as $item) {
$dupesql = "SELECT
COUNT(*)
FROM
table
WHERE
(
name = '$name'
AND description = '$description'
AND manufacturer = '$manufacturer'
AND city ='$city'
AND price = '$price'
AND enddate = '$end_date'
)";
$duperaw = mysql_query($dupesql);
if($duperaw > 0) {
echo nl2br("$name already exists in $city \n");
}
else {
$sql = "INSERT INTO table (..... (here go the values to be inserted)
....
Each value is defined prior to running through this checking, my result always comes back as item already exists. I dumped "dupesql" and copy/pasted the command into phpmyadmin which comes back with count 0.
每个值都是在运行此检查之前定义的，我的结果总是返回，因为项目已经存在。我转储了“dupesql”并将命令复制/粘贴到 phpmyadmin 中，该命令返回计数为 0。
回答by hoppa
You want to do the following:
您想要执行以下操作：
$dupesql = "SELECT * FROM table where (name = '$name' AND description = '$description' AND manufacturer = '$manufacturer' AND city ='$city' AND price = '$price' AND enddate = '$end_date')";
$duperaw = mysql_query($dupesql);
if (mysql_num_rows($duperaw) > 0) {
//your code ...
}
See Herefor more information.
请参阅此处了解更多信息。
回答by Ben
As I see it your question can be broken down into 2 parts. Why is my PHP code not working? I don't know, don't know much PHP and other people seem to have just answered that :-). The second question is how can I check for duplicates? You're checking for duplicates the completely wrong way.
在我看来，您的问题可以分为两部分。为什么我的 PHP 代码不起作用？我不知道，对 PHP 了解不多，其他人似乎刚刚回答了这个问题:-)。第二个问题是如何检查重复项？您正在以完全错误的方式检查重复项。
Create a unique index/ primary keyon your table. Then when you try to insert the DB will throw an error if there's a duplicate. Catch the error and deal with it how you want. Counting the number of records is definitely the wrong way to go and will be a significant detriment to the speed of your code.
在您的表上创建唯一索引/主键。然后当您尝试插入数据库时，如果有重复项，则会抛出错误。捕获错误并按照您的意愿处理它。计算记录数绝对是错误的方法，并且会严重损害您的代码速度。
回答by Homan
You can still run into race conditions where 2 users try to insert dups at the same time, checking for dups using a select statement simultaneously gives both users the go ahead to insert their records. I prefer to set unique index on the DB and then catch the error that bubbles up from the DB.
您仍然可能遇到竞争条件，其中 2 个用户尝试同时插入 dup，同时使用 select 语句检查 dups 使两个用户都可以继续插入他们的记录。我更喜欢在 DB 上设置唯一索引，然后捕获从 DB 冒出的错误。
回答by Johan
You don't need to check for uniqueness in PHP, you can just do the whole test in MySQL:
你不需要在 PHP 中检查唯一性，你可以在 MySQL 中完成整个测试：
INSERT INTO table1 (name, description, ......)
SELECT name1, description1, manufacturer1, city1, price1, enddate1
FROM (SELECT * FROM (
SELECT
@name:= '$name' as name1
,@description:= '$description' as description1
,.....
FROM DUAL as d1
LEFT JOIN table1 t1
ON (t1.name = d1.name1
AND t1.description = d1.description1
AND ......)
WHERE t1.name IS NULL) s1) s2
This will only insert the values if they pass the uniqueness test.
如果它们通过唯一性测试，这只会插入值。
回答by EKY
In short you need to do your checking through PHP and you also need to add a composite unique constraint in your MySQL table to get the best of both worlds.
简而言之，您需要通过 PHP 进行检查，并且您还需要在 MySQL 表中添加一个复合唯一约束，以获得两全其美的效果。
it would be good if we broke down your question into two separate questions.
如果我们将您的问题分解为两个单独的问题，那就太好了。
1- How do I check duplicates using PHP and notify about the user before executing query? 2- How do I specify a composite unique constraint in MySQL?
1- 如何在执行查询之前使用 PHP 检查重复项并通知用户？2- 如何在 MySQL 中指定复合唯一约束？
First, you need a simple PHP function to check whether this record exist in the DB or not, like the one below:
首先，您需要一个简单的 PHP 函数来检查该记录是否存在于数据库中，如下所示：
function is_exist($table, $data){
$sql = "SELECT * FROM `" . $table . "` WHERE ";
foreach ($data as $key => $val) :
$sql .= "`$key`='" . $val . "' AND ";
endforeach;
$sql = substr($sql,0, -5);
$result = $mysql_query($sql);
$count = $mysql_num_rows($result);
return ($count > 0) ? true: false;
}
You should call your PHP function like this way below:
你应该像下面这样调用你的 PHP 函数：
$data = array('column1'=>$_POST['value'],'column2'=>$_POST['value'], ...);
if(is_exist($data)){
// Print your error message
}else{
// Run your insert query
}
This way you can prevent duplicates before going to the MySql Database, but in order to have a duplicate free database, you need to add a composite unique constraint in your MySQL table.
这样你可以在进入MySql数据库之前防止重复，但是为了有一个无重复的数据库，你需要在你的MySQL表中添加一个复合唯一约束。
This simple SQL command can do the trick:
这个简单的 SQL 命令可以解决问题：
alter table `tablename` add unique index(name, description, manufacturer, city, price, enddate);
I hope that helps, if I mess anything please burden me.
我希望这有帮助，如果我搞砸了任何事情，请给我带来负担。
回答by Dumbdelidumbdumb
Try Following
尝试关注
INSERT IGNORE INTO person_tbl (last_name, first_name)
-> VALUES( 'Jay', 'Thomas');
回答by Puzo
Try this one:
试试这个：
foreach($states_to_add as $item) {
$dupesql = "SELECT
name
FROM
table
WHERE
(name = '$name'
AND description = '$description'
AND manufacturer = '$manufacturer'
AND city ='$city'
AND price = '$price'
AND enddate = '$end_date'
)";
$duperaw = mysql_query($dupesql);
if( mysql_num_rows($duperaw) ) {
echo nl2br("$name already exists in $city \n");
}
else {
$sql = "INSERT INTO table (..... (here go the values to be inserted)
