Guest Posted July 27, 2003 Posted July 27, 2003 Hopefully this makes sense. What happened was one of my suppliers went under. So I had to delete 2000 products, which was all one category and subcategories. Well OSC died part way through, guess that was a bit much. I was able to go in and use Phpmyadmin and delete then from the products table because I use the model number and have 2 or 3 letter code in the begining to let me know who the supplier is. Problem is now that I have 2000 descriptions that dont match a product. Site is running fine but I really rather have everthing clean. Sql is not my best thing. What I need to know is a query that basically looks at products_description and matches it to a products, if no match, delete. Any help would be appreciated. Chris
Rumble Posted July 27, 2003 Posted July 27, 2003 Hi there, This should be what you're looking for. Save the following code as delete.php and place in the admin folder Then open the file online in your web browser and the query will automatically run and all your product descriptions with no corresponding products table entry will be deleted!!! eg http://www.yoursite.com/admin/delete.php This is a permanent deletion so I advise you back up your database first just in case!!! <?php /* $Id: delete.php,v 1.146 2003/07/11 14:40:27 hpdl Exp $ osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright (c) 2003 osCommerce Released under the GNU General Public License */ require('includes/application_top.php'); ?> <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> <head> <meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>"> <title><?php echo TITLE; ?></title> <link rel="stylesheet" type="text/css" href="includes/stylesheet.css"> </head> <body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF"> <!-- header //--> <?php require(DIR_WS_INCLUDES . 'header.php'); ?> <!-- header_eof //--> <!-- body //--> <table border="0" width="100%" cellspacing="2" cellpadding="2"> <tr> <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft"> <!-- left_navigation //--> <?php require(DIR_WS_INCLUDES . 'column_left.php'); ?> <!-- left_navigation_eof //--> </table></td> <!-- body_text //--> <td width="100%" valign="top"> <?php $is_there_query = tep_db_query("select pd.products_id from " . TABLE_PRODUCTS_DESCRIPTION . " pd"); while ($is_there = tep_db_fetch_array($is_there_query)) { if ($is_there['products_id'] > 0) { $delete_blank_query = tep_db_query("select p.products_id from " . TABLE_PRODUCTS . " p where p.products_id = '" . (int)$is_there['products_id'] . "'"); $delete_blank = tep_db_fetch_array($delete_blank_query);# $remove = $is_there['products_id']; if ($is_there['products_id'] = $delete_blank['products_id']) { echo ''; } else { tep_db_query("delete from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id = '" . $remove . "'"); } } } echo '<br><br><center><h1>Your redundant product descriptions have been removed!</h1></center>'; ?> </td> <!-- body_text_eof //--> </tr> </table> <!-- body_eof //--> <!-- footer //--> <?php require(DIR_WS_INCLUDES . 'footer.php'); ?> <!-- footer_eof //--> <br> </body> </html> <?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?> Once the deletion process has finished you will be shown a message Your redundant product descriptions have been removed! But remember this might take a while if you have 2000 entries to delete!! Good luck, let me know how it goes! Reddy to Rumble Thank you osCommerce and all who Contribute to her!
Guest Posted July 27, 2003 Posted July 27, 2003 Actaully it only took about 20 seconds to run and now both products and product_descriptions read 5643. Thats been driving me nuts for a month now. Thanks Alot! :D
Guest Posted July 27, 2003 Posted July 27, 2003 Actually also noticed products_to_categories needed fixing, so for anyone else who may run it to this, just take the above script and change TABLE_PRODUCTS_DESCRIPTION to TABLE_PRODUCTS_TO_CATEGORIES and that will get cleaned up too. Thanks again Rumble.
New 
Recommended Posts
Archived
This topic is now archived and is closed to further replies.