Delete test orders in Magento

By: Dejan Radic, Dec 13, 2011

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.

Published in:

194 comments

Wellington says:

Apr 22, 2017 at 21:12

worked perfectly for me with, tks!

Reply
Betty Jewett says:

Sep 14, 2016 at 11:31

Awesome Post !
Used with Magento 1.9. Simple installation and effective solution to delete orders. Great tool!

Reply
primosoma says:

Sep 09, 2015 at 12:17
I added also
```
$connection->query("ALTER TABLE {$table} AUTO_INCREMENT=1");
```
after truncate, just for sake of completeness.
Reply
1. Abhishek Pandey says:
  
  Sep 22, 2015 at 12:06
  To avoid ‘1701 Cannot truncate a table referenced in a foreign key constraint’ issue in above script, you can add (to disable foreign check)
```
$connection->query("SET FOREIGN_KEY_CHECKS=0");
```
  after connection object is created. And add below at end.(to enable the foreign check)
```
$connection->query("SET FOREIGN_KEY_CHECKS=1");
```
Clive Marsh says:

Jul 17, 2015 at 17:30

I’ve found also an easier way to delete test orders, without using PHP scripts. The steps are described in this article: https://www.mag-manager.com/useful-articles/magento-orders-management/how-to-delete-magento-test-orders/

Reply
1. Hardy says:
  
  Jul 22, 2015 at 5:32
  
  Cheeky bastard!
bidhi says:

Jul 08, 2015 at 8:35

work for mebut how to reset lifetime sales and all dashboard

Reply
ina says:

Jul 07, 2015 at 2:46

Hello.
I really want to use that code but i have no idea how to use the code
thank you

Reply
André says:

Dec 22, 2014 at 14:59

All ok to remove all orders. I’ve maked a test with new order and invoiced it’s all right.

Magento Version 1.9.0.1 😉

Thanks.

Reply
Balas Vibradoras says:

Nov 20, 2014 at 22:40

Very useful. don’t really like to have them.

Reply
Farhan Islam says:

Nov 19, 2014 at 16:15

Any hint for how to delete an individual order in Magento once the Mage:: is initialized?

Reply
parag says:

Aug 05, 2014 at 13:30

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

Why?

Reply
1. Rakesh Patel. says:
  
  Feb 20, 2015 at 15:13
  
  Try Below script.. its working for me..
  
  ===========
  
  SET FOREIGN_KEY_CHECKS=0;
  TRUNCATE catalogsearch_query;
  ALTER TABLE catalogsearch_query AUTO_INCREMENT=1;
  
  TRUNCATE sales_flat_order;
  TRUNCATE sales_flat_order_address;
  TRUNCATE sales_flat_order_grid;
  TRUNCATE sales_flat_order_item;
  TRUNCATE sales_flat_order_payment;
  TRUNCATE sales_flat_order_status_history;
  
  TRUNCATE sales_flat_quote;
  TRUNCATE sales_flat_quote_address;
  TRUNCATE sales_flat_quote_address_item;
  TRUNCATE sales_flat_quote_item;
  TRUNCATE sales_flat_quote_item_option;
  TRUNCATE sales_flat_order_item;
  TRUNCATE sendfriend_log;
  TRUNCATE tag;
  TRUNCATE tag_relation;
  TRUNCATE tag_summary;
  TRUNCATE wishlist;
  TRUNCATE log_quote;
  TRUNCATE report_event;
  TRUNCATE tag_relation;
  TRUNCATE tag_summary;
  TRUNCATE wishlist;
  TRUNCATE log_quote;
  TRUNCATE report_event;
  
  ALTER TABLE sales_flat_order AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_order_address AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_order_grid AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_order_item AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_order_payment AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_order_status_history AUTO_INCREMENT=1;
  
  ALTER TABLE sales_flat_quote AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_quote_address AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_quote_address_item AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_quote_item AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_quote_item_option AUTO_INCREMENT=1;
  ALTER TABLE sales_flat_order_item AUTO_INCREMENT=1;
  ALTER TABLE sendfriend_log AUTO_INCREMENT=1;
  ALTER TABLE tag AUTO_INCREMENT=1;
  ALTER TABLE tag_relation AUTO_INCREMENT=1;
  ALTER TABLE tag_summary AUTO_INCREMENT=1;
  ALTER TABLE wishlist AUTO_INCREMENT=1;
  ALTER TABLE log_quote AUTO_INCREMENT=1;
  ALTER TABLE report_event AUTO_INCREMENT=1;
  
  TRUNCATE eav_entity_store;
  ALTER TABLE eav_entity_store AUTO_INCREMENT=1;
  
  INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’1\’, \’11\’, \’1\’, \’1\’, \’000000000\’);
  update eav_entity_store’ set ‘increment_prefix’= 1 where ‘entity_type_id’=\’5\’ and ‘store_id’=\’1\;
  update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’4\’ and ‘store_id’=\’1\;
  
  INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’2\’, \’16\’, \’1\’, \’2\’, \’000000000\’);
  update eav_entity_store’ set ‘increment_prefix’= 2 where ‘entity_type_id’=\’6\’ and ‘store_id’=\’1\;
  update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’18\’ and ‘store_id’=\’1\;
  
  INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’3\’, \’19\’, \’1\’, \’3\’, \’000000000\’);
  update eav_entity_store’ set ‘increment_prefix’= 3 where ‘entity_type_id’=\’8\’ and ‘store_id’=\’1\;
  update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’24\’ and ‘store_id’=\’1\;
  
  INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’4\’, \’23\’, \’1\’, \’4\’, \’000000000\’);
  update eav_entity_store’ set ‘increment_prefix’= 4 where ‘entity_type_id’=\’7\’ and ‘store_id’=\’1\;
  update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’28\’ and ‘store_id’=\’1\;
  
  SET FOREIGN_KEY_CHECKS=1;
  
  =============
suga says:

Jul 05, 2014 at 14:50

can’t able to delete orders in magento-1.9.0.1

Reply
analpaper says:

Apr 28, 2014 at 3:34

dont forget to truncate the table downloadable_link_purchased too.

Reply
Gert says:

Mar 05, 2014 at 20:41

This works with 1.8.1, but you first need to alter the code like Zaour said in an earlier comment.

Reply
kamleshwar says:

Jan 28, 2014 at 14:23

It worked for me .. i ‘m using 1.7.0.2.. 🙂 🙂

Reply
Shane says:

Jan 16, 2014 at 17:29

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

Reply
Nanyaks says:

Dec 16, 2013 at 21:21

It worked for me. Im using magento CE 1.7.0.2

Thanks.

Reply
avijit says:

Dec 05, 2013 at 20:07

works fine on 1.7.0.2 expets deltes the status of the orders..

Reply
Steve says:

Nov 26, 2013 at 17:36

Hat super funktioniert in 1.8.!
dankedankedanke!

Reply
Bernard says:

Nov 20, 2013 at 0:58

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.

Reply
Jim says:

Nov 07, 2013 at 3:25

Used the download and worked perfectly for me with 1.7.0.2 – much appreciated – Thks

Reply
Jo says:

Oct 14, 2013 at 1:54

Hi,

There is an extension that can automaticallly delete an order or range of orders at once.

http://www.magentocommerce.com/magento-connect/catalog/product/view/id/19988/

Reply
Aftab Khalid says:

Aug 12, 2013 at 12:05

Dejan Radic, Is this code compatible with 1.7.0.2?

Reply
mike says:

Jul 04, 2013 at 10:40

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

Reply
mike says:

Jul 04, 2013 at 9:56

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.

Reply
Jane says:

Jul 02, 2013 at 7:48

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

Reply
Darko Goles says:

Jul 02, 2013 at 7:44
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);
```
Reply
1. Vlad says:
  
  Jun 16, 2016 at 2:18
  
  Thank you Darko Goles, script with your fix works like a charm!
kreso says:

Jun 08, 2013 at 21:51

Thanks Dean,

great script.. helped me with test orders!

cheers from Zagreb

Reply
Oto says:

Apr 16, 2013 at 16:18

Anyone tested this with CE version 1.7.0.2?

Reply
Zaour says:

Apr 14, 2013 at 18:27

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

Reply
Alexandre Parra says:

Apr 04, 2013 at 13:48

Worked on 1.7 CE successfully.

Thank you!

Reply
MagePsycho says:

Mar 03, 2013 at 12:55
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!
Reply
Jose Esteves says:

Feb 18, 2013 at 14:53

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!

Reply
OX Vijay says:

Feb 17, 2013 at 15:49

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 🙂

Reply
MagentoHostSolution says:

Feb 17, 2013 at 6:53

After I decompile work some days and it is die again, so my experience is keep compile disable to keep all of extensions work well together 🙂

Reply
Beafully Swiss says:

Feb 07, 2013 at 4:38

It ‘ the way it is IMKate, it should do like that every time you install new extension for magento

Reply
IMKate says:

Feb 06, 2013 at 7:15

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?

Reply
IMKate says:

Feb 05, 2013 at 3:23

Work well for me with deleting order in Magento 1.6.1. Thanks Dejan Radic

Reply
Bones says:

Jan 21, 2013 at 13:03

Great script man, it helped me alot. Great job here at inchoo.net. Keep it up.

Reply
Sebastian says:

Jan 04, 2013 at 20:26

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?

Reply
1. Ray Foss says:
  
  Jan 08, 2015 at 22:30
  
  You should not kill the Foreign key constraints, they are there to preserve integrity. The better idea is to run the queries in test mode and reorganize all the queries to delete the children first.

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

Filter order grid by multiple ID’s

Custom Product Option and its use-case in Magento

194 comments

Leave a Reply Cancel reply