Display error message in php for MYSQL duplicate error

Question

I am using UNIQUE Constraint in my table for two columns to prevent duplicate rows. If I try to insert a row with the same 2 columns I get

A Database Error Occurred Error Number: 1062

Duplicate entry '62-88' for key 'subscription'

INSERT INTO subscriptions (user_id, subscribed_to, date) VALUES ('62', '88', '2011-07-11 19:15:13')

Filename: C:\wamp\www\mysite\system\database\DB_driver.php

Line Number: 330

How can I return a php error, e..g Already subscribed! instead of displaying the mysql error?

possible duplicate of [Handle error for duplicate entries - PHP MySQL](http://stackoverflow.com/questions/3146838/handle-error-for-duplicate-entries-php-mysql) — Ben, Sep 23 '15 at 12:19

score 5 · Accepted Answer · answered Jul 10 '11 at 16:26

5

Call mysql_errno() after your mysql_query() and check for 1062.

Note: It's a more common/intuitive solution to query the database first. See answer by Manocho.

answered Jul 10 '11 at 16:26

Jason McCreary

71,546
23
135
174

1

+1: The fewer trips between the database & the application, the better. – OMG Ponies Jul 10 '11 at 16:47
Thanks for the suggestion! I'm using an MVC pattern. If I understood correctly, I add `mysql_errno()` after my `insert ` query (in my model) and that should return `1062`? Then I would use `if ($query == 1062)` display error? – CyberJunkie Jul 10 '11 at 17:52
Yes. You could put it in your Model. But I'd argue this is business logic and it may be better served in your Controller. Note, check if `mysql_errno()` returns `1062`, not the query. – Jason McCreary Jul 10 '11 at 18:03
Where is the answer by Manocho? – Muhammad bin Yusrat Nov 17 '16 at 12:47

score 5 · Answer 2 · answered Jul 10 '11 at 16:26

5

I think you will need to run a query manually to check if the entry already exists in your database. so something like: SELECT COUNT(*) FROM subscriptions WHERE (user_id = '62' AND subscribed_to = '88'). If the count returned is > 0 then return the error, however you want it displayed.

answered Jul 10 '11 at 16:26

Noah Duncan

500
1
5
18

@Jason 's response is actually more efficient than mine, though both would work. – Noah Duncan Jul 10 '11 at 16:28
The fewer trips between the database & the application, the better. – OMG Ponies Jul 10 '11 at 16:47

score 1 · Answer 3 · answered Jul 10 '11 at 16:27

http://php.net/manual/en/function.mysql-error.php I assume you are able to programatically obtain the MySQL error
http://www.php.net/manual/en/function.mysql-errno.php
http://dev.mysql.com/doc/refman/5.5/en/error-handling.html

Using the 3rd link, compare the mysql error or mysql errno to the list of errors and if the condition is met, provide your alternate error message.

score 0 · Answer 4 · edited Sep 25 '15 at 04:02

0

To solve the error you can do this:

mysql_query($sql);

if (mysql_errno() == 1062) {       
  print "<script type=\"text/javascript\">"; 
  print "alert('The informations are already inserted')"; 
  print "</script>";
}

edited Sep 25 '15 at 04:02

Unheilig

16,196
193
68
98

answered Jan 17 '13 at 05:59

Toihid

1

score 0 · Answer 5 · answered Aug 13 '17 at 15:40

try this code :

  $query = "INSERT INTO ".$table_name." ".$insertdata;
                if(mysqli_query($conn,$query)){
                    echo "data inserted into DB<br>";                   
                }else{
                   if(mysqli_errno($conn) == 1062)
                       echo "duplicate entry no need to insert into DB<br>";
                   else
                    echo "db insertion error:".$query."<br>";

                }//else end

Display error message in php for MYSQL duplicate error

5 Answers5