Capture mysql database error and use it in php

Question

I created the following table for user to user subscriptions.

CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `subscribed_to` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_subscription` (`user_id`,`subscribed_to`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=76 ;

I'm disallowing identical rows by making the columns user_id and subscribed_to unique. If a user tries to resubmit the same data I get:

A Database Error Occurred Error Number: 1062

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

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

Line Number: 330

I'm preventing the database error by checking if an identical row exists before trying to insert data.

$query = "SELECT COUNT(*) FROM subscriptions WHERE (user_id = '62' AND subscribed_to = '88')";

if ($query > 0)
{
    //display already subscribed message
}
else
{
    //insert new row
}

The database already checks the table and returns an error. The select count(*) query above seems redundant. Do I really need to check the table once more in my application? Is there a way to capture the database error if it occurs, and do something with that in my application?

If you have an idea how please share an example. I haven't a clue how this is done..!

Mridul Kashatria · Accepted Answer · 2011-07-12 17:41:34Z

1

Check out PHP function mysql_error

You can wrap the db call within a try catch statement for functions that throw exceptions to prevent your application from crashing.

PHP function mysql_query does not throw exception on error, but returns FALSE. You can check the return value and execute mysql_error to find out the trouble or log it.

edited Jul 12, 2011 at 17:41

answered Jul 12, 2011 at 17:34

Mridul Kashatria

4,1972 gold badges21 silver badges15 bronze badges

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

4 Comments

CyberJunkie Over a year ago

Thanks for the answer! Can you please provide an example? I'm not sure how this is done

Mridul Kashatria Over a year ago

which PHP function are you using to run the queries? is it mysql_query?

CyberJunkie Over a year ago

I'm using activerecord via the codeigniter framework which I believe uses mysql_query

Mridul Kashatria Over a year ago

$this->db->_error_message(); will give you the error message using codeigniter. Check out this forum thread for more.

Collectives™ on Stack Overflow

Capture mysql database error and use it in php

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related