Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Rewriting database query for New Products module


Leeb2

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...