Delete test orders in Magento

Featured Image

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.

Interested in hiring us?

Have a chat with us. You would be surprised how small changes can make your business even more successful.


About Dejan Radic

Backend Developer

Dejan is a Magento Backend Developer, who is constantly learning and expanding his knowledge. When he's not dealing with Magento, he enjoys playing tennis.

Read more posts by Dejan / Visit Dejan's profile

182 comments

  1. Sales->order data will delete but On Dashboard, Lifetime Sales and Average Order values are available

    Why?

  2. Seems to cause issues with CE 1.8.1.0. Invoices are no longer created correctly following running this code.

  3. Perfect solution! Simple and fast! Downloaded from link at the end of the post and Worked like a charm with no editings. Just uploaded (magento root folder) and executed it.

    Thank you for sharing this.

  4. Hi again, Okay i just took the plunge and run the script and adding the reset order numbers and invoice numbers mentioned by Darko Goles.

    But this still did not work, running magento version 1.7.0.2.. any help would be appreciated.
    thanks

  5. Hi, i’ve used your script to delete my test orders, but have slight problem, i have a live order and when i try and process the invoice for this i get the error,

    Unable to save the invoice.

    I looked at my orders and i just have the 1 order, but looking in the database, under eav_entity_store
    I have some of my test order and the new order.. Looks like i had to reset the order numbers and the invoice numbers.. can anyone help me as how i can do this without loosing my 1 order details..

    thanks.

  6. Work for me, Darko Goles. I meet the foreign key problem all of time. You help me save much time since I don’t have to delete many tables in many times. Thanks

  7. For anybody that have FOREIGN KEY check errors, just add this before truncating tables:

    $q1 = "SET foreign_key_checks = 0";
    $connection->query($q1);

    And then on the end of the file we could add:

    $q1 = "SET foreign_key_checks = 1";
    $connection->query($q1);

    And sometimes is neccesary to reset order numbers, invoice numbers etc. after deleting this, so before end of the file we can add:

    $eavEntityStore = $resource->getTableName('eav/entity_store');
    $connection->truncateTable($eavEntityStore);
  8. Hello,

    I use magento 1.7.0.2 and I’ve got error messages like this:
    “Error SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`database`.`sales_flat_quote_address`, CONSTRAINT `FK_ZA_SALES_FLAT_QUOTE_ADDR_QUOTE_ID_ZA_SALES_FLAT_QUOTE_ENTT_ID` FOREIGN KEY (`quote_id`) REFERENCES `database`.`sales_flat_quote` (`entity_) occurred truncating the sales_flat_quote table.”

    To solve it we need to execute “SET FOREIGN_KEY_CHECKS = 0;” before and “SET FOREIGN_KEY_CHECKS = 1;” after the truncation commands.

    For this reason I’ve modified this script at lines 97 and 124 and added follow lines before and after “while” :
    $connection->query(“SET FOREIGN_KEY_CHECKS = 0″);
    while ($table = current($salesEntitiesConf) ){
    ….
    }
    $connection->query(“SET FOREIGN_KEY_CHECKS = 1″);

    Regards
    Zaour

  9. Awesome script!
    Just had a chance to use it and worked like a charm.

    If in case the script is executed accidently in the live environment it will delete live orders, so it’s good to put the following line of code at the last of the script:

    @unlink(__FILE__);

    which will delete the script file once it is run.

    Cheers!

  10. Hey Dejan! I appreciate your kindness to help people getting rid of the terrible nightmare (yes nightmare) of having to erase test orders in Magento. You surely are a great programmer since you produced that great script! But I have to say I’m too lazy, you know, I allways look for an easier way for getting things done. So I didn’t use your script but I user this extension: Seamless Delete Order. Thank you anyway for the good will!

  11. Hi,

    Script works successfully. But it truncates status related also. So it will create problems in order status. @Dejan Radic, pls change the script. If any one face like this problem, you can reset the following tables from any other magento installation. 1. sales_order_status 2.sales_order_status_state 3.sales_order_status_label. thanks guys :)

  12. Today, I tried to decompile and then compile again but it make my site down without any error show. After I delete cache folder and then delete this code, it work well again. Any idea why?

  13. Hm i have some errors when I receive new sales.
    After using the script it always troubles with Integrity constraint violations.
    Any experience with this?

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> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.