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 — Ben
– Ben, Commented Sep 23, 2015 at 12:19

Jason McCreary · Accepted Answer · 2011-07-10 16:26:26Z

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, 2011 at 16:26

Jason McCreary

73.3k23 gold badges140 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

OMG Ponies Over a year ago

+1: The fewer trips between the database & the application, the better.

CyberJunkie Over a year ago

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?

Jason McCreary Over a year ago

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.

Muhammad bin Yusrat Over a year ago

Where is the answer by Manocho?

Noah Duncan · Accepted Answer · 2011-07-10 16:26:56Z

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, 2011 at 16:26

Noah Duncan

5101 gold badge5 silver badges18 bronze badges

2 Comments

Noah Duncan Over a year ago

@Jason 's response is actually more efficient than mine, though both would work.

OMG Ponies Over a year ago

The fewer trips between the database & the application, the better.

sdolgy · Accepted Answer · 2011-07-10 16:27:30Z

1

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.

answered Jul 10, 2011 at 16:27

sdolgy

7,0013 gold badges43 silver badges61 bronze badges

Comments

Unheilig · Accepted Answer · 2015-09-25 04:02:31Z

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, 2015 at 4:02

Unheilig

16.3k193 gold badges71 silver badges101 bronze badges

answered Jan 17, 2013 at 5:59

Toihid

1

Comments

Hassan Saeed · Accepted Answer · 2017-08-13 15:40:23Z

0

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

answered Aug 13, 2017 at 15:40

Hassan Saeed

7,1701 gold badge43 silver badges38 bronze badges

Collectives™ on Stack Overflow

Display error message in php for MYSQL duplicate error

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related