Remove test accounts and their associated details cleanly

[Raver]

HI All,

I have tested my shop installation and input test details and customer registrations and such.

Now that I am going live ( wish me luck)..can I remove all the accounts and their associated details, i.e. invoice, orders, registration without affecting the database in any way? What I am after is a database to receive real customers, thus stats and what have you is accurate.

I tried emptying some tables but the shop became unstable as it was trying to access some data that was deleted. Thus, I must have not remove all trace. Thanks to db backups I was up and running again.( Phew)

Which tables can I empty to achieve my outcome..totally clean and empty ready to receive customer no. 1?
Any ideas?

Thx!

[Hessellund]

Hi,

This is the SQL-query I ran to clean it all up:
# Orders (Ordernumbers will restart at 1050)
# Customers
# Statistics (Updated to include reinsertion of special pages)

TRUNCATE ps_orders;
TRUNCATE ps_order_detail;
TRUNCATE ps_order_discount;
TRUNCATE ps_order_history;
TRUNCATE ps_order_return;
TRUNCATE ps_order_return_detail;
TRUNCATE ps_order_slip;
TRUNCATE ps_order_slip_detail;
TRUNCATE ps_message;
TRUNCATE ps_cart;
TRUNCATE ps_cart_discount;
TRUNCATE ps_cart_product;
ALTER TABLE `ps_orders`  AUTO_INCREMENT =1050;

TRUNCATE ps_customer;
TRUNCATE ps_address;

TRUNCATE ps_connections;
TRUNCATE ps_connections_page;
TRUNCATE ps_guest;
TRUNCATE ps_page;
TRUNCATE ps_pagenotfound;
TRUNCATE ps_page_viewed;
TRUNCATE ps_page_type;
TRUNCATE ps_date_range;
INSERT INTO `ps_page_type` (`id_page_type`, `name`)
   VALUES (NULL , 'product.php'), (NULL , 'category.php'), (NULL , 'order.php'), (NULL , 'manufacturer.php');

[Raver]

Hi Hessellund,

Thx, appreciate your time n for the detailed SQL-queries.

Have a Nice Day!

[Hessellund]

I just found that my catelog stats were not working.

After truncating we have to reinsert some special files into the database.

Statistics resetting:

TRUNCATE ps_connections;
TRUNCATE ps_connections_page;
TRUNCATE ps_guest;
TRUNCATE ps_page;
TRUNCATE ps_pagenotfound;
TRUNCATE ps_page_viewed;
TRUNCATE ps_page_type;
TRUNCATE ps_date_range;
INSERT INTO `ps_page_type` (`id_page_type`, `name`)
   VALUES (NULL , 'product.php'), (NULL , 'category.php'), (NULL , 'order.php'), (NULL , 'manufacturer.php');

[ukbaz]

Hi - just found my stats weren't working either - tried your code as above and got the following error:

#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 'TRUNCATE ps_customer' at line 3

I really need to get the stats working for the site.

Any Idea why I get the error?

Thanks

Baz

[Hessellund]

The SQl-error is because of a missing ; after 1050. (I updated the post).

To only reset stats, you should use the sql in my post #3.

The fix for me, was the INSERT lines.

[geiri]

Hi, Hessellund
What if you would like to remove all products and orders and start with a fresh shop?

[Hessellund]

Hi geiri

You could drop all tables in your database and do a fresh install by going to the install folder.

The install procedure will then recreate the tables needed.

[exadra37]

The best way is to follow the link in my signature :coolsmile:

[mhu100]

The best way is to follow the link in my signature :coolsmile:

Is there one in english also?

[exadra37]

Not yet but see the pictures and copy the code and paste in phpMyAdmin in tab SQL .

[geiri]

Thank you Exadra37

You can get it translated with google

http://translate.google.com/translate?prev=hp&hl=is&js=y&u=http://forum.prestashopbr.com/viewtopic.php?f=10&t=788&sl=auto&tl=en&history;_state0=

[exadra37]

I am very busy doing my shop, resolving problems, being moderator in Portuguese comunity , but i will do a topic in English when a have a little break.

Mean while the people can use the google translator to resolve the problem.

[mhu100]

I am very busy doing my shop, resolving problems, being moderator in Portuguese comunity , but i will do a topic in English when a have a little break.

Mean while the people can use the google translator to resolve the problem.

Please no stress haha. Googletool will do. I was just wondering if there was one in english because the translation by google aren't always the right ones.
Thanks for writing the tutorial.

[bijububu]

TRUNCATE `ps_orders`;
TRUNCATE `ps_order_customization_return`;
TRUNCATE `ps_order_detail`;
TRUNCATE `ps_order_discount`;
TRUNCATE `ps_order_history`;
TRUNCATE `ps_order_message`;
TRUNCATE `ps_order_message_lang`;
TRUNCATE `ps_order_return`;
TRUNCATE `ps_order_return_detail`;
TRUNCATE `ps_order_return_state`;
TRUNCATE `ps_order_return_state_lang`;
TRUNCATE `ps_order_slip`;
TRUNCATE `ps_order_slip_detail`;
TRUNCATE `ps_message`;
TRUNCATE `ps_cart`;
TRUNCATE `ps_cart_product`;
TRUNCATE `ps_customer`;
TRUNCATE `ps_address`;
TRUNCATE `ps_cart_discount`;
TRUNCATE `ps_connections;
TRUNCATE `ps_connections_page;
TRUNCATE `ps_guest;
TRUNCATE `ps_pagenotfound;
TRUNCATE `ps_page_viewed;