Jump to content

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


Recommended Posts

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`;

  • Like 3
  • Sad 1
Link to comment
Share on other sites

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 ;-)
Link to comment
Share on other sites

@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.

Link to comment
Share on other sites

  • 2 months later...

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!

Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • 4 months later...

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

Link to comment
Share on other sites

  • 4 weeks later...
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!
  • Like 1
Link to comment
Share on other sites

  • 3 months later...
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.
Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

@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....

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

@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...

Link to comment
Share on other sites

  • 2 months later...

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

Link to comment
Share on other sites

  • 3 weeks later...
  • 2 weeks later...
  • 3 months later...
  • 1 month later...

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

Link to comment
Share on other sites

  • 3 months later...

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

Link to comment
Share on other sites

  • 2 months later...

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.

  • Like 1
Link to comment
Share on other sites

  • 3 months later...
  • 1 month later...
  • 1 month later...

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

  • Like 1
Link to comment
Share on other sites

  • 4 weeks later...
  • 2 weeks later...
  • 1 month later...
  • 10 months later...
  • 2 weeks later...

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

Link to comment
Share on other sites

  • 3 months later...

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;

Link to comment
Share on other sites

@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?

Link to comment
Share on other sites

  • 3 months later...
  • 1 year later...
  • 1 year later...

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
Link to comment
Share on other sites

  • 4 years later...
On 12/16/2008 at 1:46 AM, Bruno Leveque said:

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`;
 

 

Hello,

Instead of entering the codes above, you can go to the say ps_customer in phpmyadmin & go to "MORE" at top right. Then "OPERATIONS".

At Operation Page at lowest part click on the red line: "Empty the table (TRUNCATE)" & OK it. It is DONE!

Link to comment
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
×
×
  • Create New...