Delete Test Orders in Magento Using MySql Queries

Many Magento users find it very disappointing not to be able to delete test orders or at least to make them removed from the Admin Sales page.

It’s a required step that merchants or developers place several test orders before they officially launch the site. Obviously, they don’t want their test sales mixed in the regular sales reports. It’s hard to believe, but there is no easy solution for this in Magento Admin panel.

These MySql queries can be handy when you want a fresh start with your Magento store. This is not a definite solution because you directly update the database using queries and also because it works only when you can remove all order data.

This includes queries to delete customer data and orders for downloadable products.

Don’t forget to backup your DB before you run these!

SET FOREIGN_KEY_CHECKS=0;

– Reset sales data
TRUNCATE `sales_order`;
TRUNCATE `sales_order_datetime`;
TRUNCATE `sales_order_decimal`;
TRUNCATE `sales_order_entity`;
TRUNCATE `sales_order_entity_datetime`;
TRUNCATE `sales_order_entity_decimal`;
TRUNCATE `sales_order_entity_int`;
TRUNCATE `sales_order_entity_text`;
TRUNCATE `sales_order_entity_varchar`;
TRUNCATE `sales_order_int`;
TRUNCATE `sales_order_text`;
TRUNCATE `sales_order_varchar`;
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`;

ALTER TABLE `sales_order` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_datetime` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_decimal` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_int` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_text` AUTO_INCREMENT=1;
ALTER TABLE `sales_order_varchar` 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;

– Reset customers data
TRUNCATE `customer_address_entity`;
TRUNCATE `customer_address_entity_datetime`;
TRUNCATE `customer_address_entity_decimal`;
TRUNCATE `customer_address_entity_int`;
TRUNCATE `customer_address_entity_text`;
TRUNCATE `customer_address_entity_varchar`;
TRUNCATE `customer_entity`;
TRUNCATE `customer_entity_datetime`;
TRUNCATE `customer_entity_decimal`;
TRUNCATE `customer_entity_int`;
TRUNCATE `customer_entity_text`;
TRUNCATE `customer_entity_varchar`;
TRUNCATE `log_customer`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;

ALTER TABLE `customer_address_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_address_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_datetime` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_decimal` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_int` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_text` AUTO_INCREMENT=1;
ALTER TABLE `customer_entity_varchar` AUTO_INCREMENT=1;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;

– Reset purchased downloadable data
TRUNCATE `downloadable_link_purchased`;
TRUNCATE `downloadable_link_purchased_item`;
ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;

– Reset all ID counters
TRUNCATE `eav_entity_store`;
ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

Magento Log Tables - Admin Log Cleaning

Magento saves visitor and URL access information in its log tables (log_url, log_url_info, log_visitor and log_visitor_info). These tables can be quite large and affect site performance if they are not maintained. Majority of people already have some kind of traffic analysis tool - one of the most popular tools is free Google Analytics - and they don’t necessarily find Magento log tables very efficient.

You can set up a log cleaning cron job through Magento Admin panel.

  • Go to System -> Configuration.
  • Click on System from the left menu (almost at the bottom).
  • Open Log Cleaning area and change the settings as you want.

Magento Log Cleaning

A bug was reported in the 1.2.0.3 version regarding this functionality. It seems fixed and the fix should be included in the next release:

Locate app/code/core/Mage/Adminhtml/Model/System/Config/Backend/Log/Cron.php:

$enabled = $this->getData(’groups/log/enabled/value’);
$time = $this->getData(’groups/log/fields/time/value’);
$frequncy = $this->getData(’groups/log/frequency/value’);
$errorEmail = $this->getData(’groups/log/error_email/value’);

Change the above codes from the function ‘_afterSave()’ to the following:

$enabled = $this->getData(’groups/log/fields/enabled/value’);
$time = $this->getData(’groups/log/fields/time/value’);
$frequncy = $this->getData(’groups/log/fields/frequency/value’);
$errorEmail = $this->getData(’groups/log/fields/error_email/value’);

(It’s missing “fields/” in $enabled, $frequncy, and $errorEmail.)

Eclipse PDT Word Wrap

We’d think our lovable free PHP IDE, Eclipse PDT, would come with word wrap functionality because it’s so basic. Surprisingly, it doesn’t. However, it’s not complicated to add this feature to your Eclipse PDT editor.

  • Go to Help -> Software Updates.
    Eclipse Software Update
  • Click Available Software.
  • If the site ‘http://ahtik.com/eclipse-update’ is not already listed, click Add Site.
  • Enter ‘http://ahtik.com/eclipse-update’.
  • Install Virtual Word Wrap by checking the left side box.
    Eclipse PDT Virtual Word Wrap
  • Left click in the editor.
  • Turn Virtual Word Wrap on by clicking it from the pop up window.
  • Eclipse PDT Virtual Word Wrap

We still have turn on the feature for each PHP file, but it’s still better than not having one.

Magento Table Structure

When you first look at Magento database, it can be overwhelming because of its complexity. The Magento database is heavily dependent on EAV (Entity Attribute Value) model. While the EAV model makes it easier to expand the database, i.e. adding your own entities or attributes specific for your store, SQL codes can be very difficult to write.

Before you customize Magento database, make sure you read through EAV model and you understand Magento table structure thoroughly.

EAV Core Tables
All EAV tables are prefixed with “eav_”.

  • eav_entity_type: table of all entitiesIt contains information about entity codes, entity models, entity tables and more.
    Example entities: customer, order, catalog_category, catalog_product, invoice, shipment, and so on.
    Each entity has a corresponding data table prefixed with “_entity”, i.e. customer_entity, sales_order_entity, catalog_category_entity, and so on.
  • eav_attribute: table of all attributes
    It defines all necessary attributes for each entity. For example, a customer has first name, last name, email address and so on. Customer is defined as an entity in the eav_entity_type table and customer’s attributes such as first name, last name or email are defined in the eav_attribute table. Hundreds of attributes are defined by default with Magento installation.
  • eav_attribute_option, eav_attribute_option_value
    These two tables are used to define options for each attribute. For example, the manufacturer attribute can have “Toshiba”, “Dell”, or “HP” for its options. These option values are stored in the eav_attribute_option_value table and the relationship of each option and the attribute is stored in the eav_attribute_option table. An option can also have multiple option values when it’s used for multiple stores.
  • eav_attribute_set
    This table is used to define different attribute sets for an entity. For example, a cell phone has different options from a camera. Both cell phone and camera are products (entities) that have different option sets (attribute sets).

Table Sets (Table collections)
In Magento database, an entity can have several tables that share the same prefix. For example, the product entity has the catalog_product_entity table for its main data and several other tables prefixed with “catalog_product_” such as catalog_product_entity_int, catalog_product_entity_media_gallery, catalog_product_entity_text and so on.

To store the data more efficiently, product details are stored separately depending on their data types. When the value of the data is an integer type, it’s saved in the catalog_product_entity_int table, and when its type is an image, it’s saved in the catalog_product_entity_media_gallery table. The whole point is not saving big image data with small integer data in the same table.

Tables to Define Relationships
The catalog_category_product table, catalog_product_website or downloadable_link_purchased are examples of the tables that show relationships. The catalog_category_product table show which category includes which products and the downloadable_link_purchased table shows which order has which downloadable links.

How to Edit the Magento Footer & Footer Links

Edit Magento Footer Links

The Magento footer links are collected from a few different layout components:

1. About Us | Customer Service
-> cms.xml

These two links are default static blocks taken from the database. To edit them, go to Magento Admin -> CMS -> Static Blocks.

2. Site Map
-> catalog.xml

<reference name=”footer_links”>
<action method=”addLink” translate=”label title” module=”catalog” ifconfig=”catalog/seo/site_map”><label>Site Map</label><url helper=”catalog/map/getCategoryUrl” /><title>Site Map</title></action>
</reference>

3. Search Terms | Advanced Search
-> catalogsearch.xml

<reference name=”footer_links”>
<action method=”addLink” translate=”label title” module=”catalogsearch” ifconfig=”catalog/seo/search_terms”>
<label>Search Terms</label>
<url helper=”catalogsearch/getSearchTermUrl” />
<title>Search Terms</title>
</action>
<action method=”addLink” translate=”label title” module=”catalogsearch”>
<label>Advanced Search</label>
<url helper=”catalogsearch/getAdvancedSearchUrl” />
<title>Advanced Search</title>
</action>
</reference>

4. Contact Us
-> contacts.xml

<reference name=”footer_links”>
<action method=”addLink” translate=”label title” module=”contacts” ifconfig=”contacts/contacts/enabled”><label>Contact Us</label><url>contacts</url><title>Contact Us</title><prepare>true</prepare></action>
</reference>

5. RSS
-> rss.xml

<reference name=”footer_links”>
<action method=”addLink” translate=”label title” module=”rss” ifconfig=”rss/config/active”><label>RSS</label><url>rss</url><title>RSS testing</title><prepare>true</prepare><urlParams/><position/><li/>
<a>class=”link-feed”</a>
</action>
</reference>

* 2. Site Map, 3. Search Terms | Advanced Search, 4. Contact Us, and 5. RSS are the HTML links pulled by the function “$this->getLinks()”, not by “$this->getChildHtml(name)”. These links are pulled from a few different places as specified above.

6. To edit the default Magento footer message ” Help Us to Keep Magento Healthy - Report All Bugs (ver. 1.2.1.2) © 2007 Magento Demo Store. All Rights Reserved.”, edit the following file.
-> /app/design/frontend/default/default/template/page/html/footer.phtml

7. To edit the copyright notice,
-> /app/design/frontend/default/default/template/page/html/footer.phtml