New Products in Month - only one per category?

[thewitt]

Today if you are displaying 9 new products on the default.php page, and you add 27 products in three categories, you'll get the last 9 added.

 

If you add the products by category, then your New listing will only include products from one category.

 

It would be nice to have this logic do something like displaying the last 9 products but not if they are all in the same category - or no more than X from one category, or some other form of grouping. Simply displaying the last 9 makes it awkward when adding products to have this list mean anything.

 

You would probably want to limit the total rows processed to something reasonable, but to simply display the last 9 products added seems really limiting.

 

Any suggestions on how to accomplish this?

 

Thanks,

 

-t

[thewitt]

I've been playing with the select statement to try to pull out the list I'm interested in, and I'm stuck.

 

If I use the following sql statement:

select p.products_id, p.products_image, p.products_tax_class_id, c.categories_id,

IF(s.status, s.specials_new_products_price, p.products_price) as products_price 

from products p 

left join specials s on p.products_id = s.products_id

left join products_to_categories c on p.products_id = c.products_id

where products_status = '1' 

order by p.products_date_added DESC 

limit 10;

 

I get the fields I want in the output, however what I really want is to limit this to unique p.products_id and c.categories_id columns.

 

In other words, 10 rows of data without duplicating product id or category ids.

 

Any way to do this with a select statement?

 

Thanks,

 

-t