How to handle error for duplicate entries?
I have a PHP form that enters data into my MySQL database. My primary key is one of the user-entered values. When the user enters a value that already exists in the table, the MySQL error «Duplicate entry ‘entered value’ for key 1» is returned. Instead of that error, I would like to alert the user that they need to enter a different value. Just an echoed message or something. How to turn a specific MySQL error into a PHP message?
6 Answers 6
To check for this specific error, you need to find the error code. It is 1062 for duplicate key. Then use the result from errno() to compare with:
mysqli_query('INSERT INTO . '); if (mysqli_errno() == 1062) A note on programming style
You should always seek to avoid the use of magic numbers (I know, I was the one to introduce it in this answer). Instead, you could assign the known error code ( 1062 ) to a constant (e.g. MYSQLI_CODE_DUPLICATE_KEY ). This will make your code easier to maintain as the condition in the if statement is still readable in a few months when the meaning of 1062 has faded from memory 🙂
Yeah, I just found that too 😀 I plugged it in and it worked perfectly. Thanks jensgram, just the solution I was looking for!
@jensgram: +1 thanks for very useful answer & tip. I’m just adding this link for those using PDO: php.net/manual/en/pdo.errorcode.php
Why do you spend a while paragraph on instructing the reader to use constants, instead of just (or at least in addition to) applying it to your code snippet?
You can check the return value from mysql_query when you do the insert.
$result = mysql_query("INSERT INTO mytable VALUES ('dupe')"); if (!$result) < echo "Enter a different value"; >else Yep — mysql_errno() will return the error code of the last error. Off the top of my head, I don’t know what that code is, but you could just check for that inside the (!$result) branch.
try this code to handle duplicate entries and show echo message:
$query = "INSERT INTO ".$table_name." ".$insertdata; if(mysqli_query($conn,$query))< echo "data inserted into DB
"; >else< if(mysqli_errno($conn) == 1062) echo "duplicate entry no need to insert into DB
"; else echo "db insertion error:".$query."
"; >//else end Use mysql_errno() function, it returns the error numbers. The error number for duplicate keys is 1062. for example
$query = mysql_query("INSERT INTO table_name SET . ); if (mysql_errno() == 1062) This is my full code that I used and works perfect. Its PDO friendly, and can handle your error easily, (once you have used die to discover what that is. Then you can copy the error message from there, and enclose it in an if. This came from a signup page, where I wanted to redirect to the login page, if the primary key (email) was found, and produced an error.
function insertUserDetails($email, $conn)< try < $query = $conn->prepare ("INSERT INTO users (emailaddress) VALUES (:email)"); $query ->bindValue('email', $email); $query->execute(); > catch (PDOException $e) < if(str_contains($e, '1062 Duplicate entry')) < header("Location: login.php"); >die("Error inserting user details into database: " . $e->getMessage()); > > This question is in a collective: a subcommunity defined by tags with relevant content and experts.
MySQL — ignore insert error: duplicate entry
I am working in PHP. Please what’s the proper way of inserting new records into the DB, which has unique field. I am inserting lot of records in a batch and I just want the new ones to be inserted and I don’t want any error for the duplicate entry. Is there only way to first make a SELECT and to see if the entry is already there before the INSERT — and to INSERT only when SELECT returns no records? I hope not. I would like to somehow tell MySQL to ignore these inserts without any error. Thank you
insert ignore works for me The problem am having some rows are deleted from table and am trying to insert the rows same id which were deleted previously in that case «insert ignore» statement worked
5 Answers 5
You can use INSERT. IGNORE syntax if you want to take no action when there’s a duplicate record.
You can use REPLACE INTO syntax if you want to overwrite an old record with a new one with the same key.
Or, you can use INSERT. ON DUPLICATE KEY UPDATE syntax if you want to perform an update to the record instead when you encounter a duplicate.
Edit: Thought I’d add some examples.
Examples
Say you have a table named tbl with two columns, id and value . There is one entry, and value=1. If you run the following statements:
REPLACE INTO tbl VALUES(1,50); You still have one record, with value=50. Note that the whole record was DELETED first however, and then re-inserted. Then:
INSERT IGNORE INTO tbl VALUES (1,10); The operation executes successfully, but nothing is inserted. You still have and value=50. Finally:
INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200; You now have a single record with and value=200.
«If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead.» -from the above insert link
Warnings do not raise an error however, so your script will continue to execute. From the manual: If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row still is not inserted, but no error is issued.
IGNORE will ignore errors that may have nothing to do with duplicate keys. It’s better to do ON DUPLICATE KEY UPDATE field=field so you can catch those errors and as a bonus, not have any warnings.
You can make sure that you do not insert duplicate information by using the EXISTS condition.
For example, if you had a table named clients with a primary key of client_id, you could use the following statement:
INSERT INTO clients (client_id, client_name, client_type) SELECT supplier_id, supplier_name, 'advertising' FROM suppliers WHERE not exists (select * from clients where clients.client_id = suppliers.supplier_id); This statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following statement:
INSERT INTO clients (client_id, client_name, client_type) SELECT 10345, 'IBM', 'advertising' FROM dual WHERE not exists (select * from clients where clients.client_id = 10345); The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.
Error: Duplicate entry ‘0’ for key ‘PRIMARY’
I can’t resolve my problem, this is the error from mysql that I’m getting: 
I can edit and update my data when I’ve got one record in the database but when I add two rows, I get the error. Some pictures from database And when I change the row, row ID goes down to 0 and that’s is a problem as I can’t edit other rows. 
CREATE TABLE `dati` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `value1` varchar(255) NOT NULL, `value2` varchar(255) NOT NULL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 PACK_KEYS=1 $sql="UPDATE dati SET ,title= '$titletxt',value1='$value1',value2='$value2' WHERE 1"; if (!mysqli_query($con,$sql)) < die('Error: ' . mysqli_error($con)); >echo ' '; mysqli_close($con); > ?> $result = mysqli_query($con,"SELECT * FROM dati"); while($row = mysqli_fetch_array($result)) < echo ""; echo " "; echo " "; echo " "; echo ""; echo " "; > mysqli_close($con); ?> INSERT results in DUPLICATE ENTRY ‘0’ FOR KEY PRIMARY
I am using a simple php script to insert data into database but it’s failing. The query just doesn’t become successful without showing a single error which is why I am unable to figure out the problem. Some expert here help me please.
echo $name." ".$email." ".$pass." ".$phone." ".$area." ".$specialization." ".$city." ".$latitude." ".$longitude; $query = mysqli_query($conn, "INSERT INTO users (name, email, pass, phone, area, specialization, hospital, city, latitude, longitude) VALUES ('$name', '$email', '$pass', '$phone', '$area', '$specialization', '$hospital', '$city', '$latitude', '$longitude') "); if ($query) < echo "Status: Registeration Successful!"; // creating directory for user and storing dummy profile picture //mkdir('../profiles/'.$email_trim, 0777); //$result_copy = copy("img/dp.jpg.jpg", "../profiles/".$email_trim."/dp.jpg.jpg"); >else This «Status: Err» is always printed. I don’t know why. P.S I have double checked the database the field labels are fine. UPDATE 1: I added the
statement and it says «DUPLICATE ENTRY ‘0’ FOR KEY PRIMARY’. PROBLEM AND SOLUTION: The issue was that I had an ‘id’ field which was primary key of the table but it was not set to AUTO_INCREMENT. So, whenever I tried to insert a new record, I was actually inserting entries with duplicate PKs which was the issue. I change it to AUTO_INCREMENT and it solved the problem.