Quick help with MySQL Query - Selecting OLD Product at Random

[Jeff_Mash]

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?

[mc12345678]

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.

[Jeff_Mash]

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?

[RodG]

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/2...stead-of-where

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

Cheers
RodG

[Jeff_Mash]

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/2...stead-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!

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