PHP MySQLi connection and query error handling. How?

Question

The following PHP code works. I just don't seem to be able to handle its errors in a custom way.

For example, when I intentionally misspell anything in the connection string to return code "3" for database down, my AJAX script just hangs in beforeSend forever...

This is what I got:

<?php  

  if(isset($_POST["postT_VAL"])) {

  $client_id    = $_POST["postCLIENT_ID"];
  $project_id   = $_POST["postPROJECT_ID"];
  $mainsheet_id = $_POST["postMAINSHEET_ID"];
  $field_name = $_POST["postT_ID"];
  $field_value = $_POST["postT_VAL"];

  $link = mysqli_connect("database.domain.com", "username1", "password1", "db220474");

  if (!$link) {

  /* return 3 = database offline */
  echo "3";

  } else {

  /* build query */
  $sql = "UPDATE tbl_mainsheet2 SET ".$field_name." = '".$field_value."' WHERE client_id = '".$client_id."' AND project_id = '".$project_id."' AND mainsheet_id = '".$mainsheet_id."'";  

  /* execute query */    
  mysqli_query($link, $sql);

  /* return 0 = no update / 1 = successful update */
  echo "".mysqli_affected_rows($link);

  /* close connection */
  mysqli_close($link);

 }

 }

?>

New Research

Alright. After some research I have found that this works. It appears that you need to tell mysqli to throw exceptions.. For some reason this is different than just trying to handle an error the "IF" way. Any suggestions for improvement?

mysqli_report(MYSQLI_REPORT_STRICT);

      try {
           $link = mysqli_connect("database.domain.com", "username1", "password1", "db220474");
      } catch (Exception $e ) {
           echo "3";
           exit;
      }

Code Update

Here's is the final tested and working PHP solution for all to see.

<?php  

  /* Status Codes

     return 0 = Nothing to Update
     return 1 = Successful Update Query
     return 2 = Database Connection refused
     return 3 = MySQL Query Error OR Wrong URL Parameters */

  mysqli_report(MYSQLI_REPORT_OFF);

  if(isset($_GET["postT_VAL"])) {

  $client_id    = $_GET["postCLIENT_ID"];
  $project_id   = $_GET["postPROJECT_ID"];
  $mainsheet_id = $_GET["postMAINSHEET_ID"];
  $field_name = $_GET["postT_ID"];
  $field_value = $_GET["postT_VAL"];

  try {
       $link = mysqli_connect("domain", "username", "password", "database");
  } catch (Exception $e) {
       // echo "".$e->getCode();
       /* return 2 = Database Connection refused */
       echo "2";

       exit;
  }

  /* Build dynamic Update Query string */
  $sql = "UPDATE tbl_mainsheet2 SET ".$field_name." = '".$field_value."' WHERE client_id = '".$client_id."' AND project_id = '".$project_id."' AND mainsheet_id = '".$mainsheet_id."'";  

  /* Execute Update Query */    
  if(!mysqli_query($link, $sql)) {

  echo "3";
  /* Close Connection */
  mysqli_close($link);

  exit;

  } else {

  /* return 0 = Nothing to Update / 1 = Successful Update Query */
  echo "".mysqli_affected_rows($link);

  /* Close Connection */
  mysqli_close($link);

  }

 }

?>

Use `mysqli_error($link)` to reveal the error. Also, your codes are vulnerable to SQL Injection. Please review & rewrite. — Raptor, Aug 03 '17 at 03:15
You might get an "Ahha" moment if you just run the code inside your if... ie copy that section into a new file - or comment out the existing if and run the page directly... — TimBrownlaw, Aug 03 '17 at 04:01
After `echo "3"` you need to add `exit()` or `die($message)` to get the error message and to prevent executing next line of code if there's an error in your connection. — Jrey, Aug 03 '17 at 04:17
Does this answer your question? [Should we ever check for mysqli\_connect() errors manually?](https://stackoverflow.com/questions/58808332/should-we-ever-check-for-mysqli-connect-errors-manually) — Dharman, Jun 23 '20 at 23:56

suchislife · Answer 1 · 2017-08-03T12:30:07.350

Here's is the final tested and working PHP solution for all to see.

<?php  

  /* Status Codes

     return 0 = Nothing to Update
     return 1 = Successful Update Query
     return 2 = Database Connection refused
     return 3 = MySQL Query Error OR Wrong URL Parameters */

  mysqli_report(MYSQLI_REPORT_STRICT);

  if(isset($_GET["postT_VAL"])) {

  $client_id    = $_GET["postCLIENT_ID"];
  $project_id   = $_GET["postPROJECT_ID"];
  $mainsheet_id = $_GET["postMAINSHEET_ID"];
  $field_name = $_GET["postT_ID"];
  $field_value = $_GET["postT_VAL"];

  try {
       $link = mysqli_connect("domain", "username", "password", "database");
  } catch (Exception $e) {
       // echo "".$e->getCode();
       /* return 2 = Database Connection refused */
       echo "2";

       exit;
  }

  /* Build dynamic Update Query string */
  $sql = "UPDATE tbl_mainsheet2 SET ".$field_name." = '".$field_value."' WHERE client_id = '".$client_id."' AND project_id = '".$project_id."' AND mainsheet_id = '".$mainsheet_id."'";  

  /* Execute Update Query */    
  if(!mysqli_query($link, $sql)) {

  echo "3";
  /* Close Connection */
  mysqli_close($link);

  exit;

  } else {

  /* return 0 = Nothing to Update / 1 = Successful Update Query */
  echo "".mysqli_affected_rows($link);

  /* Close Connection */
  mysqli_close($link);

  }

 }

?>

I haven't been able to tie any answers on here with the solution I have found other than the error handling for the query.. — suchislife, Aug 03 '17 at 12:13

score -1 · Answer 2 · answered Aug 03 '17 at 03:36

-1

Try this while Executing Query

 /* execute query */    
  mysqli_query($link, $sql) or die(mysqli_error($link));

answered Aug 03 '17 at 03:36

Prince Arora

354
1
6
20

score -1 · Answer 3 · answered Aug 03 '17 at 03:51

enter code here
<?php
$con=mysqli_connect("localhost","my_user","my_password","my_db");

// Check connection
if (mysqli_connect_errno())
 {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
 }

 // Perform a query, check for error
if (!mysqli_query($con,"INSERT INTO Persons (FirstName) VALUES ('Glenn')"))
{
echo("Error description: " . mysqli_error($con));
}
 mysqli_close($con);
?>

try this good luck

score -1 · Answer 4 · answered Aug 03 '17 at 12:26

-1

You can do like this:

try {
   $link = mysqli_connect("domain", "username", "password","database");
 } catch (Exception $e) {
       echo $e;
   exit;
 }

answered Aug 03 '17 at 12:26

TheCleverIdiot

412
4
19

PHP MySQLi connection and query error handling. How?

4 Answers4