Delete test orders in Magento

You got a Magento project to develop, you created a Magento theme, you placed initial products and categories and you also placed some test orders to see if Shipping and Payment methods work as expected. Everything seems to be cool and the client wishes to launch the site. You launch it. When you enter the administration for the first time after the launch, you will see all your test orders there. You know those should be deleted. But how?
This is a new version of the same article.

If you try to delete orders in the backend, you will find out that you can only set the status to โ€œcancelledโ€ and the order is still there.ย  Unfortunately, Magento doesn’t enable us to delete those via administration, so you will not see any “Delete order” button. This can be quite frustrating both to developers and the merchants. People coming from an SAP world find the inability to delete to have some merit but there should be a status that removes the sales count from the reports i.e. sales, inventory, etc.

So, what to do?

You need to create php script in Magento root folder, copy this code into script and run script.

/**
 * @author Dejan Radic <dejan.radic@inchoo.net>
 */
 
if (version_compare(phpversion(), '5.2.0', '<')===true) {
    echo  '<div style="font:12px/1.35em arial, helvetica, sans-serif;"><div style="margin:0 0 25px 0; border-bottom:1px solid #ccc;"><h3 style="margin:0; font-size:1.7em; font-weight:normal; text-transform:none; text-align:left; color:#2f2f2f;">Whoops, it looks like you have an invalid PHP version.</h3></div><p>Magento supports PHP 5.2.0 or newer. <a href="http://www.magentocommerce.com/install" target="">Find out</a> how to install</a> Magento using PHP-CGI as a work-around.</p></div>';
    exit;
}
 
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
 
$mageFilename = 'app/Mage.php';
 
if (!file_exists($mageFilename)) {
    echo $mageFilename." was not found";
    exit;
}
 
require_once $mageFilename;
 
Mage::app();
 
$executionPath = null;
 
/*
 * determine Magento Edition
 */
if (file_exists('LICENSE_EE.txt')) {
    $edition = 'EE';
}elseif (file_exists('LICENSE_PRO.html')) {
    $edition = 'PE';
} else {
    $edition = 'CE';    
}
 
if(($edition=='EE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
        || ($edition=='PE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
        || ($edition=='CE' && version_compare(Mage::getVersion(), '1.6.0.0.', '<')===true)
  ){
   $executionPath = 'old'; 
} else {
   $executionPath = 'new';  
}
 
$xpathEntity = 'global/models/sales_entity/entities//table';
 
if ($executionPath == 'old') {
    $xpathResource = 'global/models/sales_mysql4/entities//table';
} else {
    $xpathResource = 'global/models/sales_resource/entities//table';
}
 
$salesEntitiesConf = array_merge(
    Mage::getSingleton('core/config')->init()->getXpath($xpathEntity), 
    Mage::getSingleton('core/config')->init()->getXpath($xpathResource)
);
 
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_write');
 
 
/*
 * If you want delete System/Order Statuses (Status and State) you
 * should comments below lines (46-51)
 */
$skipTables = array (
        $resource->getTableName('sales_order_status'),
        $resource->getTableName('sales_order_status_state'),
        $resource->getTableName('sales_order_status_label')
    );
$salesEntitiesConf = array_diff($salesEntitiesConf, $skipTables);
 
 
/*
 
Multiple RDBMS Support in Magento CE 1.6+ / EE 1.11+
    http://www.magentocommerce.com/images/uploads/RDBMS_Guide2.pdf
 
2.2. Adapters:
 
... The new Varien_DB_Adapter_Interface was added to sign a contract that all 
developed adapters must execute in order to get Magento working on an actual 
database. The interface describes the list of methods and constants that can be used by resource models...
 
Used below in the loop:
 
 * If $executionPath == 'old'
    * Varien_Db_Adapter_Pdo_Mysql::showTableStatus()
    * Varien_Db_Adapter_Pdo_Mysql::truncate()  
 * Else
    * Varien_Db_Adapter_Interface::isTableExists()
    * Varien_Db_Adapter_Interface::truncateTable()
 
*/
 
while ($table = current($salesEntitiesConf) ){
    $table = $resource->getTableName($table);
 
    if ($executionPath == 'old') {
        $isTableExists = $connection->showTableStatus($table);
    } else {
        $isTableExists = $connection->isTableExists($table);
    }
    if ($isTableExists) {
        try {
            if ($executionPath == 'old') {
                $connection->truncate($table);
            } else {
                $connection->truncateTable($table);
            }
 
            printf('Successfully truncated the <i style="color:green;">%s</i> table.<br />', $table);
        } catch(Exception $e) {
            printf('Error <i style="color:red;">%s</i> occurred truncating the <i style="color:red;">%s</i> table.<br />', $e->getMessage(), $table);
        }
    }
 
    next($salesEntitiesConf);
}
 
exit('All done...');

After you have it executed, the test orders will not be in the database any more. Keep in mind that this will delete ALL orders, in the database. So, you should execute this queries immediately after launch.

This script is tested on following Magento versions: 1.6.1.0 CE, 1.6.0.0 CE, 1.5.1.0. CE, 1.11.0.2 EE, 1.10.0.2 EE, 1.9.0.0 EE, 1.11.0.0 PE, 1.10.0.2 PE and 1.9.0.0 PE.
You can download script here.

You made it all the way down here so you must have enjoyed this post! You may also like:

Adding gallery tab to a CMS page Antun Martinovic
, | 5

Adding gallery tab to a CMS page

Filter order grid by multiple ID’s Petar Sambolek
Petar Sambolek, | 13

Filter order grid by multiple ID’s

Custom Product Option and its use-case in Magento Ivan Galambos
Ivan Galambos, | 25

Custom Product Option and its use-case in Magento

194 comments

  1. @DB I guess, you can create you own script from this but modifying the customer_ tables.

    Don’t forget to make a backup of your database first ๐Ÿ˜‰

  2. Nice!

    Is there something like that to clear up all test customer accounts and product data in one go safely?

  3. Using version CE1.6.2.0. Removed all orders, no errors. Thank you so very much. You saved me from having to install buggy modules I would only use once if they even worked. Great script!

  4. Great script but skiptables doesn’t work for me on CE1.6.2. Replacing it with the following:

    $skipTables = array (
    		'sales_order_status', 
    		'sales_order_status_state', 
    		'sales_order_status_label'
    );
    $salesEntitiesConf = array_diff($salesEntitiesConf, $skipTables);

    i can preserve my statuses! ๐Ÿ™‚

    The problem seems to be that $resource->getTableName(‘sales_order_status’) return the db table name with the installation prefix when $salesEntitiesConf entries don’t contain it.

    However a great script!

  5. You to thank you for such a nice contribution to the community.

    I can confirm it works perfect in CE 1.7.0.2

  6. How can you add orders to magento EE using API for version 1.8 – is this allowed or do you need a license?

  7. I also got these Key Constraints Violation errors in my exception.log and using the Script in Magento 1.7

    For the latter I just manually had to truncate the (in my case) three tables using

    SET FOREIGN_KEY_CHECKS = 0;
    and
    SET FOREIGN_KEY_CHECKS = 1;

    before and after it to ignore the key constraints.

    The errors in the exception.log come from the not truncated LOG_QUOTE table. Just truncate this as well and you shouldn’t receive another error.

  8. Hi there,
    Ran this perfectly on our staging server (on 1.6.1) but getting all sorts of Foreign Key Constraints on our (just before launch) production server:
    SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`loveknitting`.`sales_flat_quote_address`, CONSTRAINT `FK_SALES_FLAT_QUOTE_ADDRESS_QUOTE_ID_SALES_FLAT_QUOTE_ENTITY_ID` FOREIGN KEY (`quote_id`) REFERENCES `loveknitting`.`sales_flat_quote` (`) occurred truncating the sales_flat_quote table.

    Any ideas?

    Hoping you can help.

    Thanks for posting

  9. Thanks for posting this! We’ll be sure to save the link for the next time one of our merchants or development partners asks about deleting test transactions.

  10. Tested and work perfect in magento 1.6.1, it save a lot of time when I have to install delete order extension and do that, now I just have it already in my magento extension all of time.

    Thanks a lot

  11. Is this script also working for Magento 1.7? I hope it does. If not will you make a script? Thanks

  12. Hey i run the script to delete the orders. The orders are deleted at the backend side but still the orders are displaying in database sales_flat_order & sales_flat_order-item. Can you help me out.

    Regards,
    kiran

  13. Thanks a lot! Worked like a charm on my ready-to-launch installation (Magento 1.5.1.0, MarketReady Germany 2.1.18, several extension).

    Kind regards,
    Marco

  14. When I use the script in the article it for some reason doesn’t truncate the table ‘sales_order_tax_item’

    This resulted in error like this

    SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ’18-26′ for key 2

    when clicking Place Order in the Checkout because there would already by old rows with the same item ids in the ‘sales_order_tax_item’ table. I have now deleted the old rows from this table which should prvent that error from occuring anymore.

    The error doesn’t show on every order, just when the same ids occur that already exists in the table.

    Thought this could help anyone else getting the same errors.

  15. Hello Gregg,

    First off all its not my script ๐Ÿ™‚ But i did ran in the same problem as you. I lost all my order statussen.

    So what is did was add the three lines of code on rule 76 of above script.

    It simply removes the three tables (about the statussen) from the table truncate array.

    If you want to make sure these are the right array keys for EE first run the script with the delete part comment out and print the array and check if the numbers are correct.

  16. Hey Derrick,

    I ran your script on EE 1.11.1 and it removed the default order statuses. I see you have a section commented out in your script but not quite sure what’s going on. Can you fill me in? Is there a way to get those back?

    Thanks

  17. Seems like the array_diff doesn’t work in my case

    If i use this in stead. It works.

    unset($salesEntitiesConf[20]);
    unset($salesEntitiesConf[21]);
    unset($salesEntitiesConf[22]);

    It removes the three tables from the array

  18. Hello,

    When i use this script the SkipTables doesn’t work and it delete my order statussen every time.

    Anywone has a clue why?

    The only thing that comes in my mind is the table prefix i use.

  19. thanks for the script, worked perfect in ver 1.6.0.0 – i installed and ran wee_deleteorders from magento connect but it still left details of orders on the sales/orders page / your script got rid of everything

    cheers again, jack

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.