[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1811
  • Last Modified:

How to let php-script continue after MySQL-error

I've got a small website made with php and using MySQL for storing information. In the database I've made some relationships between some of the tables.

However, when I try to for instance 'delete from user where userid=2' and userid 2 is used elsewhere, I'm unable to catch the error and give a meaningful error message to the user. What I get is:
ERROR: unable to execute query 'delete from User where UserId='2'' and the php-script is halted.

I've tried going through php.ini, setting error_reporting(0) and
  try{
    $result = SQLQuery($query);
    return "User deleted successfully";
  }catch(Exception $e){
    return "Some error message";
  }

I could of course check that the userid is not in use before I try to delete but that defeats some of the purpose of the foreign key contraint.
0
johnnybaluba
Asked:
johnnybaluba
1 Solution
 
hernst42Commented:
> ERROR: unable to execute query 'delete from User where UserId='2'' and the php-script is halted.
This message is generated by your application, not php. Seems that the Database layer you are using stops if a SQL-errors occurs and does not throw an execption. You need to change those things in your use database layer, so you can catch that error and give proper error messages.
0
 
Aleksandar BradarićSoftware DeveloperCommented:
Here's what I get when trying to delete a row with a RESTRICT constraint - the execution did not stop and the error seems friendly enough:
---
BEFORE!
Cannot delete or update a parent row: a foreign key constraint fails
AFTER!
---

Here's the code which produced it:
---
<?php

  function sql_exec($aSQL, $return_affected = 0, $return_insert_id = 1) {
    $my_server   = 'localhost';
    $my_username = 'my_user';
    $my_password = 'my_pass';
    $my_database = 'test';

    $aID = false;
    if($aSQL != '') {
      # connect to database...
      $data = mysql_connect($my_server, $my_username, $my_password);
      if(!empty($data)) {
        if(mysql_select_db($my_database)) {
          mysql_query('SET NAMES utf8');
          $theSQL = $aSQL;
          $query = mysql_query($theSQL);
          if($query) {
            if($return_affected)
              $aID = mysql_affected_rows();
            else if($return_insert_id)
              $aID = mysql_insert_id();
            else
              $aID = true;
          }
          else {
            print mysql_error($data) . " <BR>\n";
          }
        }
        mysql_close($data);
      }
    }

    return $aID;
  }

  $aSQL = "DELETE FROM myOne WHERE id = 1 ";
  print "BEFORE!<BR>\n";
  sql_exec($aSQL);
  print "AFTER!<BR>\n";

?>
---
0
 
v2MediaCommented:
Precede your mysql functions (or any functions for that matter) with an @ symbol to supress errors.

$query = @mysql_query($theSQL);

if($query) {

//proceed with result processing

} else {

//output an alternative

}
0
 
AlexanderRCommented:
v2Media got a good one.
Errors should also be caught like hernst42 said.  In php i believe it can be done as in http://php.net/exceptions
Thats a propper way of dealing with errors across all languages.
0
 
johnnybalubaAuthor Commented:

:"> I feel really stupid now. I've made my own DB layer and for some obscure reason I'd put this inside the SQLQuery function:
if (!$res)
{
    echo("<B>ERROR:</b> unable to execute query '$query'");
   closeDB();
   exit;
}

Why I didn't check there first is beyond me. Anyway, thanks for the tips.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now