Ecommerce developers with Magento

Hello Matthew,
That was the point of this post. It is currently impossible to do it with Magento administration. You would have to go to MySQL client and delete relevant entities manually starting with sales_order* tables.

Works like a charm. Thanks for the post!

Paddy

Correct me if I’m wrong, but don’t you want to add these tables too?

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `sales_order_tax`;
TRUNCATE `sales_flat_quote_shipping_rate`;
TRUNCATE `sales_flat_quote_payment`;
TRUNCATE `log_url_info`;
TRUNCATE `log_url`;

ALTER TABLE `sales_order_tax` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS=1;

Hey there Tomislav!

Great post here, very appreciated on behalf of the community!

We did a bit further modification by the following:
+ resetting all dashboard data
+ setting prefix’s for the following numbers: orders, invoices, shipments, and credit memo’s

Feel free to check it out here: http://eliasinteractive.com/blog/magento-ecommerce-how-to-reset-all-test-order-information-and-set-unique-prefix-for-orders-invoices-shipments-and-credit-memos/

Thanks again for your contributions!
Lee Taylor / Elias Interactive

Hi Tomislav,

Thoroughly useful script. Exactly what I was looking for whilst developing out my Magento shop. A real time saver…

Cheers!

@Robert: We surely will, just when we manage to catch some free time. Hope you understand. Few days passed since your question and I was wondering if you managed to solve the issue already. If you did, feel free to post the update query to us and we’ll publish it. Thanks.

THANKS!

I confirm, works great under 1.3.2.2!
thanks so much

Thanks for the post.

You also might want to truncate the following tables too:

whishlist_item
table that starts with catalogsearch

Works perfectly on Magento ver. 1.3.2.3 !
Thanks for this trick

Thanks a lot!!!

I comfirm that works with 1.3.2.4, thanks

Like others I was astonished to find this facility not present.

I have php and sql knowledge and feel for those that have mentioned the inability to get around these things. I have made a simple form using the code discussed on this site and it is available for download at http://www.mycreateabook.com.au/magentoReset.zip for all who wants to use it.

The only requirement would be that you need to know your sql database settings that should be available from your website administrator if you do not already know it.

Thanks for the code in the firt place. It saved me heaps of time fishing through to work it out myself. I am still in the process of setting up my site with Magento. It is visible at the moment at http://webstore.mycreateabook.com.au

Hi Matthew.

If you are referring to my post, it has been included already but I have it at the bottom of the file to keep it separate.

Very effective query. We had a lot of test data in our database, and we’re lauching into production today. Thanks for the help.

Absolutely brilliant. Thanks so much for posting this – worked like a charm. Solid gold.

Brilliant stuff, this worked without flaw on version 1.3.2.4 Thanks.

please could you provide the sql query to delete the top and last 5 search terms?

[...] for 1.3.2.4 use http://inchoo.net/ecommerce/magento/delete-test-orders-in-magento/ [...]

Has anyone tried this with release 1.4.0.1 ?

I delete specific orders like this (cobbed together from magento forums)

first, set collation with this script:
$value)
{
mysql_query(“ALTER TABLE $value COLLATE $db_collate”);
echo $value.’ changed to ‘.$db_collate.’ ‘;
}
}
echo “DB collation changed”;
?>

Then I run this in phpmyAdmin (just change the order ID at the start):
[it does seem to mess with the order ID in the URLs]

## SET FOREIGN_KEY_CHECKS = 1;
## WORKS ON VERSION 1.3.2.3

SET @orderId = ’100000772′;

SET FOREIGN_KEY_CHECKS = 1;

SET @salesFlatQuoteId = (
SELECT entity_id
FROM sales_flat_quote
WHERE reserved_order_id = @orderId
);
SET @salesOrderId = (
SELECT entity_id
FROM sales_order
WHERE increment_id = @orderId
);

CREATE TEMPORARY TABLE del_sales(
id INT AUTO_INCREMENT PRIMARY KEY,
salesId INT(10)
)
COLLATE ‘utf8_unicode_ci’;

CREATE TEMPORARY TABLE del_statusSales(
id INT AUTO_INCREMENT PRIMARY KEY,
salesId INT(10)
)
COLLATE ‘utf8_unicode_ci’;

INSERT INTO del_statusSales (salesId)
SELECT entity_id
FROM sales_order_entity_int
WHERE value = @salesOrderId
AND attribute_id = ANY (
SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = ‘order_id’
)
AND entity_id = ANY (
SELECT entity_id
FROM sales_order_entity
WHERE entity_type_id = ANY (
SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = ‘invoice’
OR entity_type_code = ‘shipment’
OR entity_type_code = ‘creditmemo’
)
);

INSERT INTO del_sales (salesId)
SELECT entity_id
FROM sales_order_entity
WHERE parent_id = ANY (
SELECT salesId
FROM del_statusSales
)
AND entity_type_id = ANY (
SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = ‘invoice_item’
OR entity_type_code = ‘invoice_comment’
OR entity_type_code = ‘shipment_item’
OR entity_type_code = ‘shipment_comment’
OR entity_type_code = ‘shipment_track’
OR entity_type_code = ‘creditmemo_item’
OR entity_type_code = ‘creditmemo_comment’
);
INSERT INTO del_sales (salesId)
SELECT salesId
FROM del_statusSales;

INSERT INTO del_sales (salesId)
SELECT entity_id
FROM sales_order_entity
WHERE parent_id = @salesOrderId;

DELETE FROM sales_order_entity
WHERE entity_id = ANY (
SELECT salesId
FROM del_sales
);

DELETE FROM sales_flat_quote
WHERE reserved_order_id = @orderId;

DELETE FROM sales_flat_order_item
WHERE quote_item_id = @salesFlatQuoteId;

DELETE FROM sales_order
WHERE increment_id = @orderId;

/* drop temp tables */
DROP TEMPORARY TABLE del_sales;
DROP TEMPORARY TABLE del_statusSales;

great job. thanks

Is there any way for doing this in magento 1.4.1? Thanks ~

This is not working on Recent Magento v1.4.1
Help me~~~~

Because Magento uses foreign keys, it’s enaugh to run truncate on 2-3 tables `sales_flat_order` , `sales_flat_quote` (for 1.4.1.0 didn’t test to see if there are any but tables as sales_flat_order_… will also be truncated) also truncate will set your increment id to 1

you can Easily Delete orders and manage all orders via Order book extension
check this out
http://www.magentocommerce.com/magento-connect/Modulesoft+Solutions/extension/4047/mspl_orderbook

found this one on a german site…… for magento 1.4.1.x

i am going to try this on my test magento shop, will let you know if it worked….

– Reset Magento TEST Data
SET FOREIGN_KEY_CHECKS=0;
– reset dashboard search queries
TRUNCATE `catalogsearch_query`;
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;
– reset sales order info
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;

oh,it show error on my magento 1.4.1.0.
here is error:

Error

SQL query:

– RESET dashboard search queries TRUNCATE `catalogsearch_query` ;

MySQL said: Documentation
#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘– reset dashboard search queries
TRUNCATE `catalogsearch_query`’ at line 1

Works fine for me on 1.4.1.0, just remove the comment lines starting with “-” before running the query~

Thanks to Jordi Bos.