if mysql_query() fails, what to do?

Sometimes so happens that mysql_query() fails to INSERT data and I am unaware of it. So, the ques开发者_高级运维tion is how do I know when it happens?

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.

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.

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

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

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.

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());

.

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

You can check how many rows MySQL has inserted/updated/deleted by doing this query right after your insert/update/delete query.

SELECT ROW_COUNT() as rows_affected

If this returns 0 your insert failed. If it returns 1 or more you've succeeded.

See: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_row-count

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