Handle database exceptions with php

Question

This exception(error) is displayed when i try to delete a user.

Error: the XML response that was returned from the server is invalid.
Received:
Cannot delete or update a parent row: a foreign key constraint fails                         
(`db_almacen`.`perfil_solicitud_material`, CONSTRAINT `perfil_solicitud_material_ibfk_1`
FOREIGN  KEY (`id_usuario`) REFERENCES `usuario` (`id_usuario`))

It is displayed on that way because i have a FOREIGN KEY related to my user's id. I would like to display a different message instead of that. How can i do this i checked php exceptions manual but i didn't find an example please help me. I am using Mysql and PHP My php code

$query = "DELETE FROM unidad_solicitante where id_unid_sol ='$cod';";
$result = mysql_query($query) or die(mysql_error());

Thank You

Just a note that mysql_query is deprecated now and it's recommended you use MySQLi or PDO. — Andrew, Sep 27 '13 at 19:54
You either need to set the [`ON DELETE`](http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html) action for your foreign key constraint, or you need to clean up the child records of the FK relationship before deleting the parent key. — Sammitch, Sep 27 '13 at 19:56
The `mysql_*` functions are **no longer maintained** and shouldn't be used in any new codebase. It is being phased out in favor of newer APIs. Instead you should use [**prepared statements**](https://www.youtube.com/watch?v=nLinqtCfhKY) with either [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli). — tereško, Sep 27 '13 at 19:57

score 1 · Answer 1 · edited May 23 '17 at 10:32

Do not use mysql_ functions: http://pl1.php.net/mysql_query

See the red box? They are deprecated, not safe, and in general it is a bad idea to use them.

If you want PHP code to throw actual Exception, then use PDO; If you want to produce safe code, then use PDO; If you don't want dark demons (also applies to script kiddies) of SQL injection to get your site down, use... you know what :) mysql_ functions are wrong, mysql_ functions are evil, mysql_ functions are waking up dark forces , mysql_ functions will steal the ice cream from your child, mysql_ functions are voting in senate, mysql_ functions aren't feeding their dog; mysql_ functions will haunt you at night; mysql_ functions are selling drugs on the streets;

This fragment of your query code: ='$cod'; seems vulnerable, so please read this: How can I prevent SQL injection in PHP?

With PDO, you can handle any exception with the usual try{} catch(...){} block;

If you cannot use PDO or really really do not want to, you should check if mysql_query() returned true, if not, then your error message will be returned by mysql_error(), so it will be something like this:

$result = mysql_query(...);
if(!$result) {
    $error = mysql_error();
    // .. handle
}

score 0 · Answer 2 · answered Sep 27 '13 at 19:57

Any mysql error is going to trigger the or die(...), because mysql_query() will return a plain boolean FALSE to signal the failure. If you want to exempt certain error conditions from the die(), you'll have to test for them explicitly:

$result = mysql_query(...);
if ($result === FALSE) {
    switch(mysql_errno()) {
      case 1216: ... foreign key error ...
      case xxx: ...
      default:
            die(mysql_error());
    }
}

The error codes are defined here: http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html

score 0 · Answer 3 · answered Sep 27 '13 at 20:01

The code responsible for displaying database error is here:

die(mysql_error())

die() function terminates the script and displays a message
mysql_error() outputs last error connected with executing MySQL query

If you want to display your own message use the following code:

if(mysql_query($query)) {
    echo 'success';
}
else {
    echo 'error';
}

Handle database exceptions with php

3 Answers3