Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Empty a database


Daemonj

Recommended Posts

If you need to reset a database to empty (no products but keeping the configuration settings), then take the following and paste it into a PHPMyAdmin sql window and you should be good to go.

 

DELETE FROM `address_book`; 

DELETE FROM `banners`; 

DELETE FROM `banners_history`; 

DELETE FROM `categories`; 

DELETE FROM `categories_description`; 

DELETE FROM `counter`; 

DELETE FROM `customers`; 

DELETE FROM `customers_basket`; 

DELETE FROM `customers_info`; 

DELETE FROM `manufacturers`; 

DELETE FROM `manufacturers_info`; 

DELETE FROM `orders`; 

DELETE FROM `orders_products`; 

DELETE FROM `orders_status_history`; 

DELETE FROM `orders_total`; 

DELETE FROM `products`; 

DELETE FROM `products_attributes`; 

DELETE FROM `products_attributes_download`; 

DELETE FROM `products_description`; 

DELETE FROM `products_notifications`; 

DELETE FROM `products_options`; 

DELETE FROM `products_options_values`; 

DELETE FROM `products_options_values_to_products_options`; 

DELETE FROM `products_to_categories`; 

DELETE FROM `reviews`; 

DELETE FROM `reviews_description`; 

DELETE FROM `sessions`; 

DELETE FROM `specials`; 

DELETE FROM `whos_online`; 



ALTER TABLE banners AUTO_INCREMENT = 1; 

ALTER TABLE banners_history AUTO_INCREMENT = 1; 

ALTER TABLE categories AUTO_INCREMENT = 1; 

ALTER TABLE customers AUTO_INCREMENT = 1; 

ALTER TABLE customers_basket AUTO_INCREMENT = 1; 

ALTER TABLE customers_basket_attributes AUTO_INCREMENT = 1; 

ALTER TABLE manufacturers AUTO_INCREMENT = 1; 

ALTER TABLE manufacturers_info AUTO_INCREMENT = 1; 

ALTER TABLE orders AUTO_INCREMENT = 1; 

ALTER TABLE orders_products AUTO_INCREMENT = 1; 

ALTER TABLE orders_status_history AUTO_INCREMENT = 1; 

ALTER TABLE orders_total AUTO_INCREMENT = 1; 

ALTER TABLE products AUTO_INCREMENT = 1; 

ALTER TABLE products_attributes AUTO_INCREMENT = 1; 

ALTER TABLE products_description AUTO_INCREMENT = 1; 

ALTER TABLE products_options_values_to_products_options AUTO_INCREMENT = 1; 

ALTER TABLE reviews AUTO_INCREMENT = 1; 

ALTER TABLE specials AUTO_INCREMENT = 1;

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Link to comment
Share on other sites

Glad to be of help. :)

 

Another alternative would be to copy and paste the information into a file called reset_db.sql and simply load that file every time you needed to clean a database.

"Great spirits have always found violent opposition from mediocre minds. The latter cannot understand it when a man does not thoughtlessly submit to hereditary prejudices but honestly and courageously uses his intelligence." - A. Einstein

Link to comment
Share on other sites

  • 5 months later...
  • 3 months later...

Hi there,

 

I want to delete all the orders from my database, but keep all my customer and product information.

 

Is there a simple way to do this?

 

thanks

 

Chris

Link to comment
Share on other sites

DELETE FROM `orders`;
DELETE FROM `orders_products`;
DELETE FROM `orders_status_history`;
DELETE FROM `orders_total`;

ALTER TABLE orders AUTO_INCREMENT = 1;
ALTER TABLE orders_products AUTO_INCREMENT = 1;
ALTER TABLE orders_status_history AUTO_INCREMENT = 1;
ALTER TABLE orders_total AUTO_INCREMENT = 1;

are the lines that deal with orders.

 

Hth,

Matt

Always back up before making changes.

Link to comment
Share on other sites

  • 3 months later...

Ok I ran the code

 

DELETE FROM `orders`;
DELETE FROM `orders_products`;
DELETE FROM `orders_status_history`;
DELETE FROM `orders_total`;

ALTER TABLE orders AUTO_INCREMENT = 1;
ALTER TABLE orders_products AUTO_INCREMENT = 1;
ALTER TABLE orders_status_history AUTO_INCREMENT = 1;
ALTER TABLE orders_total AUTO_INCREMENT = 1;

 

but now my orders do not appear in the admin under orders. They do show as pending, but they are not being in the orders_total table. The email notification is correct, but the order does not show up the user account either. Could I have a corrupted file or two??

Confused worm farmer looking for assistance.

Link to comment
Share on other sites

with you running delete from orders, orders_products, etc you have emptied the tables holding the info . . .

 

restore from your backup prior to running the delete (umm, you did backup didnt you) and then post what it is you are trying to do in the support areas of the forum

Link to comment
Share on other sites

Ya I've got a complete back up. I empty the tables, as I am using this database on a new site, than ran a fresh order. The fresh order is not appearing in the aforementioned areas.

Confused worm farmer looking for assistance.

Link to comment
Share on other sites

  • 7 months later...
  • 1 year later...
  • 2 months later...

Hi:

 

I am not clear on how to do this. What file do I need to open and edit/reset/change?

 

DELETE FROM `orders`;
DELETE FROM `orders_products`;
DELETE FROM `orders_status_history`;
DELETE FROM `orders_total`;

ALTER TABLE orders AUTO_INCREMENT = 1;
ALTER TABLE orders_products AUTO_INCREMENT = 1;
ALTER TABLE orders_status_history AUTO_INCREMENT = 1;
ALTER TABLE orders_total AUTO_INCREMENT = 1;

are the lines that deal with orders.

 

Hth,

Matt

Link to comment
Share on other sites

  • 3 months later...
  • 2 weeks later...
And hoe could i completely empty the database. Please let me know someone. I am trying to find out that for about 3 hrs now.

 

I know now, thank you myself for help.

Link to comment
Share on other sites

  • 1 month later...

I would like to ONLY empty the existing customers and the orders, but I want to keep everything else including the product database, and all other configurations. what SQL would I run to empty the orders and customers tables?

 

Scott

Link to comment
Share on other sites

  • 3 months later...

quick question. If I wanted to delete the column pertaining to credit card numbers.

 

would I type:

 

DELETE FROM orders

WHERE cc_numbers *

 

 

would that purge all credit card numbers from my database? And allow me to retain all other customers information pertaining to orders and addresses?

Link to comment
Share on other sites

quick question. If I wanted to delete the column pertaining to credit card numbers.

 

would I type:

 

DELETE FROM orders

WHERE cc_numbers *

would that purge all credit card numbers from my database? And allow me to retain all other customers information pertaining to orders and addresses?

Make a backup first and better try:

update orders set cc_number = NULL;

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...