johnson4 Posted October 30, 2006 Share Posted October 30, 2006 Backup and then try: $product_deletion_query = tep_db_query("select products_id from from " . TABLE_PRODUCTS . " where products_status = 0"); while ($product_deletion = tep_db_fetch_array($product_deletion_query)) { tep_db_query("delete from " . TABLE_PRODUCTS . " where products_id = '" . $product_deletion['products_id'] . "'"); tep_db_query("delete from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . $product_deletion['products_id'] . "'"); } You will need to add any other relevant product tables within the while loop e.g. products_attributes, products_description etc... thanks Ill gove it a shot, If I put this in a php file and add a crontab to my server it should keep things all cleaned up. thanks again Quote Link to comment Share on other sites More sharing options...
johnson4 Posted October 30, 2006 Share Posted October 30, 2006 (edited) I made a file called dbclean.php and put this in it <?php require('includes/application_top.php'); $product_deletion_query = tep_db_query("select products_id from from " . TABLE_PRODUCTS . " where products_status = 0"); while ($product_deletion = tep_db_fetch_array($product_deletion_query)) { tep_db_query("delete from " . TABLE_PRODUCTS . " where products_id = '" . $product_deletion['products_id'] . "'"); tep_db_query("delete from " . TABLE_SPECIALS . " where products_id = '" . $product_deletion['products_id'] . "'"); tep_db_query("delete from " . TABLE_REVIEWS . " where products_id = '" . $product_deletion['products_id'] . "'"); tep_db_query("delete from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id = '" . $product_deletion['products_id'] . "'"); tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET . " where products_id = '" . $product_deletion['products_id'] . "'"); tep_db_query("delete from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . $product_deletion['products_id'] . "'"); tep_db_query("delete from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . $product_deletion['products_id'] . "'"); } ?> I tried to run this file with a crontab but it returned Status: 404 Content-type: text/html X-Powered-By: PHP/4.3.2 No input file specified. and if I go to the file with a browser I got this 1064 - You have an error in your SQL syntax near 'from products where products_status = 0' at line 1 select products_id from from products where products_status = 0 [TEP STOP] any idea where I went wrong? Im sorry if I've done something obviously wrong, my php/mySQL skils are pretty low. Thanks again for any help you can give me Edited October 30, 2006 by johnson4 Quote Link to comment Share on other sites More sharing options...
♥yesudo Posted October 30, 2006 Author Share Posted October 30, 2006 My fault - you have from in there twice. Quote Your online success is Paramount. Link to comment Share on other sites More sharing options...
johnson4 Posted October 30, 2006 Share Posted October 30, 2006 hahaa ya thats the kinda thing that would have me baffled for hours on end, thanks a ton, it's working great now Quote Link to comment Share on other sites More sharing options...
deansmar Posted August 17, 2011 Share Posted August 17, 2011 i think this contribution is fantastic, and has tought me a few things about SQL databases i am still running a v2.2rc2a shop and insted of deleting all of the Orders i would just like to delet Old Orders would this code do the trick..?? thanks for your time.. :thumbsup: <?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!") ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.