v1.50 - Fresh installation

Be sure to backup your database before running any scripts against your zen cart database.

After extensive testing on the zen cart I was preparing for my customer, I noticed we had accrued hundreds of test orders and several fake (test) customer accounts now existed in the database.

Now, when I am ready to launch the site for the customer, I needed to flush the database of orders and reset the counters as well as drop all the test customers from the database, so it was clean of false orders and fake customer accounts.

After careful analysis, I came up with this script which works for my needs, and I share it for anyone who is looking to perform a mysql script to reset the orders and customers as well as zero out the counter for their store.

Be sure to backup your database before running any scripts against your zen cart database.

Also, the table names may be different from yours, as these table names do no use the zc_ prefix, so you may need to edit this script to match your prefix settings on your tables.

I would reccommend executing this script from the mysql server, or in PhpMyAdmin and not in the SQL patch function in the administration tool.

Be sure to backup your database before running any scripts against your zen cart database.

The script will perform the following actions:


  • DELETE customers accounts created before 2012-10-27
  • DELETE orders placed before 2012-10-27
  • DELETE counter_history records before 2012-10-27
  • Set the counter start date to 2012-10-27
  • Set the hit counter to 0



Oh, and I almost forgot to give you the most important part of this share:

Be sure to backup your database before running any scripts against your zen cart database.


I hope this helps anyone who can use this script.

Code:
--
-- Delete customers FROM database, where the account creation date is before the launch date 2012-10-27
--

DELETE FROM customers WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created < '2012-10-27');
DELETE FROM address_book WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
DELETE FROM customers_basket WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
DELETE FROM customers_basket_attributes WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
DELETE FROM products_notifications WHERE customers_id IN (select customers_info_id FROM customers_info WHERE customers_info_date_account_created > '2012-10-27');
DELETE FROM customers_info WHERE customers_info_date_account_created > '2012-10-27';

--
-- Delete all orders dated before October 27, 2012
--

DELETE orders_total FROM orders_total
INNER JOIN orders ON orders.orders_id = orders_total.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders_status_history FROM orders_status_history
INNER JOIN orders ON orders.orders_id = orders_status_history.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders_products_download FROM orders_products_download
INNER JOIN orders ON orders.orders_id = orders_products_download.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders_products_attributes FROM orders_products_attributes
INNER JOIN orders ON orders.orders_id = orders_products_attributes.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders_products FROM orders_products
INNER JOIN orders ON orders.orders_id = orders_products.orders_id
WHERE orders.date_purchased <= '2012-10-27';

DELETE orders FROM orders WHERE date_purchased <= '2012-10-27';

--
--  Reset Statistics
--  Hit Counter Started = 20121027 (launch Date) / Or current date?
--  Hit Counter = 0
--

DELETE counter_history from counter_history where startdate < '20121027';
UPDATE counter set startdate = '20121027', counter='0';