Quick help with MySQL Query - Selecting OLD Product at Random

Quick question. Since my MySQL coding can get quite sloppy, I am hoping for some nice person to let me know how to do this particular query the most efficient way.

My Goal: I am going to select a product at random, and I want to know when the last time it was sold. Essentially, I am trying to randomly select an old product that is sitting around, so that I can dynamically put it on sale.

I just need to know the best MySQL select statement for finding that product.

Like, would this work and be the best way to call it?

DISTINCT p.products_id, o.date_purchased, o.orders_id, op.orders_id
FROM zen_products p, zen_orders_products op, zen_orders o
WHERE p.products_status = '1'
AND p.products_ordered >0
AND (
o.date_purchased ><= DATE_SUB( CURDATE( ) , INTERVAL 90
DAY )
AND op.products_id = p.products_id
AND op.orders_id = o.orders_id
)
ORDER BY COUNT( op.products_id ) DESC
LIMIT 1

Would that be the best way to accomplish this? Should there be some call to a random number function between 1 and NumProducts in our database, etc?

So there's this $db function:

function ExecuteRandomMulti($zf_sql, $zf_limit = 0, $zf_cache = false, $zf_cachetime=0) {

It returns the results in a random order. Could pull from the first response as your "answer" to the single random product. That would require you to remove the limit 1 and the order by ascending unless you wanted items sorted that way as the "seed" but seems like extra unnecessary processing in the scheme of things...

The other part/sequence that doesn't look right is the use of ><=

Regarding other things, I'd probably left join the two other tables regarding ordering. As to the proper sql statement, I'd need to take another look, but the above was some off the cuff observations.

Thanks for the info on the ExecuteRandomMulti function. It looks like the first parameter simply will take the MySQL select statement.

So that leaves the remaining question: what is the appropriate statement to plug in? I had a type-o in my original post. This is what I have so far, but not sure if it's the right way to query the products. Not sure how to use the JOIN statement. Anyone else have any input on the statement:

DISTINCT p.products_id, o.date_purchased, o.orders_id, op.orders_id
FROM zen_products p, zen_orders_products op, zen_orders o
WHERE p.products_status = '1'
AND p.products_ordered >0
AND (
o.date_purchased <= DATE_SUB( CURDATE( ) , INTERVAL 90
DAY )
AND op.products_id = p.products_id
AND op.orders_id = o.orders_id
)

Can you type a better (more preferred way) to write that statement, if indeed it needs a JOIN statement?

Jeff_Mash:

Can you type a better (more preferred way) to write that statement, if indeed it needs a JOIN statement?

As long as the query you have returns the expected/desired results, then it is fine.

As for whether it needs to be modified to use a JOIN is mainly a matter of preference. I've just Googled it, and found the following
http://stackoverflow.com/questions/2241991/in-mysql-queries-why-use-join-instead-of-where

It's worth a quick read, even if you don't understand some of the things written.

Cheers
RodG

RodG:

As long as the query you have returns the expected/desired results, then it is fine.

As for whether it needs to be modified to use a JOIN is mainly a matter of preference. I've just Googled it, and found the following
http://stackoverflow.com/questions/2241991/in-mysql-queries-why-use-join-instead-of-where

It's worth a quick read, even if you don't understand some of the things written.

Cheers
RodG

Awesome. Thank you for the help. I will play around with it and hopefully come up with a solution to the function I'm trying to write!

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?

Hi Jeff
You are comparing your orders_id to your products_id on your join. You need to join orders to orders_products on order_id and then do a second join for your products table. Like

SELECT p.products_id, o.date_purchased, q.products_status
FROM zen_orders o
JOIN zen_orders_products p ON o.orders_id = p.orders_id
JOIN zen_products q ON p.products_id = q.products_id
WHERE o.date_purchased <= DATE_SUB( CURDATE( ) , INTERVAL 90
DAY )
AND q.products_status =1

This is from doing an SQL in phpMyAdmin

I'm still thinking about the other part of your question.

Jeff_Mash:

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?

Here is my next idea:  You need an SQL within an SQL where the inner one searches for the MAX (or latest) date that a product sells and then the outer searchers those products for the ones > 90 days.

I haven't had time to play yet but I got the idea from here, maybe you can figure it out:
<http://www.w3resource.com/sql/subqueries/nested-subqueries.php>

lruskauff:

Here is my next idea: You need an SQL within an SQL where the inner one searches for the MAX (or latest) date that a product sells and then the outer searchers those products for the ones > 90 days.

I haven't had time to play yet but I got the idea from here, maybe you can figure it out:
http://www.w3resource.com/sql/subqueries/nested-subqueries.php

I think you're onto something with nested subqueries. Now I just have to figure out the best way to go about doing that. I'm just stuck trying to figure it out. (MySQL queries are not my strong suit!)

So....when you do a MAX() query on a date, we can get the last date the product sold, I take it?

Something like this perhaps, which I think will return a unique list of all products and the last time they sold:

SELECT distinct p.products_id, MAX(o.date_purchased) FROM zen_products p 
JOIN zen_orders_products op on p.products_id = op.products_id
JOIN zen_orders o on o.orders_id = op.orders_id
GROUP BY o.date_purchased DESC

And then from there, nest that inside of another query that only selects the results which are older than 90 days?

(My head is going to explode).

I thought something like this would work, but it doesn't:

SELECT distinct p.products_id, MAX(o.date_purchased) FROM zen_products p 
JOIN zen_orders_products op on p.products_id = op.products_id
JOIN zen_orders o on o.orders_id = op.orders_id
Group BY o.date_purchased DESC 
HAVING max(o.date_purchased) < DATE_SUB( CURDATE( ) , INTERVAL 90 DAY )
LIMIT 5

Initially, the results from that query appear like they may work (it returns a list of product id's and the date purchased which is older than 90 days). But upon further investigation, the date returned for the product id is NOT the last date purchased. It's just one of the dates purchased that was 90 days old.

So any help phrasing the statement into a nested subquery would be amazing! As you can see, I am certainly TRYING to do the work on my own, so I'm not just trying to get you (or anyone else) to do it all for me. I feel like I'm 95% there, but stuck on this last 5%.

Jeff_Mash:

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);

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 no longer available]

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)";

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)";

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

mc12345678:

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

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)";

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?

Jeff_Mash:

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)
)

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.

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 no longer available]

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 no longer available]

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

My query:> DrByte:

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"