How to delete test orders and keep a clean database in PrestaShop

Remove PrestaShop Orders

One of the question that PrestaShoppers ask time by time is how to remove orders.

Why orders can’t be deleted by default?

Mainly because in Europe it’s considered illegal to remove ordering, invoice or payment information from a business.
Find out if in your country it’s illegal to remove orders and follow this tutorial at your own risk.

Still want to delete some orders?

If you’ve just created your store and started testing it, then decided that all is good and the tests must be removed, PrestaShop provides a module called Database Cleaner.
This can remove all the content in your store (that includes orders) to start from scratch.

The problem arises when you already had your store into the wild for a while, did some changes to your store configuration, maybe you added a new addon that has a big impact on the store’s functionality, did an upgrade from an older version (we all know how fun that is), whatever the reason might be you now want to test the ordering process. Then you’re stuck with those fake orders.

A feature that PrestaShop could implement to make our lives easier when testing the store is to flag all orders placed when store is in Demo mode as test orders and allow us to remove them. But until then …

What can be done?

For those of you who don’t have any form of OCD, just cancel the orders and forget about them.
But for the rest of “normal” people, don’t despair, there’s hope and hope dies last.

Do not attempt any solution that you find online (including this one) before making a full BACKUP!

Searching through PrestaShop Forum you’ll find a few hacks, some free and paid modules that can accomplish the task. But what most of them don’t address is how clean the database remains after removing a test order. This leads to a database filled with junk data that could cause some serious damage to your store.

The clean way to delete test orders

Because we are talking about keeping a clean database we’re going to work with the database directly using MySQL queries.
You could use PhpMyAdmin or whatever method you find more convenient to run the queries.

If you don’t have any experience in working with MySQL please do not continue!
The code from this tutorial might need adaptation to your store environment in order to fit your needs, get in touch with a web developer to follow this tutorial for you.

PrestaShop hasn’t yet implemented FOREIGN KEY constraints so we need to manually find how the id keys included in ps_orders table are spread across the database then simulate the ON DELETE CASCADE behavior.
Search for all the tables that have id_order and also the tables that have id_cart because all orders have associated a cart.

In the highlighted line 5 make sure you replace {{PS DATABASE NAME }} with the database name of your store.

With the resulted tables create a DELETE query from their LEFT JOIN.
If you have in the result listed tables that don’t appear on my query, include them also in query.
If in my query are tables that are not present in your resulted list, remove them from your query.

In the highlighted line 37 make sure you replace {{ID OF ORDER TO DELETE}} with the order id that you want to remove.

That’s it! You have successfully removed a test order.

In the trenches

But in a real life scenario when we test, we test. We might have many test orders placed and repeating the process above for all of them is tedious.
I often create one or more test customer accounts which I use to place many different test orders.

If you want to delete the test orders placed with a test customer account edit the snippet code Remove a test order and replace the WHERE clause from the highlighted line 37 with:

Make sure you replace {{ID OF TEST CUSTOMER}} with the customer id that you want to remove all of its placed orders.

What’s weird in all this “ILLEGAL TO DELETE ORDERS” thing is that we can delete customers and if the invoice feature is disabled there is no way to retrieve any information about who placed those orders, which makes them useless if some government agency wants to track them. You might still want to keep them for some statistical accuracy.

Here’s a little bonus MySQL query to see if you have orders of customers that have been removed.

If you made many test customer accounts and now you want to remove them and their orders, first remove the customers as you would normally do from Back Office.
Then remove all the orders placed by removed customers by editing the snippet code Remove a test order and replace the WHERE clause from the highlighted line 37 with:

Now your orders are clean as a whistle.
I wish you less test orders and more real ones.

 

  • Jorge Aramuni

    Thanks for tutorial! I’m looking for a way to remove all SAMPLE DATA that is installed with Prestashop. Some people say to use the module DATABASE CLEANER. I tried it, but it is not working right in prestashop 1.6.1.4. Some data is not deleted, like the manufacturers. Another solution (and better) that I’m looking for, is making a CLEAN INSTALL of the store. But in prestashop site there is not a clean file, without the demo samples. What do you suggest?

    • Hi, by default PrestaShop doesn’t provide a way to make an install without the demo data.
      I’ve tested module Database Cleaner on PrestaShop v1.6.1.4 and it removed all the sample data correctly. If the module isn’t removing manufacturers, it should be only one sample manufacturer, try to delete it manually from Back Office. Then to make sure your manufacturers ids will increment correctly from 1, into your database, truncate table ps_manufacturer.

      • Jorge Aramuni

        Thanks Gabriel! Database Cleaner (pscleaner module) works well! I had to edit PHP.INI file in the server, increasing the time for executing scripts:

        max_input_time: -1

        max_execution_time: 300

        After editing, it’s OK to run pscleaner module.

    • Hi Jorge, after you asked I’ve looked closer into how it could be done to avoid installing the demo data at install. It turns out it’s actually very simple. Because there no reference to this into PrestaShop documentation I’ve written an article about it here: http://premiumpresta.com/blog/install-prestashop-without-sample-data

  • PsDesigner

    Hi,
    in Prestashop 1.7.2 your guide seems to work fine but the query deletes also any product discount.

    Could we fix this problem?

    Thank you.

Subscribe!