if mysql_query() fails, what to do?

Question

Sometimes so happens that mysql_query() fails to INSERT data and I am unaware of it. So, the question is how do I know when it happens?

Answer 1

you have to analyze your sql. did you escape your parameters? sounds like this could be the problem. you may paste your sql so we can help you.

for showing the error, you can eg.

$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error());

.

Answer 2

Probably you can check LAST_INSERT_ID() (mysql_insert_id() in PHP). Provided that your table has auto_increment column.

Answer 3

You can use mysql_error php.net/mysql_error to get a better explanation that you then either display or log it in a file.

Answer 4

Quoting the documentation page of mysql_query :

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning resultset, mysql_query() returns a resource on success, or FALSE on error.

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

So, to detect whether there's been an error or not, you have to test the return value of your call to mysql_query :

$result = mysql_query('...');
if ($result === false) {
    // And error has occured while executing
    // the SQL query
}

Then, what can you do ?

• Well, first, you can log the error to a file, that you can analyse later
  • For than, you can use mysql_error and mysql_errno.
• And you can display a nice error message the user
  • i.e. some kind of "oops, an error occured page".

Remember, though : the user doesn't need to know (and will not understand) the technical error message -- so don't display it.

Answer 5

suggest making a wrapper for mysql_query that detects failure and logs the query plus mysql_error somewhere

Answer 6

If you're using a sensible client library, it will throw an exception when a SQL command fails, and you can examine the error code programmatically to know what to do.

If you have no code handling that specific error path, you should probably run a standard error handler which logs the error, stack trace and some other debug info into a log.

Answer 7

If the cause of the error is deadlock, you might want to try again.

Answer 8

One check that I usually do is like in the example

$result = mysql_query( $query );
if ( !empty( $error = mysql_error() ) )
{
    echo 'Mysql error '. $error ."<br />\n";
}
else
{
    // the query ran successfully
}

This is a good check for any kind of queries