Clean DB / Delete Old Orders

[deansmar]

Hi Folks,

 

i have been searching Hi and Low in this forum, and in the Contributions, a few entries in this forum were found but no one gave a fix or a working contribution for this problem... these are the contributions that i have found...

 

Admin Manipulation last updated 2006

 

Delete old orders last updated 2009

 

Order Controller v1.0 last updated 2008

 

i have been running a online shop for quite some years now, and were now on v2.2 RC2a, thinking of going to v2.3.1

 

the DB is getting rather large, and i would like to clean it up a little, in other words i would like to Bulk delete orders from lets say the year 2000 and also Bulk delete users that have never log in upto a certain year.

 

i would appricate any input that anyone has, wether its a plain SQL skript that would be implemented in the DB or wether some one has one of these contributions working, or for that matter found any other contribution that work..

 

might also be food for thought to be implemented into the Version 3.. ;-)

[deansmar]

i am so sorry that i have stumped all of you with this one...

 

does no one what to have a go..?? not even with the SQL code..!!

 

what does everybody else do with there DB's... let it grow and grow, until its overflowing..!

[deansmar]

i know this would empty my Orders DB...

 

DELETE FROM `orders`;

DELETE FROM `orders_products`;

DELETE FROM `orders_status_history`;

DELETE FROM `orders_total`;

 

but i would like to keep the newer stuff, and just delete the older stuff..

[deansmar]

would anyone like to comment on this code.... would it work..??

 

 

<?php 

require('includes/application_top.php'); 

$strip_date = "2008-07-01 00:00:00";
$orders = tep_db_fetch_all_array(tep_db_query("select orders_id from orders where date_purchased < '$strip_date'"));

$count_stripped = count($orders);

foreach ($orders as $orders_row) {
 $orders_id = $orders_row['orders_id'];
 tep_db_query ("delete from orders_products where orders_id = '$orders_id'");
 tep_db_query ("delete from orders_products_attributes where orders_id = '$orders_id'");
 tep_db_query ("delete from orders_status_history where orders_id = '$orders_id'");
 tep_db_query ("delete from orders_total where orders_id = '$orders_id'");  
 tep_db_query ("delete from orders where orders_id = '$orders_id'");
}

echo ("$count_stripped orders before $strip_date where deleted!")

?>