mysql_query() or die()

[blackhorse]

On a fully tested system, the sql statements are all without errors.

1) We still should use the

$result=$mysql_query(...) or die(mysql_error());

instead of just

$result=$mysql_query(...);

Because, the database connection problems (not total failure) could still fail some queries and let some queries running. Which you don't want to see. So we still have to exit, even if only one query failure. Right?

2) Should I use

$result=$mysql_query(...) or die("database connection failure, inform the admin.");

instead of use

$result=$mysql_query(...) or die(mysql_error());

instead of show the mysql_error in the pages for the end users. On a fully tested system or at any systems, there are no reasons at all to show the end users the mysql_error(), mysql_error() should be only for the developers used on the system under developing, right?

Thanks!

[Protato]

Correct, in a public environment you do not want to reveal anything that can potentially be exploited to the users.

[NogDog]

For a "live" site, you probably do not want to be die()-ing, and you definitely do want uncontrolled mysql_error() statements being displayed. You probably want to do something more along the lines of:

$result = mysql_query($sql);
if(!$result)
{
   error_log("Query error ($sql): " . mysql_error());
   echo "<p class='error'>Sorry, there was a database error . . . blah, blah, blah</p>";
   echo "</body></html>";
   exit;
}
// rest of code

Note that by using the [man]set_error_handler/man function, you could create a custom error-handling function, and then by setting some sort of "debug" constant in your script and referencing it in your error-handling function, choose different error reporting options (error_log() vs. die(), for example) based on that constant's setting.

[blackhorse]

Thanks!

I already used the error handling block approach you give in some crucial queries. But I don't know if it is necessary to add this below all the queries.

NogDog, for a tested live system. The problems I run into are usually due to the database connection. It is rare already. And in these incidents, most of the time, the whole database connections were down.

Only very rare cases, that some query's database connections are open and some are down. That is the reason I want to add the die() to all queries. Just to prevent that in the cases that some database connections are still running, some are not. That may cause some troubles. For example, a shopper is doing the online shopping, the product catalog, his account information are all working. But the query to get the countries information from the countries table is down. And the shopper may continue to send out his order, everything is fine. but missing the country because the countries query failed due to the database connection problem. For that reason, I am thinking about add the die() to all the queries if it is not with the error handling block. That could be done quickly rather than add full error handling block below each query.

Do we always have to add the block you gave below every query we do? or in my case, die () will be fine for most queries, only the error handling block for some crucial sql?

Or do we always have to add die() or add error_handling blocks below every query even they are fully tested for the live site (I only see the problem when database connections are half working for not die() ), as a good practice?

[blackhorse]

On second thought. I would go with NogDog's suggestion. Add error handling below every query. Before I do that, any other opinions?

For the regular query, should we just leave it as it is, or all of them should at least have a die(), or all of them for better practice, should have error handling block to send errors to a error blog etc.?

[NogDog]

The problem with die(), if not carefully applied, is that it can result in an invalid HTML document with unclosed tags, potentially rendering as garbled output, incomplete output, or no output at all. This can be very confusing/irritating to the user.

But if that does not concern you too much, what you could do is create a little custom die function to control what gets output depending on the debug status:

<?php
define('DEBUG', false);  // change to true for development
function myDie($error, $sql='')
{
   $details = $error;
   if(!empty($sql))
   {
      $details .= " ($sql)";
   }     

   $mysql = @mysql_error();
   if(!empty($mysql))
   {
      $details .= ": $mysql";
   }
   if(DEBUG)
   {
      die($details);
   }
   else
   {
      error_log($details);
      die($msg );
   }
}

// usage:
$result = mysql($sql) or 
      myDie("Sorry, there was a database error.", $sql);

This will only output the query and the mysql error if DEBUG is set to true, otherwise the user would just see the "Sorry,..." message.

[blackhorse]

thanks. I put all my query in the <?php ?> before <html>, so die or exit will be fine.

<?php
...
?>
<html>
</html>

But I think I will use your approach to create my own die() function. So the error could be emailed or logged.