how can I delete test order from magento

Question

I have made a website in magento. Now it is live and I want to delete the all test order. I know there are some table in all these order are stored. but I don't know the name of those table. If some body has any script which can delete the all the order data.

Please give me or please mention the name of all the tables which stores the order information

http://blog.magentomagik.com/how-to-delete-magento-test-orders-from-a-magento-store/ — haha, Dec 24 '10 at 15:04
New link: http://blog.magikcommerce.com/how-to-delete-magento-test-orders-from-a-magento-store/ — Simon, Nov 15 '12 at 08:26
You can also use this extension: https://www.magentocommerce.com/magento-connect/orders-eraser.html — Sarfaraz bheda, Aug 20 '16 at 13:16

score 68 · Accepted Answer · edited Aug 02 '17 at 10:34

68

Go to admin>sales>orders
Write down your test orders incremental ids, for example 100000001,100000002,100000003,100000111,100000112,100000199
now create a php file in magento root directory and name it: remove_test_orders.php

Paste the code below:

require 'app/Mage.php';
Mage::app('admin')->setUseSessionInUrl(false);                                                                                                                 
//replace your own orders numbers here:
$test_order_ids=array(
  '100000001',
  '100000002',
  '100000003',
  '100000111',
  '100000112',
  '100000199',
);
foreach($test_order_ids as $id){
    try{
        Mage::getModel('sales/order')->loadByIncrementId($id)->delete();
        echo "order #".$id." is removed".PHP_EOL;
    }catch(Exception $e){
        echo "order #".$id." could not be remvoved: ".$e->getMessage().PHP_EOL;
    }
}
echo "complete.";

Now go to command line and run:

php remove_test_orders.php
At the end, delete remove_test_orders.php.

edited Aug 02 '17 at 10:34

R Hisgen

5
3

answered Dec 25 '10 at 23:44

Alireza Eliaderani

1,974
16
13

1

Hey.. is there any code that delete the orders and also re-level the inventory,means also add the quantity back to products – Ravi Soni Mar 03 '13 at 14:28
1

hey i used the above code. but still order is showing in my orders grid. when ever i click on order it is showing "order is no longer exists". i thing something went wrong. – krishna Mar 11 '13 at 14:03
@muralikalpana try to reindex your data. – Ivo Pereira Jul 29 '13 at 15:23
1

I've reindexed my data and am having the same problem as muralikalpana – MrJellyhands Jan 14 '14 at 17:55
I have also same problem as @muralikalpana, using magento v1.8.1 – Rahul Dadhich Aug 26 '14 at 12:39
1

I can confirm that this works on Magento ver. 1.9.0.1. It seems to be deleting all relevant data tied to the order as well, like invoices, transactions, etc. – jtate Apr 01 '15 at 18:25
1

I created a commanline tool, if anyone interested: https://github.com/mauricioprado00/magento-delete-order/releases – useless Jun 01 '15 at 16:40

score 14 · Answer 2 · answered Jul 11 '14 at 13:14

The above answer is too old and there is drawback of this answer that it doesn't empty payment tables so there is a chance of legacy.
you can use below code it will remove payment information also.

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `sales_flat_creditmemo`;
TRUNCATE `sales_flat_creditmemo_comment`;
TRUNCATE `sales_flat_creditmemo_grid`;
TRUNCATE `sales_flat_creditmemo_item`;
TRUNCATE `sales_flat_invoice`;
TRUNCATE `sales_flat_invoice_comment`;
TRUNCATE `sales_flat_invoice_grid`;
TRUNCATE `sales_flat_invoice_item`;
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_quote_payment`;
TRUNCATE `sales_flat_quote_shipping_rate`;
TRUNCATE `sales_flat_shipment`;
TRUNCATE `sales_flat_shipment_comment`;
TRUNCATE `sales_flat_shipment_grid`;
TRUNCATE `sales_flat_shipment_item`;
TRUNCATE `sales_flat_shipment_track`;
TRUNCATE `sales_invoiced_aggregated`;
TRUNCATE `sales_invoiced_aggregated_order`;
TRUNCATE `sales_order_aggregated_created`;
TRUNCATE `sendfriend_log`;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `wishlist`;
TRUNCATE `log_quote`;
TRUNCATE `report_event`;
ALTER TABLE `sales_flat_creditmemo` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1;
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_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_aggregated_created` 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;

SET FOREIGN_KEY_CHECKS=1;

This is going to delete all orders, not just test orders as the question asks. Be wary before you run this. — pspahn, May 27 '15 at 16:14

MagePal Extensions · Answer 3 · 2014-09-25T23:40:43.197

8

Take a look at How to delete orders from Magento

<?php
$mageFilename = 'app/Mage.php';
require_once $mageFilename;
Varien_Profiler::enable();
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app('default');
Mage::register('isSecureArea', 1);
//until here you gained access to the Magento models. The rest is custom code

$orderId = 156;//put here the id of the order you want to delete. THE ONE FROM THE DATABASE NOT THE INCREMENT_ID

$order = Mage::getModel('sales/order')->load($orderId);

$invoices = $order->getInvoiceCollection();
foreach ($invoices as $invoice){
   $invoice->delete();
}

$creditnotes = $order->getCreditmemosCollection();
foreach ($creditnotes as $creditnote){
   $creditnote->delete();
}

$shipments = $order->getShipmentsCollection();
foreach ($shipments as $shipment){
   $shipment->delete();
}

$order->delete();
?>

edited Sep 25 '14 at 23:40

answered Aug 15 '14 at 16:21

MagePal Extensions

17,646
2
47
62

1

I prefer this method. As It also clears the invoices, credit notes, and shipments. Whereas I'm not sure if the accepted answer does. +1 – Kingsley May 26 '15 at 09:45
And if you have an extension that overrides the normal orders model, you just load the extended orders collection instead of `sales/order` and it deletes thoughts just fine too! (Looking at Sweettooth rewards) – JPMC Jun 03 '15 at 13:35
Use `$order = Mage::getModel('sales/order')->loadByIncrementId($incrementId);` if you want to load by increment id. – ahnbizcad Nov 01 '17 at 23:18

score 4 · Answer 4 · answered May 05 '13 at 00:57

I setup a repository for maintaining simple SQL scripts to run before launching a new Magento site. So far they can help:

empty test data from Mage; orders, customers, reports, logs etc.
empty test data from various extensions
set desired IDs for next order, invoice, shipment and creditmemos

https://github.com/ccondrup/mage-reset
Help improve them!

score 4 · Answer 5 · answered Jun 01 '15 at 16:42

I created an open source commandline tool

you can use it like this:

#delete order by increment id
php -f shell/delete-order.php -- --increment-id 100000001

#test delete order by increment id (wont actually delete it)
php -f shell/delete-order.php -- --dry-run --order-id 32

https://github.com/mauricioprado00/magento-delete-order/releases

score 2 · Answer 6 · answered Dec 24 '14 at 07:52

Almost all the previous answers have problem with Foreign key Constraint or wrong table names. I was able to achieve the deletion on a Magento v 1.9.1.0 CE using the SQL below, I modified the SQL given in previous answers:

    SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `mg_sales_flat_creditmemo`;
TRUNCATE `mg_sales_flat_creditmemo_comment`;
TRUNCATE `mg_sales_flat_creditmemo_grid`;
TRUNCATE `mg_sales_flat_creditmemo_item`;
TRUNCATE `mg_sales_flat_invoice`;
TRUNCATE `mg_sales_flat_invoice_comment`;
TRUNCATE `mg_sales_flat_invoice_grid`;
TRUNCATE `mg_sales_flat_invoice_item`;
TRUNCATE `mg_sales_flat_order`;
TRUNCATE `mg_sales_flat_order_address`;
TRUNCATE `mg_sales_flat_order_grid`;
TRUNCATE `mg_sales_flat_order_item`;
TRUNCATE `mg_sales_flat_order_payment`;
TRUNCATE `mg_sales_flat_order_status_history`;
TRUNCATE `mg_sales_flat_quote`;
TRUNCATE `mg_sales_flat_quote_address`;
TRUNCATE `mg_sales_flat_quote_address_item`;
TRUNCATE `mg_sales_flat_quote_item`;
TRUNCATE `mg_sales_flat_quote_item_option`;
TRUNCATE `mg_sales_flat_quote_payment`;
TRUNCATE `mg_sales_flat_quote_shipping_rate`;
TRUNCATE `mg_sales_flat_shipment`;
TRUNCATE `mg_sales_flat_shipment_comment`;
TRUNCATE `mg_sales_flat_shipment_grid`;
TRUNCATE `mg_sales_flat_shipment_item`;
TRUNCATE `mg_sales_flat_shipment_track`;
TRUNCATE `mg_sales_invoiced_aggregated`;
TRUNCATE `mg_sales_invoiced_aggregated_order`;
TRUNCATE `mg_sales_order_aggregated_created`;
TRUNCATE `mg_sendfriend_log`;
TRUNCATE `mg_tag`;
TRUNCATE `mg_tag_relation`;
TRUNCATE `mg_tag_summary`;
TRUNCATE `mg_wishlist`;
TRUNCATE `mg_log_quote`;
TRUNCATE `mg_report_event`;
ALTER TABLE `mg_sales_flat_creditmemo` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_creditmemo_comment` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_creditmemo_grid` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_creditmemo_item` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_invoice` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_invoice_comment` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_invoice_grid` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_invoice_item` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_order` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_order_address` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_order_grid` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_order_item` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_order_payment` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_order_status_history` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_quote` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_quote_address` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_quote_address_item` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_quote_item` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_quote_item_option` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_shipment` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_shipment_comment` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_shipment_grid` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_shipment_item` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_flat_shipment_track` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_invoiced_aggregated` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_invoiced_aggregated_order` AUTO_INCREMENT=1;
ALTER TABLE `mg_sales_order_aggregated_created` AUTO_INCREMENT=1;
ALTER TABLE `mg_sendfriend_log` AUTO_INCREMENT=1;
ALTER TABLE `mg_tag` AUTO_INCREMENT=1;
ALTER TABLE `mg_tag_relation` AUTO_INCREMENT=1;
ALTER TABLE `mg_tag_summary` AUTO_INCREMENT=1;
ALTER TABLE `mg_wishlist` AUTO_INCREMENT=1;
ALTER TABLE `mg_log_quote` AUTO_INCREMENT=1;
ALTER TABLE `mg_report_event` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

Remember to change mg_ to your table prefix.

score 1 · Answer 7 · answered Oct 19 '15 at 09:29

Create a file in the root directory and paste the following code.
It will remove all the orders from admin grid!

<?php

/**
 * @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...');

?>

user1801605 · Answer 8 · 2013-10-27T15:17:38.777

With this extension, not only it will delete the order(s), invoice(s), shipment(s), credit memo(s), RMA(s), but also it will revert back all the information such as collected tax, deleted products back to inventory and reset Increment IDs.

If you make a mistake when creating the order/invoice/shipment/credit memo/RMA and want to delete it, then this extension can do very well.

There is a test site where you can try it first and see if it works for you or not.

http://www.magentocommerce.com/magento-connect/ultimate-delete-order.html

score 0 · Answer 9 · answered Jan 04 '11 at 15:53

0

Try this one from Inchoo:

Delete test orders in Magento

answered Jan 04 '11 at 15:53

Richard Feraro

467
4
9

I tried to copy/paste into vim and ended up with a bunch of weird characters. The accepted answer worked for me. – Buttle Butkus Mar 26 '14 at 19:05

score 0 · Answer 10 · answered Nov 16 '18 at 15:45

0

Simply got your database phpMyadmin and then open the table sales_flat_order and then simply remove tha data from this tabel and your test orders will be cleared. No need of any code or commands. cool.

answered Nov 16 '18 at 15:45

Asif Mehmood

473
3
16

how can I delete test order from magento

10 Answers10

Linked