Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

DB Manipulation from Admin


yesudo

Recommended Posts

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

Link to comment
Share on other sites

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 by johnson4
Link to comment
Share on other sites

  • 4 years later...

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

?>

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...