Is there a way to purge order statistics?

[GPMaina]

Hi - I'm new to the forum, but have had pretty good experience so far installing osCMax, customizing it to my liking, populating the catalog and getting the payment module properly linked to PayPal. So far everything appears to be working properly.

But in the process of testing with PayPal a number of test orders were created in osCMax and payment "executed" in PayPal's Sandbox. I have since deleted these orders in osCMax, but the shopping cart is still ranking "BestSellers" even though all the test orders have been deleted.

Is there a way to purge ALL the test transaction activity before taking the shopping cart live? I'd like to be able to report only "real" activity.

Thanks...

[pgmarshall]

Yes! Just remove the data from the database.

This is from an oscommerce contribution enter through MySQL.

WARNING - DO NOT SIMPLY CUT AND PASTE - THIS WILL EMPTY YOUR STORE OF PRODUCTS, ATTRIBUTES, CUSTOMERS, MANUFACTURERS, ETC.

Backup your database before you use - and think it through before wiping your data.

TRUNCATE `address_book`;
TRUNCATE `categories`;
TRUNCATE `categories_description`;
TRUNCATE `customers`;
TRUNCATE `customers_basket`;
TRUNCATE `customers_basket_attributes`;
TRUNCATE `customers_info`;
TRUNCATE `manufacturers`;
TRUNCATE `manufacturers_info`;
TRUNCATE `orders`;
TRUNCATE `orders_products`;
TRUNCATE `orders_products_attributes`;
TRUNCATE `orders_products_download`;
TRUNCATE `orders_total`;
TRUNCATE `products`;
TRUNCATE `products_attributes`;
TRUNCATE `products_attributes_download`;
TRUNCATE `products_description`;
TRUNCATE `products_notifications`;
TRUNCATE `products_options`;
TRUNCATE `products_options_values`;
TRUNCATE `products_options_values_to_products_options`;
TRUNCATE `products_to_categories`;
TRUNCATE `reviews`;
TRUNCATE `reviews_description`;
TRUNCATE `specials`;

There are probably a few more tables that need to be TRUNCATE for osCmax but this should do most of your data ... be warned that it will wipe your products.

GPMaina - More specifically for you I would start with TRUNCATE of the orders tables (eg. TRUNCATE `orders_products`; TRUNCATE `orders_products_attributes`; TRUNCATE `orders_products_download`; TRUNCATE `orders_total`;) and customers eg. (TRUNCATE `customers`;
TRUNCATE `customers_basket`; TRUNCATE `customers_basket_attributes`; TRUNCATE `customers_info`;)

Backup before you start and be careful.

Regards,

[GPMaina]

Thanks, pgmarshall:

Could I accomplish the same thing by deleting the records manually using phpadmin in the following tables?

`orders`
`orders_products`
`orders_products_attributes`
`orders_products_download`
`orders_total`

I really don't want to lose the customer, product, mfg, etc. records, just the test orders and statistics.

Also, is the data in these 'orders' tables in any way related to some of the other tables such that the latter might crash if the records in the 'orders' tables are deleted manually?

Or do I have to completely purge EVERYTHING to be safe?

Thanks, again.

[pgmarshall]

Having not done this myself - I am guessing - so it might be worth waiting for someone who has done this to confirm the TRUNCATE method.

I can think of no reason why you couldn't just delete the rows using myPhpAdmin or why this would cause it to crash later. TRUNCATE also reset the auto-numbering in the table back to 1.

I would back everything up and try the following SQL - if the wheels fall off and your site bombs then you can simply restore the dbase.

 
TRUNCATE `address_book`;
TRUNCATE `orders_products`;
TRUNCATE `orders_products_attributes`;
TRUNCATE `orders_products_download`;
TRUNCATE `orders_total`;
TRUNCATE `customers`;
TRUNCATE `customers_basket`;
TRUNCATE `customers_basket_attributes`;
TRUNCATE `customers_info`;

Regards,

[jpf]

Thanks, pgmarshall:

Could I accomplish the same thing by deleting the records manually using phpadmin in the following tables?

`orders`
`orders_products`
`orders_products_attributes`
`orders_products_download`
`orders_total`

I really don't want to lose the customer, product, mfg, etc. records, just the test orders and statistics.

Also, is the data in these 'orders' tables in any way related to some of the other tables such that the latter might crash if the records in the 'orders' tables are deleted manually?

Or do I have to completely purge EVERYTHING to be safe?

Thanks, again.

Yes RUN the full SQL COMMANDS as above in phpmyadmin on your database.....
ie:

TRUNCATE `address_book`;
TRUNCATE `orders_products`;
TRUNCATE `orders_products_attributes`;
TRUNCATE `orders_products_download`;
TRUNCATE `orders_total`;
TRUNCATE `customers`;
TRUNCATE `customers_basket`;
TRUNCATE `customers_basket_attributes`;
TRUNCATE `customers_info`;

[GPMaina]

To pgmarshall and jpf:

What you instructed worked quite well... and thanks for the advice to backup the database first. I was able to restore it a number of times to double check what was going on.

Actually, I did a little rooting around the database using phpMyAdmin and found that the database didn't show any real orders at all. I am wondering of osCMax uses the PalPal "SandBox" setting as a switch not to record the test orders? Is that possible?

The second thing I found is that by going into the "products" table in phpMyAdmin I could edit the "products_quantity" and "products_ordered" fields to reset them to 0. And by going into the "products_description" table, I could edit the "products_viewed" fields to 0 as well. This gave me the results I wanted by resetting the Best Selling Products and Number of Times Viewed back to zero before letting the shopping cart go live.

Of course the "products_quantity" could have been edited in the Catalog Admin right in osCMax... there does not appear to be a way to edit the "products_ordered" nor the "products_viewed" except directly in the database.

Thanks again for leading me to the right area to investigate.

[GPMaina]

One last (I hope) question on this topic... after all my order processing testing, I was able to purge all the test order information and reset the products ordered and products viewed fields to zero. But the OrderID field continue to increment from the last test order #. Can you tell me exactly which table contains the Order ID increment field so I can reset that to zero as well and start with a new Order #1?

Thanks...

[pgmarshall]

Open your dbase in myPhpAdmin or similar and run the following SQL.

ALTER TABLE orders AUTO_INCREMENT = 1

This will reset the auto_increment to the lowest free available number.

I am not sure, but it may be worth doing this for all of the orders_ tables ie. orders_products, orders_products_attributes, etc.

Hope this works - I have not tried it or tested it - so make sure you BACKUP!

Regards,

[ridexbuilder]

Consider this:
Do you really want your 1st 'live' customer to know that they are order number one?
"Oh no, what have I done, is this site legit, or have I just become a guinea pig?"
Would a starting order of, say, 500 or 1000 not instil a bit more confidence?

[GPMaina]

Excellent point - thanks.

George