CODEWITHSUNDEEP
phpmysqldatabaseduplicates
Drew
Drew

Reputation: 21

Check for duplicate when editing a record in 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.

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.

Upvotes: 2

Views: 2958

Answers (5)

Drew
Drew

Reputation: 21

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

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

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

if( mysql_errno() == '1062' )

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

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

}

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

Thank you to everyone who took the time to offer help

Upvotes: 0

doniyor
doniyor

Reputation: 37876

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

Upvotes: 0

Dipen
Dipen

Reputation: 240

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

  1. First you need an primary key(auto_increment) in "ID" field
  2. 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.
  3. use primary key filed for update, delete etc...

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

Upvotes: 1

Neeraj
Neeraj

Reputation: 9155

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

$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.

Upvotes: 1

mavili
mavili

Reputation: 3424

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.

Upvotes: 0

Related Questions