Quick help with MySQL Query - Selecting OLD Product at Random

[mc12345678]

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:

$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?

Thinking:

$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 )  and not exists (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);

[Jeff_Mash]

I like where you're going with this one, mc12345678

However, that query code is returning products which were NEVER purchased. I run the query, and it gives me a list of products with a purchase date....but looking at the database, those products were never sold. Perhaps there is some tweak to the query needed?

Screenshot:
Attachment 15816

[DrByte]

The logic of the following query is:
"get distinct products_id values from the list of all has-been-purchased-at-least-once products, where the purchase date is more than 90 days ago"

"SELECT distinct products_id from " . TABLE_ORDERS_PRODUCTS . " op 
JOIN " . TABLE_ORDERS . " o ON o.orders_id = op.orders_id AND o.date_purchased > DATE_SUB(CURDATE(), INTERVAL 90 DAY)";

[lruskauff]

The logic of the following query is:
"get distinct products_id values from the list of all has-been-purchased-at-least-once products, where the purchase date is more than 90 days ago"

"SELECT distinct products_id from " . TABLE_ORDERS_PRODUCTS . " op 
JOIN " . TABLE_ORDERS . " o ON o.orders_id = op.orders_id AND o.date_purchased > DATE_SUB(CURDATE(), INTERVAL 90 DAY)";

Hi Dr Byte
Does this rule out those products that were purchased multiple times, with one or more of those times being >=90 days and the one or more of those times being purchased <90 days. For example, I sold a rocket kit yesterday and I sold one last year. This would pull the product "Rocket Kit" because it records a sale > 90 days.

Thank you.
lruskauff

[lruskauff]

Thinking:

$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 )  and not exists (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);

In the above, change

 JOIN zen_orders o on o.orders_id =  p.products_id

to

 JOIN zen_orders o on o.orders_product_id =  p.products_id

two places

[Jeff_Mash]

The logic of the following query is:
"get distinct products_id values from the list of all has-been-purchased-at-least-once products, where the purchase date is more than 90 days ago"

"SELECT distinct products_id from " . TABLE_ORDERS_PRODUCTS . " op 
JOIN " . TABLE_ORDERS . " o ON o.orders_id = op.orders_id AND o.date_purchased > DATE_SUB(CURDATE(), INTERVAL 90 DAY)";

I think the logic needs to be the following:

"get distinct products_id values from the list of all has-been-purchased-at-least-once products, where the product HASN'T BEEN PURCHASED WITHIN 90 DAYS."

Like lruskauff mentioned above, your query WILL return products purchased greater than 90 days. However, we are missing one extra query, and that is "The products returned must also NOT HAVE BEEN purchased within 90 days as well."

That is the missing factor here, and the one we are all trying to figure out how best to write the query. Any ideas?

[DrByte]

I think the logic needs to be the following:

"get distinct products_id values from the list of all has-been-purchased-at-least-once products, where the product HASN'T BEEN PURCHASED WITHIN 90 DAYS."

Like lruskauff mentioned above, your query WILL return products purchased greater than 90 days. However, we are missing one extra query, and that is "The products returned must also NOT HAVE BEEN purchased within 90 days as well."

That is the missing factor here, and the one we are all trying to figure out how best to write the query. Any ideas?

Perhaps:

SELECT distinct products_id from orders_products op JOIN orders o ON o.orders_id = op.orders_id 
WHERE o.date_purchased > DATE_SUB(CURDATE(), INTERVAL 90 DAY)
AND products_id not in ( 
   SELECT distinct products_id from orders_products op 
   JOIN orders o ON o.orders_id = op.orders_id AND o.date_purchased <= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
)

[Jeff_Mash]

I think that may be right. I'm not sure, but it looks like we may need to REVERSE the operators after date purchased though. If I am reading your query correctly, it looks like this will return to us products which may have only been purchased within the last 90 days and nothing else, correct?

I want to return products that have NOT been purchased within 90 days.

So perhaps this may be the code:

SELECT DISTINCT products_id
FROM zen_orders_products op
JOIN zen_orders o ON o.orders_id = op.orders_id
WHERE o.date_purchased < DATE_SUB( CURDATE( ) , INTERVAL 90
DAY )
AND products_id NOT
IN (

SELECT DISTINCT products_id
FROM zen_orders_products op
JOIN zen_orders o ON o.orders_id = op.orders_id
AND o.date_purchased >= DATE_SUB( CURDATE( ) , INTERVAL 90
DAY )
)
LIMIT 5

I added LIMIT 5 for my testing, and added the "zen_" prefix to the tables.

[Jeff_Mash]

Wow, we are almost there! It looks like with the code below, I am getting the proper products returned (those which haven't sold within 90 days).

Attachment 15821

However, when I request to return other information (like the product NAME, the product IMAGE, things that I need for my other function), then the results returned give me some duplicates.

Here is the FULL QUERY I am using:

SELECT DISTINCT op.products_id, pd.products_name, p.products_image, p.products_price, p.products_model, p.products_quantity, o.date_purchased, o.orders_id, op.orders_id
FROM zen_orders_products op
JOIN zen_orders o ON o.orders_id = op.orders_id JOIN zen_products p on p.products_id = op.products_id
JOIN zen_products_description pd ON pd.products_id = p.products_id
WHERE (p.products_quantity > 2
AND o.date_purchased <= DATE_SUB( CURDATE( ) , INTERVAL 90 DAY ) OR (p.products_quantity > 0
AND o.date_purchased <= DATE_SUB( CURDATE( ) , INTERVAL 1 YEAR )))
AND p.products_status = '1'
AND (p.manufacturers_id = '0'
OR p.manufacturers_id = '8')
AND p.master_categories_id != '7'
AND p.master_categories_id != '47'
AND p.master_categories_id != '9'
AND p.products_ordered > 0
AND p.products_price > 14.95 
AND op.products_id NOT
IN (

SELECT DISTINCT op.products_id
FROM zen_orders_products op
JOIN zen_orders o ON o.orders_id = op.orders_id
    JOIN zen_products p on p.products_id = op.products_id
AND o.date_purchased >= DATE_SUB( CURDATE( ) , INTERVAL 90
DAY )

) ORDER BY op.products_id DESC
LIMIT 5

And this is what is returning (you can see that the product ID's are being listed more than once since it's finding multiple orders):

Attachment 15820

What part of that query above can I change so that it only lists the product ONE TIME without duplicates. Is that possible?

[DrByte]

My query:

Perhaps:

SELECT distinct products_id from orders_products op JOIN orders o ON o.orders_id = op.orders_id 
WHERE o.date_purchased > DATE_SUB(CURDATE(), INTERVAL 90 DAY)
AND products_id not in ( 
   SELECT distinct products_id from orders_products op 
   JOIN orders o ON o.orders_id = op.orders_id AND o.date_purchased <= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
)

means:
"get a list of product_ids from the order history where the purchase-date was more than 90 days ago, and then remove any product_ids from sales made within the last 90 days, if any"