Leeb2 Posted February 8, 2007 Posted February 8, 2007 For the New Products module on the index page, I want products from some categories and not other categories to be displayed. I think it is one or both of the below database queries that selects the products to display, from new_products.php. Say the categories I want to use are categories_id 1 and 2 in the database. if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) { $new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); } else { $new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); } How should I rewrite these queries?
Guest Posted February 8, 2007 Posted February 8, 2007 you change the query in the else statement to: $new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (p2c.categories_id = '1' or p2c.categories_id = '2') and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); But its better to write some code for the admin part to select such special categories.
Leeb2 Posted February 8, 2007 Author Posted February 8, 2007 you change the query in the else statement to: $new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (p2c.categories_id = '1' or p2c.categories_id = '2') and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); But its better to write some code for the admin part to select such special categories. Thanks. However why is it better to do it the other way? What is the trouble with this way? Also I don't understand what you mean by writing code for the admin part.
Velveeta Posted February 8, 2007 Posted February 8, 2007 Thanks. However why is it better to do it the other way? What is the trouble with this way? Also I don't understand what you mean by writing code for the admin part. For manageability and scalability... If you found in the future that you wanted to include or exclude additional categories from that filter, you could just use your admin to toggle them on or off, otherwise, you'd have to edit that query everytime you found a new category to include... Richard. Richard Lindsey
Leeb2 Posted February 8, 2007 Author Posted February 8, 2007 For manageability and scalability... If you found in the future that you wanted to include or exclude additional categories from that filter, you could just use your admin to toggle them on or off, otherwise, you'd have to edit that query everytime you found a new category to include... Richard. Thank you.
Leeb2 Posted February 8, 2007 Author Posted February 8, 2007 you change the query in the else statement to: $new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (p2c.categories_id = '1' or p2c.categories_id = '2') and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS); But its better to write some code for the admin part to select such special categories. How would the first query go, the first one in the if else statement, since that is the one my site uses?
Guest Posted February 9, 2007 Posted February 9, 2007 the first query is used for the home page is that what mean? Try using the same query I posted
Leeb2 Posted February 9, 2007 Author Posted February 9, 2007 the first query is used for the home page is that what mean? Try using the same query I posted Yes, I mean the first query, the one used for the home page. That is the one I need to change. The one you gave me is the second one. I'm not sure what that is used for. Replacing the first query with the changed second query doesn't work though.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.