在MySQL中编辑记录时检查重复

I'm having problems with a piece of code and wonder if someone can help.

我遇到一段代码问题,并想知道是否有人可以提供帮助。

I have a form that submits information to a MySQL database, I have the correct code for checking to see if the submitted product code already exists, and if so shows a warning message and the record is not added.

我有一个向MySQL数据库提交信息的表单,我有正确的代码用于检查提交的产品代码是否已经存在,如果是,则显示警告消息并且未添加记录。

That code is:

该代码是:

$result = mysql_query("SELECT * FROM listing_1 WHERE product_code='$product_code'");
$num_rows = mysql_num_rows($result);
if ($num_rows) {
   adminwarnmessage("DUPLICATE REFERENCE CODE","FAILURE - <b>$product_name</b> has <b>NOT</b> been added because the reference number already exists.");
}

That works fine for Data Entry, however I have another form that allows users to edit the record, this is what is causing me a problem, as the above code only tells me that there is already a matching record in the database, Of course when I try to save (update) the record it now tells me I can't because it is a duplicate.

这适用于数据输入,但是我有另一种形式允许用户编辑记录,这是导致我出现问题的原因,因为上面的代码只告诉我数据库中已有匹配的记录,当然我尝试保存(更新)它现在告诉我我不能的记录,因为它是重复的。

What I would like to happen is that it doesn't allow users to choose another productcode that already exists, but I want them to be able to update the record using the same product code the form fetched from the database.

我想要发生的是它不允许用户选择已经存在的另一个产品代码,但我希望他们能够使用从数据库中获取的表单相同的产品代码来更新记录。

Hope that makes sense, any help greatly appreciated.

希望有意义,任何帮助都非常感激。

5 个解决方案

#1

If you have id (primary key) then You will have to compare with id of that product before updating the record. For example

如果您有id(主键),那么在更新记录之前,您必须与该产品的ID进行比较。例如

$result = mysql_query("SELECT * FROM listing_1 WHERE product_code='$product_code' AND id!=$id"); 
$num_rows = mysql_num_rows($result); 
if ($num_rows) { 
    echo "duplicate record";
}

Here $id is the id of the product that you should have while editing the record.

这里$ id是编辑记录时应该具有的产品的ID。

#2

following is the step you need to follow when you managing the Database

以下是管理数据库时需要遵循的步骤

First you need an primary key(auto_increment) in "ID" field

首先,您需要在“ID”字段中输入主键(auto_increment)

When you execute insert query that time first check where record is already available or not. if not available than only you should execute insert query.

当您执行插入查询时,首先检查记录是否已经可用。如果不可用,则只应执行插入查询。

use primary key filed for update, delete etc...

使用主键提交更新,删除等...

if you follow the above step than you never face this problem

如果你按照上面的步骤,你从来没有遇到过这个问题

#3

Are you perhaps checking for duplicate occurrences in both of insert and update statement? If so, you shouldn't. Duplicate entry is relevant only when "inserting". You shouldn't use the same check for update. Hope that helps.

您是否可能在insert和update语句中检查重复的次数?如果是这样,你不应该。重复输入仅在“插入”时相关。您不应该使用相同的检查进行更新。希望有所帮助。

#4

why the same code for update also? you can use another query for updating which is better for debugging if you have problems later. try this

为什么同样的代码更新呢?您可以使用另一个查询进行更新,如果以后遇到问题,可以更好地进行调试。尝试这个

$result = mysql_query("UPDATE listing_1 SET product_code='$new_product_code' WHERE product_code='$product_code' AND id='$id'");
if($result) {
   echo "your product was updated.";
} else {
   echo "your product is not in DB";
}

EDIT: be careful in updating or inserting things, take always id to check unless your product_code is unique

编辑:小心更新或插入内容,除非您的product_code是唯一的,否则请始终使用id进行检查

#5

EDIT I have resolved this issue by making $productcode field a unique Index. Now when editing if there is a duplicate..

编辑我已经通过使$ productcode字段成为唯一索引解决了这个问题。现在编辑时是否有重复..

Mysql does not accept the update query, it returns an error code

Mysql不接受更新查询,它返回错误代码

I trap that error code and include it in an if statement...

我捕获了该错误代码并将其包含在if语句中...

if( mysql_errno() == '1062' )

if(mysql_errno()=='1062')

adminwarnmessage("DUPLICATE REFERENCE CODE","FAILURE - $product_name has NOT been added because the reference number already exists");

adminwarnmessage(“DUPLICATE REFERENCE CODE”,“FAILURE - $ product_name尚未添加,因为参考编号已存在”);

} adminmessage("Item Updated", Congratulations you updated $productname succesfully");

} adminmessage(“项目已更新”,恭喜您成功更新了$ productname“);

}

This now allows editing of $productcode but does not allow it to be changed to one already used in the database.

现在允许编辑$ productcode,但不允许将其更改为已在数据库中使用的产品代码。

Thank you to everyone who took the time to offer help

感谢所有花时间提供帮助的人

#1