I am running into a little problem here, and I hope someone can suggest a quick tweak to my MySQL statement:
PROBLEM: I am trying to query one random record for an older product which hasn't sold in over 90 days. So my SQL query looks similar to this:
Code:
$db->ExecuteRandomMulti("SELECT DISTINCT(p.products_id), o.date_purchased FROM zen_products p JOIN zen_orders o on o.orders_id = p.products_id
WHERE p.products_status = '1'
AND o.date_purchased <= DATE_SUB( CURDATE( ) , INTERVAL 90 DAY )
ORDER BY o.date_purchased DESC
", 1);
That is a simplified version of my code. I stripped out a lot of additional stuff to narrow down the product I want (by price, category, etc).
The problem is this. It correctly returns to me a product id which was sold less than 90 days ago........HOWEVER, that doesn't mean it also wasn't sold yesterday, or a week ago either.
See what I mean?
In other words, the SQL query is looking at orders older than 90 days old, and selecting a random product from there........but I want the product that is selected to have ALSO NOT SOLD within that 90 period.
Does that make sense?
I am running into trouble on how to tweak my SQL query so that 1) it finds a product that last sold at least 90 days ago, and 2) also hasn't sold within 90 days as well.
If you look at my query statement above, you will see that it does return what I am asking for: a product that sold over 90 days ago. But it doesn't also check to make sure that same product hasn't sold more recently, since those recent records are not checked for in the query.
I am unsure if what I am asking can be done within a single SQL query statement (I hope so), or if I need to do a secondary query to check if the returned product has also not been sold within 90 days.
Any ideas?
Bookmarks