Detect Failing Update Queries in Prestashop PHP Scripts

How to check if a query failed in custom Prestashop PHP scripts.

1015 views
d

By. Jacob

Edited: 2019-03-05 12:34

GDPR vs Ads.

I recently had to check if an update query failed in a custom PHP script for PrestaShop. The problem was, the query would return true, even when the row did not exist.

Querying the database directly was not an option, since I am using PrestaShops database methods.

Luckily, I found a method called Affected_Rows(), which returns the number of rows affected by a query. For SELECT statements, this will return the number of rows selected. For UPDATE and INSERTs, it will return the number of ROWs affected.

Here is the function I came up with:

function if_query_failed_error($query_result) {
  $affected_rows = Db::getInstance()->Affected_Rows(); // Figure out if the query had any affect
  if ((!$query_result) || ($affected_rows == 0)) {
    header('Content-Type: text/plain; charset=utf-8');
    http_response_code(500); // Internal Server Error (If something fails, it's likely due to something internal)
    echo 'Error: The SQL query has failed, or nothing needed to be updated.';
    exit();
  }
}

I used Content-Type to show a simple plain text error message to the client, but you can use your own error handler if needed.

The problem with UPDATES

For my SELECT statements, a simple if(!$result){ ... } statement would typically be enough to handle errors. This is not the case for UPDATE queries, since they will return true, even when the row you are trying to update does not exist in the table.

Using mysql_num_rows() will not work on update statements, and it has also been deprecated for a long time. But there are other options for MySQLi or PDO_MySQL. These are (according to php.net):

  1. mysqli_num_rows()
  2. mysqli_stmt_num_rows()
  3. PDOStatement::rowCount()

When creating a custom script for PrestaShop, you are likely using the build-in database class. So, the above is not an option. Instead you can use Affected_Rows() like this:

Db::getInstance()->Affected_Rows();

The Affected_Rows() method will return the number of rows affected by the last query performed by your script. The Db::getInstance() part refers to the database instance/link opened by your script.

If you worry this might get the wrong value due to concurrent requests, you got no reason to worry. The instance is unique to the script that opened it, so it will not be mixed up with other scripts or concurrent users running the script. I guess it works the same as if you used rowCount().

A working script would probably look something like this:

require $_SERVER['DOCUMENT_ROOT'] . '/config/config.inc.php'; // Enables use of PrestaShop features/classes...

$table_name = _DB_PREFIX_ . 'stock_available';
$query = "UPDATE $table_name SET out_of_stock = '$out_of_stock' WHERE id_product ='$id_product'";
$result = Db::getInstance()->executeS($query);
if_query_failed_error($result); // Exit() with "500 Internal Server Error" on error

// If no error, continue!

function if_query_failed_error($query_result) {
  $affected_rows = Db::getInstance()->Affected_Rows(); // Figure out if the query had any affect
  if ((!$query_result) || ($affected_rows == 0)) {
    header('Content-Type: text/plain; charset=utf-8');
    http_response_code(500); // Internal Server Error (If something fails, it's likely due to something internal)
    echo 'Error: The SQL query has failed, or nothing needed to be updated.';
    exit();
  }
}

Links

  1. Best Practices of the Db Class - prestashop.com

Tell us what you think: