Rewriting database query for New Products module

[Leeb2]

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]

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]

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]

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.

[Leeb2]

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]

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]

the first query is used for the home page is that what mean? Try using the same query I posted

[Leeb2]

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.