Bestsellers Date Limit

[piperman]

Hi everyone,

My best_sellers.php page works great, but it shows the bestsellers of all time on my site.

 

What i want, is to limit it to say, 3 months with only newly added product, say in the past 3 months are included. so the bestsellers could be called, whats hot , or something.

 

I've been messing with the code but i'm not getting anywhere.

I've found a contribution, but its not really what i want.

http://www.oscommerce.com/community/contributions,1981

 

My code is:

 

$best_sellers_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_ordered > 2 and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_ordered DESC, pd.products_name";

 

So i need to put in some Date Limit code, so only recently added products are in the new whats hot page....

 

so this needs to go in somewhere... p.products_date_added and limit it to 3 months or 90 days or something,

Any help would be greatly appreciated,

Thanks a lot...

Piperman.

[Guest]

To limit the products date added to 90 days try this.

 

In the where clause of your above query right after this part

and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "'

add

and p.products_date_added >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)

Change 90 to however many days you want to go back.

[piperman]

Hi Brian,

That's great worked like a treat.

I see now that what i actually wanted was the bestsellers from the past 90 days,

but using your code (which is what i asked for) i get the bestsellers from items added over the last 90 days....

 

So, I've upped the date to 200 days and put in a sold great than 5 units,

Now i get the best-sellers form the past few months,

which is much more representative of what is actually selling at the moment.

Thanks a lot for your help,

G.

[Guest]

You're welcome. Glad yo got worked out.

[dave249]

nice answer, had a similar issue but now works a treat