DB Manipulation from Admin

[johnson4 0]

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

[johnson4 0]

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

[♥yesudo 1]

My fault - you have from in there twice.

[johnson4 0]

hahaa ya thats the kinda thing that would have me baffled for hours on end,

thanks a ton, it's working great now

[deansmar 1]

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!") 

?>