Page 1 of 3 123 LastLast
Results 1 to 10 of 22
  1. #1
    Join Date
    Aug 2004
    Posts
    762
    Plugin Contributions
    0

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

    Code:
    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?
    - Jeff

  2. #2
    Join Date
    Jul 2012
    Posts
    16,719
    Plugin Contributions
    17

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

    So there's this $db function:
    Code:
    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.
    ZC Installation/Maintenance Support <- Site
    Contribution for contributions welcome...

  3. #3
    Join Date
    Aug 2004
    Posts
    762
    Plugin Contributions
    0

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

    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:

    Code:
    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

  4. #4
    Join Date
    Jan 2007
    Location
    Australia
    Posts
    6,167
    Plugin Contributions
    7

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

    Quote Originally Posted by Jeff_Mash View Post
    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

  5. #5
    Join Date
    Aug 2004
    Posts
    762
    Plugin Contributions
    0

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

    Quote Originally Posted by RodG View Post
    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

  6. #6
    Join Date
    Aug 2004
    Posts
    762
    Plugin Contributions
    0

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

    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?
    Last edited by Jeff_Mash; 22 Nov 2015 at 10:08 PM.
    - Jeff

  7. #7
    Join Date
    Sep 2008
    Location
    WA
    Posts
    555
    Plugin Contributions
    0

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

    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

    Code:
    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.

  8. #8
    Join Date
    Sep 2008
    Location
    WA
    Posts
    555
    Plugin Contributions
    0

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

    Quote Originally Posted by Jeff_Mash View Post
    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?
    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/subque...subqueries.php

  9. #9
    Join Date
    Aug 2004
    Posts
    762
    Plugin Contributions
    0

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

    Quote Originally Posted by lruskauff View Post
    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/subque...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:

    Code:
    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).
    Last edited by Jeff_Mash; 23 Nov 2015 at 01:21 AM.
    - Jeff

  10. #10
    Join Date
    Aug 2004
    Posts
    762
    Plugin Contributions
    0

    Default Re: Quick help with MySQL Query - Selecting OLD Product at Random

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

    Code:
    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

 

 
Page 1 of 3 123 LastLast

Similar Threads

  1. v155 Help with a MySQL query
    By lat9 in forum General Questions
    Replies: 7
    Last Post: 26 Apr 2016, 10:11 PM
  2. help with database query selecting manufacturers
    By bn17311 in forum General Questions
    Replies: 1
    Last Post: 23 Apr 2015, 08:54 AM
  3. Need help with a mySQL Query
    By jeffmic in forum General Questions
    Replies: 3
    Last Post: 19 Dec 2010, 02:21 PM
  4. Help for mysql query!!
    By g.nencini in forum General Questions
    Replies: 2
    Last Post: 13 Jul 2009, 05:32 AM
  5. Help with Random Code appearing in Product List
    By Oozle in forum General Questions
    Replies: 10
    Last Post: 29 May 2009, 03:04 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR