Jump to content
  • 0
mcfreedom

[Solved] How to reset or delete all orders and customers?

Question

45 answers to this question

Recommended Posts

  • 0

Hi,

You can delete customers via your admin panel, you can also truncate MySQL tables:

TRUNCATE TABLE `ps_customer`;
TRUNCATE TABLE `ps_address`;
TRUNCATE TABLE `ps_orders`;
TRUNCATE TABLE `ps_order_detail`;
TRUNCATE TABLE `ps_order_discount`;
TRUNCATE TABLE `ps_order_history`;
TRUNCATE TABLE `ps_message`;
TRUNCATE TABLE `ps_cart`;
TRUNCATE TABLE `ps_cart_product`;
TRUNCATE TABLE `ps_cart_discount`;

Share this post


Link to post
Share on other sites
  • 0
Hi,

You can delete customers via your admin panel, you can also truncate MySQL tables:

TRUNCATE TABLE `ps_customer`;
TRUNCATE TABLE `ps_address`;
TRUNCATE TABLE `ps_orders`;
TRUNCATE TABLE `ps_order_detail`;
TRUNCATE TABLE `ps_order_discount`;
TRUNCATE TABLE `ps_order_history`;
TRUNCATE TABLE `ps_message`;
TRUNCATE TABLE `ps_cart`;
TRUNCATE TABLE `ps_cart_product`;
TRUNCATE TABLE `ps_cart_discount`;



Will the ordernumber ect. start with 000001 again because I think this is what most of us want after testing ;-)

Share this post


Link to post
Share on other sites
  • 0

@TRUTHSEEKER

When your in phpMyAdmin, on the left side choose the database you want to truncate. Now you get a diferent window an you have tabs on the top of the page... Click on the SQL tab (second from the left?) and paste the code there and click run or execute (bottom right corner).

Hope this helps.

Share this post


Link to post
Share on other sites
  • 0

Is it possible to just delete the sample order? I already have about 10 orders, and don't want to reset as I will loose this information. I need to just remove the sample order so that the stats in my store gives the correct information. Please help! Thank you!

Share this post


Link to post
Share on other sites
  • 0
Is it possible to just delete the sample order? I already have about 10 orders, and don't want to reset as I will loose this information. I need to just remove the sample order so that the stats in my store gives the correct information. Please help! Thank you!


If you would have used the search you would have know you need to do that directly in your database via phpMyAdmin.

Share this post


Link to post
Share on other sites
  • 0

Thanks for your help, don't know what I would have done without it!

Is it possible to just delete the sample order? I already have about 10 orders, and don't want to reset as I will loose this information. I need to just remove the sample order so that the stats in my store gives the correct information. Please help! Thank you!


If you would have used the search you would have know you need to do that directly in your database via phpMyAdmin.

Share this post


Link to post
Share on other sites
  • 0

Got it, here's how you do it, you need this code... ...you need to be logged in too.

yourshop.fr/your-admin-folder/index.php?tab=AdminOrders&id_order=1&deleteorder;&token;=

Now, go on to the orders page and open the sample order, the link will be something like

http://bluecowcables.com/Haybail/index.php?tab=AdminOrders&id_order=1&vieworder&token;=f62930bu9214d96112339x567c0e9kq

after putting your site address and admin folder into my link, you need to cut and paste the security token from your order onto your link and then cut and paste the whole thing into your browser.

You should have this...

yourshop.fr/your-admin-folder/index.php?tab=AdminOrders&id_order=1&deleteorder&token;=f62930bu9214d96112339x567c0e9kq

You can change the "&id;_order=" value to the number of the order you wish to delete.


I hope this helps,
John Perkin
www.bluecowcables.com

Share this post


Link to post
Share on other sites
  • 0
Got it, here's how you do it, you need this code... ...you need to be logged in too.

yourshop.fr/your-admin-folder/index.php?tab=AdminOrders&id_order=1&deleteorder;&token;=

Now, go on to the orders page and open the sample order, the link will be something like

http://bluecowcables.com/Haybail/index.php?tab=AdminOrders&id_order=1&vieworder;&token;=f62930bu9214d96112339x567c0e9kq

after putting your site address and admin folder into my link, you need to cut and paste the security token from your order onto your link and then cut and paste the whole thing into your browser.

You should have this...

yourshop.fr/your-admin-folder/index.php?tab=AdminOrders&id_order=1&deleteorder;&token;=f62930bu9214d96112339x567c0e9kq

You can change the "&id;_order=" value to the number of the order you wish to delete.


I hope this helps,
John Perkin
www.bluecowcables.com


or just replace vieworder by deleteorder ;)
it works!

Share this post


Link to post
Share on other sites
  • 0
Hi,

You can delete customers via your admin panel, you can also truncate MySQL tables:

TRUNCATE TABLE `ps_customer`;
TRUNCATE TABLE `ps_address`;
TRUNCATE TABLE `ps_orders`;
TRUNCATE TABLE `ps_order_detail`;
TRUNCATE TABLE `ps_order_discount`;
TRUNCATE TABLE `ps_order_history`;
TRUNCATE TABLE `ps_message`;
TRUNCATE TABLE `ps_cart`;
TRUNCATE TABLE `ps_cart_product`;
TRUNCATE TABLE `ps_cart_discount`;



After I run this SQL command, all my data is succesfully deleted. But somehow, I can't create any customer again.

Anybody can help?

the message is:
There is 1 error :

1. an error occurred while creating your account


ps: I still run for my test site, so the customer was created by myself.

Share this post


Link to post
Share on other sites
  • 0

kesit: It's likely the script had an error, Try running it again and this time note if it has an error. If so, post that back here.


Sekuta was correct, it's far easier to change vieworder to deleteorder. I didn't understand him at first, so I'll make it real simple to help others out there:
1) Go to the Orders Tab
2) Click on an order
3) In your browser's address bar, you now have an address like this: http://www.yoursite.com/yourpath/youradminfolder/index.php?tab=AdminOrders&id_order=2&vieworder;&token;=8easdfc42a4c9176s8df7a1fe919ec65bc5
4) See vieworder in that address I showed you? Simply find that in your address bar and change vieworder to deleteorder and then press enter. Order is now gone!

Note: This forum adds semicolons (;) to the path I pasted here. Don't let that mess you up. There should be no ; in the address bar path.

Share this post


Link to post
Share on other sites
  • 0

@kyferez: may i know which script do you suggest to run again? is it the sql or creating account module?

if it's creating account modul, i already tried to run it again and again...but nothing change, still "an error occurred while creating your account" message

if it's the sql command, nothing faulty message:
TRUNCATE TABLE `ps_customer`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_address`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_orders`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_order_detail`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_order_discount`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_order_history`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_message`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_cart`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_cart_product`;# MySQL returned an empty result set (i.e. zero rows).

TRUNCATE TABLE `ps_cart_discount`;# MySQL returned an empty result set (i.e. zero rows).


If it help with a clue, when i try to register...this will be happened:
- ps_customer will successfully create data
- ps_cart will create data, but with wrong id_customer and/or id_guest


hope it'll help you to help me.....

thanks for reply

and sorry for thread starter since i just seem to hack your topic....

Share this post


Link to post
Share on other sites
  • 0

I seriously doubt this will fix it, but it's worth a try:

TRUNCATE TABLE `ps_customer`;
TRUNCATE TABLE `ps_address`;
TRUNCATE TABLE `ps_orders`;
TRUNCATE TABLE `ps_order_detail`;
TRUNCATE TABLE `ps_order_discount`;
TRUNCATE TABLE `ps_order_history`;
TRUNCATE TABLE `ps_message`;
TRUNCATE TABLE `ps_cart`;
TRUNCATE TABLE `ps_cart_product`;
TRUNCATE TABLE `ps_cart_discount`;
ALTER TABLE `ps_customer` AUTO_INCREMENT = 0;
ALTER TABLE `ps_address` AUTO_INCREMENT = 0;
ALTER TABLE `ps_orders` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_detail` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_discount` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_history` AUTO_INCREMENT = 0;
ALTER TABLE `ps_message` AUTO_INCREMENT = 0;
ALTER TABLE `ps_cart` AUTO_INCREMENT = 0;
ALTER TABLE `ps_cart_product` AUTO_INCREMENT = 0;
ALTER TABLE `ps_cart_discount` AUTO_INCREMENT = 0;


If this still doesn't work, start a new thread stating what you did and the results so this one doesn't get too hijacked.

Share this post


Link to post
Share on other sites
  • 0

@kyferez...thanks for your help. unfortunately it doesn't fix my problem....but it's really give me hope..

i already post a new thread about this (if someone read this and think know the problem): http://www.prestashop.com/forums/viewthread/39109/help_installation___upgrade/unsolved_cant_add_new_customer_after_clearing_customer_database

--------edit-----------

thanks,
my problem is solved...
i have to add 1 more sql command:

TRUNCATE TABLE `ps_customer_group`;


thanks everyone...

Share this post


Link to post
Share on other sites
  • 0

I used the code here on in this thread and had the same problem. Googled and found a solution: truncate customer_group table. Hope it works for you to.

TRUNCATE TABLE `ps_customer_group`

Share this post


Link to post
Share on other sites
  • 0

Hi, sorry to hijack this thread, but i have a problem.

I had set up a few test customer accounts and when i was deleting them, i accidentally deleted a proper customers account.

Now when i try to add that customer back it keeps telling me that the email address has already been registered, is there any way of adding this customer back?

The account seems to be still there in the BO>Customers>Addresses tab and also the BO>Customers>Groups tab and also there is still parts in the BO>Customers>Carts tab aswell, but i cant seem to get the customer added again so they can log in

Share this post


Link to post
Share on other sites
  • 0

if you reset your customer table you should do this as well in order to keep off problems with account creation in the future:

TRUNCATE TABLE `ps_customer_group`;

Share this post


Link to post
Share on other sites
  • 0

Hi friends!

One solution working, in 3 easy steps ;-).

1.- Make a prestashop clear installation, without any examples

2.- Save a sql with only the tables that we need:

ps_address;
ps_cart;
ps_cart_discount;
ps_cart_product;
ps_customer;
ps_customer_group;
ps_message;
ps_orders;
ps_order_detail;
ps_order_discount;
ps_order_history;



3.- At the real shop database, clear the tables, and import the clear SQL.

And that´s all!.

Sometimes, you win more time thinking about, that making error test xdxd.

Bye!

Jose Ramon Leon Somovilla

Share this post


Link to post
Share on other sites
  • 0

VERY IMPORTANT:

In PrestaShop V.1.3.1.1 (which is the one I'm currently using), if you want to reset all your customers and orders info, you need to use the code mentioned above, plus this line:

TRUNCATE TABLE `ps_customer_group`;

Which I believe resets a new table in the DB, which has been in PrestaShop since version 1.3.0. (after the original code in this entry was posted).

Otherwise you will have an error message when creating a new user account either from the frontend of the shop or from the backend (object customer duplicate entry).

So this is the complete (updated) code you'll need to run in your SQL:

TRUNCATE TABLE `ps_customer`;
TRUNCATE TABLE `ps_address`;
TRUNCATE TABLE `ps_orders`;
TRUNCATE TABLE `ps_order_detail`;
TRUNCATE TABLE `ps_order_discount`;
TRUNCATE TABLE `ps_order_history`;
TRUNCATE TABLE `ps_message`;
TRUNCATE TABLE `ps_cart`;
TRUNCATE TABLE `ps_cart_product`;
TRUNCATE TABLE `ps_cart_discount`; 
TRUNCATE TABLE `ps_customer_group`;



Hope this helps :)

Carlos

Share this post


Link to post
Share on other sites
  • 0

To FULLY clean 100% your Prestashop Store v1.4 you need to run this:

TRUNCATE `address`;
TRUNCATE `alias`;
TRUNCATE `cart`;
TRUNCATE `cart_discount`;
TRUNCATE `cart_product`;
TRUNCATE `category_product`;
TRUNCATE `connections`;
TRUNCATE `connections_page`;
TRUNCATE `connections_source`;
TRUNCATE `customer`;
TRUNCATE `customer_group`;
-- TRUNCATE `customer_hash`; My custom
-- TRUNCATE `customer_log`; My custom
TRUNCATE `customer_message`;
TRUNCATE `customer_thread`;
TRUNCATE `delivery`;
TRUNCATE `feature`;
TRUNCATE `feature_lang`;
TRUNCATE `feature_product`;
TRUNCATE `feature_value`;
TRUNCATE `feature_value_lang`;
TRUNCATE `guest`;
TRUNCATE `image`;
TRUNCATE `image_lang`;
-- TRUNCATE `image_type`; Bug, list of types of system images (Thanks to Webplus)
TRUNCATE `manufacturer`;
TRUNCATE `manufacturer_lang`;
TRUNCATE `message`;
TRUNCATE `orders`;
TRUNCATE `order_detail`;
TRUNCATE `order_history`;
TRUNCATE `product`;
TRUNCATE `product_attachment`;
TRUNCATE `product_attribute`;
TRUNCATE `product_attribute_combination`;
TRUNCATE `product_attribute_image`;
TRUNCATE `product_country_tax`;
TRUNCATE `product_download`;
TRUNCATE `product_group_reduction_cache`;
TRUNCATE `product_lang`;
TRUNCATE `product_sale`;
TRUNCATE `product_tag`;
TRUNCATE `range_price`;
TRUNCATE `range_weight`;
TRUNCATE `scene`;
TRUNCATE `scene_category`;
TRUNCATE `scene_lang`;
TRUNCATE `scene_products`;
TRUNCATE `search_index`;
TRUNCATE `search_word`;
TRUNCATE `stock_mvt`;
TRUNCATE `store`;
TRUNCATE `supplier`;
TRUNCATE `supplier_lang`;

Note that if you created any carrier in the store you have to edit the tables 'carrier', 'carrier_group', 'carrier_lang', 'carrier_zone' to leave only the carrier with ID 1. (Because bug edit carrier insert new carrier instead update).

Optionally you can delete all carriers instead leave only ID 1 with this:

TRUNCATE `carrier`;
TRUNCATE `carrier_group`;
TRUNCATE `carrier_lang`;
TRUNCATE `carrier_zone`;

Please report any issues.

Best Regards.

Share this post


Link to post
Share on other sites
  • 0

To FULLY clean 100% your Prestashop Store v1.4 you need to run this:

TRUNCATE `address`;
TRUNCATE `alias`;
TRUNCATE `cart`;
TRUNCATE `cart_discount`;
TRUNCATE `cart_product`;
TRUNCATE `category_product`;
TRUNCATE `connections`;
TRUNCATE `connections_page`;
TRUNCATE `connections_source`;
TRUNCATE `customer`;
TRUNCATE `customer_group`;
-- TRUNCATE `customer_hash`; My custom
-- TRUNCATE `customer_log`; My custom
TRUNCATE `customer_message`;
TRUNCATE `customer_thread`;
TRUNCATE `delivery`;
TRUNCATE `feature`;
TRUNCATE `feature_lang`;
TRUNCATE `feature_product`;
TRUNCATE `feature_value`;
TRUNCATE `feature_value_lang`;
TRUNCATE `guest`;
TRUNCATE `image`;
TRUNCATE `image_lang`;
TRUNCATE `image_type`;
TRUNCATE `manufacturer`;
TRUNCATE `manufacturer_lang`;
TRUNCATE `message`;
TRUNCATE `orders`;
TRUNCATE `order_detail`;
TRUNCATE `order_history`;
TRUNCATE `product`;
TRUNCATE `product_attachment`;
TRUNCATE `product_attribute`;
TRUNCATE `product_attribute_combination`;
TRUNCATE `product_attribute_image`;
TRUNCATE `product_country_tax`;
TRUNCATE `product_download`;
TRUNCATE `product_group_reduction_cache`;
TRUNCATE `product_lang`;
TRUNCATE `product_sale`;
TRUNCATE `product_tag`;
TRUNCATE `range_price`;
TRUNCATE `range_weight`;
TRUNCATE `scene`;
TRUNCATE `scene_category`;
TRUNCATE `scene_lang`;
TRUNCATE `scene_products`;
TRUNCATE `search_index`;
TRUNCATE `search_word`;
TRUNCATE `stock_mvt`;
TRUNCATE `store`;
TRUNCATE `supplier`;
TRUNCATE `supplier_lang`;

Note that if you created any carrier in the store you have to edit the tables 'carrier', 'carrier_group', 'carrier_lang', 'carrier_zone' to leave only the carrier with ID 1. (Because bug edit carrier insert new carrier instead update).

Optionally you can delete all carriers instead leave only ID 1 with this:

TRUNCATE `carrier`;
TRUNCATE `carrier_group`;
TRUNCATE `carrier_lang`;
TRUNCATE `carrier_zone`;

Please report any issues.

Best Regards.

 

 

Thanks. I just wouldn't delete ps_image_type

Share this post


Link to post
Share on other sites
  • 0

Thank you, this worked for me, including the ps_customer_group table. One thing I had to do was get rid of the ' or ` delimiters and make sure you have the semicolon at the end of all the lines.

Share this post


Link to post
Share on other sites
  • 0

Iám using presta 1.4.9, and runing that in database, its not possible to me to login when I using one page chekout.

 

But I can login when I using account login botton.

 

Any adia how to fix that please?

Share this post


Link to post
Share on other sites
  • 0

Hi

 

I am not being able to find the way to reset the invoices # to 0.

 

I think i have deleted all info about the test clients and orders and invoices and credit notes, etc etc but it keeps giving me an error that the next invoice number has to be >6...

 

Using 1.5.1

 

Any ideas?

 

Thanks

Share this post


Link to post
Share on other sites
  • 0

I have the option to view the sample order but not to delete it, I want to get rid of the ipod touch photo from my home page, can someone advise in very simple terms how to do it please, I'm a novice ;)

Share this post


Link to post
Share on other sites
  • 0

I've managed to get complete reset for test orders on 1.5.3.1

 

TRUNCATE TABLE `ps_customer`;
TRUNCATE TABLE `ps_customer_message`;
TRUNCATE TABLE `ps_customer_thread`;
TRUNCATE TABLE `ps_delivery`;
TRUNCATE TABLE `ps_guest`;
TRUNCATE TABLE `ps_loyalty`;
TRUNCATE TABLE `ps_loyalty_history`;
TRUNCATE TABLE `ps_address`;
TRUNCATE TABLE `ps_orders`;
TRUNCATE TABLE `ps_order_carrier`;
TRUNCATE TABLE `ps_order_cart_rule`;
TRUNCATE TABLE `ps_order_detail`;
TRUNCATE TABLE `ps_order_detail_tax`;
TRUNCATE TABLE `ps_order_history`;
TRUNCATE TABLE `ps_order_invoice`;
TRUNCATE TABLE `ps_invoice_payment`;
TRUNCATE TABLE `ps_invoice_tax`;
TRUNCATE TABLE `ps_message`;
TRUNCATE TABLE `ps_cart`;
TRUNCATE TABLE `ps_cart_product`;
TRUNCATE TABLE `ps_paypal_customer`;
TRUNCATE TABLE `ps_paypal_order`;

ALTER TABLE `ps_customer` AUTO_INCREMENT = 0;
ALTER TABLE `ps_customer_message` AUTO_INCREMENT = 0;
ALTER TABLE `ps_customer_thread` AUTO_INCREMENT = 0;
ALTER TABLE `ps_delivery` AUTO_INCREMENT = 0;
ALTER TABLE `ps_guest` AUTO_INCREMENT = 0;
ALTER TABLE `ps_loyalty` AUTO_INCREMENT = 0;
ALTER TABLE `ps_loyalty_history` AUTO_INCREMENT = 0;
ALTER TABLE `ps_address` AUTO_INCREMENT = 0;
ALTER TABLE `ps_orders` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_carrier` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_cart_rule` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_detail` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_detail_tax` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_history` AUTO_INCREMENT = 0;
ALTER TABLE `ps_order_invoice` AUTO_INCREMENT = 0;
ALTER TABLE `ps_invoice_payment` AUTO_INCREMENT = 0;
ALTER TABLE `ps_invoice_tax` AUTO_INCREMENT = 0;
ALTER TABLE `ps_message` AUTO_INCREMENT = 0;
ALTER TABLE `ps_cart` AUTO_INCREMENT = 0;
ALTER TABLE `ps_cart_product` AUTO_INCREMENT = 0;
ALTER TABLE `ps_paypal_customer` AUTO_INCREMENT = 0;
ALTER TABLE `ps_paypal_order` AUTO_INCREMENT = 0;

Share this post


Link to post
Share on other sites
  • 0

@Jero

 

Does this delete only the orders?

 

Any other setting (like carriers, shippings, tax rules etc) I guess that are not altered. But I see u truncate the 'ps_customers'.. so will it delete all customers too?

Share this post


Link to post
Share on other sites
  • 0

orotoi, you can remove lines that delete things you don't want to delete.

 

After 5 years is there really not a way to do this in the back office GUI? Anything having to do with cPanel is SUCH a pain.

Share this post


Link to post
Share on other sites
  • 0

Hi,

 

You can delete customers via your admin panel, you can also truncate MySQL tables:

 

TRUNCATE TABLE `ps_customer`;TRUNCATE TABLE `ps_address`;TRUNCATE TABLE `ps_orders`;TRUNCATE TABLE `ps_order_detail`;TRUNCATE TABLE `ps_order_discount`;TRUNCATE TABLE `ps_order_history`;TRUNCATE TABLE `ps_message`;TRUNCATE TABLE `ps_cart`;TRUNCATE TABLE `ps_cart_product`;TRUNCATE TABLE `ps_cart_discount`;

 

I also have a problem after manually migrating customers and orders to a new install on another domain and server.

 

Everything seems okay, but the Orders tab on backoffice side only display zero for every total of each orders. The Customers list however shows correct totals.

 

Perhaps you could point out something that I missed?

 

These were the migrated tables:

customer*
orders
order_carrier
order_detail
order_detail_tax
order_history
order_invoice
order_invoice_payment
order_payment
paypal_customer
paypal_order

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×