Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts

    Exclamation left join or inner join?

    Hi All

    i am updating some of my code, currently i am pulling out 6 records based on popularity from my product table

    tbl_dvds: filmID,filmEAN,filmName,filmIMG etc etc

    and the query used is
    PHP Code:
        $popular mysql_query("SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding FROM tbl_dvds WHERE filmBinding = 'dvd' ORDER BY filmPopularity DESC, filmReleaseDate DESC LIMIT 0,6"); 
    which worked fine, BUT now i want to go a little more advance and return the 6 records based on the popularity of the last 30 days, so i have set up a new table for this

    tbl_popularity: popID, prodID, userIP, dateStamp

    now each time the product is viewed a new record is entered into the new table.

    so now i want to adjust the query to use the new table to see which 6 products are the most popular in the last 30 days but im not sure how to go about it.

    im sure i need to use joins?

    something like this maybe
    PHP Code:
        $popular mysql_query("SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding FROM tbl_dvds AS dvd LEFT JOIN tbl_popularity AS pop ON dvd.filmID = pop.prodID WHERE pop.prodID <> '0' && film.filmBinding = 'dvd' GROUP BY pop.prodID DESC LIMIT 0,6"); 
    but this query give the following error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given

    can anyone help me fix the query and in the left join the best way to go?

    many thanks
    Luke

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok sorted out why the query didnt work

    i tried to return film.filmBinding where as it should of been dvd.binding

    so here is the correct query
    PHP Code:
    $popular mysql_query("SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding FROM tbl_dvds AS dvd LEFT JOIN tbl_popularity AS pop ON dvd.filmID = pop.prodID WHERE pop.prodID <> '0' && dvd.filmBinding = 'dvd' GROUP BY pop.prodID DESC LIMIT 0,6"); 
    but if my tbl_popularity table has less than 6 products, it currently only shows the number of probucts n the table, how do i get it so that it will always show 6 records, how do i get it to fill the remaining records with ones not in the popularity table?

    thanks

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,233
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Look here:
    http://www.codingforums.com/showthre...192#post818192

    Code:
    SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding
    FROM tbl_dvds AS dvd LEFT JOIN tbl_popularity AS pop ON dvd.filmID = pop.prodID 
    WHERE pop.prodID <> '0' && dvd.filmBinding = 'dvd' 
    GROUP BY pop.prodID DESC LIMIT 0,6
    You have destroyed the LEFT JOIN by having WHERE pop.prodID <> '0' in there. You have effectively converted it into an INNER JOIN.

    (By the by: Why would the prodID be a *string*? '0' instead of just 0??)

    You *possbily* could fix that by doing
    Code:
    SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding
    FROM tbl_dvds AS dvd LEFT JOIN tbl_popularity AS pop 
    ON dvd.filmID = pop.prodID AND pop.prodID <> 0
    WHERE dvd.filmBinding = 'dvd' 
    GROUP BY pop.prodID DESC LIMIT 0,6
    But I tend to think that what you really want is this:
    Code:
    SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding
    FROM tbl_dvds AS dvd LEFT JOIN tbl_popularity AS pop ON dvd.filmID = pop.prodID 
    WHERE pop.prodID IS NOT NULL AND dvd.filmBinding = 'dvd' 
    GROUP BY pop.prodID DESC LIMIT 0,6
    But if *THAT* is so, then indeed you don't need/want a LEFT JOIN at all. Because returning only the records where pop.prodID IS NOT NULL is the same thing as just doing an INNER JOIN.

    Anyway, my head hurts. I don't see that query works at all, because you are doing GROUP BY ... DESC instead of ORDER BY ... DESC.

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    Hi mate, thanks for your reply

    you are correct the prodID shouldnt be a string, have made changes to that.

    i dont really want the WHERE pop.prodID <> '0' there but i've added it because one of my queries is adding several blank rows into the tbl_popularity table which is annoying and the WHERE pop.prodID <> '0' is there to ignore those records.

    so back to the above query

    my tbl_dvds holds several thousand records of dvds and
    my tbl_popularity holds records on when each dvd was viewed ie

    popID,prodID,userIP, dateTime
    Code:
    1	145792761	86.154.126.162	2011-07-31 21:35:53
    19	126094673	86.154.126.162	2011-07-31 22:32:46
    18	0	86.154.126.162	2011-07-31 22:31:46
    17	148540651	69.63.189.250	2011-07-31 22:30:31
    16	0	86.154.126.162	2011-07-31 22:30:07
    15	133785381	86.154.126.162	2011-07-31 22:28:53
    14	0	86.154.126.162	2011-07-31 22:26:53
    13	129983020	86.154.126.162	2011-07-31 22:24:50
    20	126094673	69.63.190.250	2011-07-31 22:32:57
    21	149829451	86.154.126.162	2011-07-31 22:34:42
    22	149829451	66.220.153.249	2011-07-31 22:34:47
    23	0	86.154.126.162	2011-07-31 22:39:25
    24	0	86.154.126.162	2011-07-31 22:40:26
    and i want the top 6 dvds (prodIDs) from this table to be viewed on my site in order of the number of entrys in this table... at the moment although the query is incorrect this

    PHP Code:
        $popular mysql_query("SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding FROM tbl_dvds AS dvd 
    LEFT JOIN tbl_popularity AS pop ON dvd.filmID = pop.prodID WHERE pop.prodID <> 0 && dvd.filmBinding = 'dvd' GROUP BY pop.prodID DESC LIMIT 0,6"
    ); 
    pulls out the top 6 dvds but does not order them by number of times they appear in the table, i assume thats because im using group by and not count or something similar?

    thanks
    Luke

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,233
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    *AS A GUESS*, I think you want this:

    Code:
    SELECT filmID as id, filmName as name, filmIMG as img, filmRRP as rrp, filmBinding as binding, COUNT(*) AS howmany
    FROM tbl_dvds AS dvd LEFT JOIN tbl_popularity AS pop 
    ON dvd.filmID = pop.prodID AND pop.dateStamp > DATE_SUB( curdate(), INTERVAL 30 DAY )
    WHERE dvd.filmBinding = 'dvd'
    GROUP BY filmID, filmName, filmIMG, filmRRP, filmBinding
    ORDER BY howmany DESC
    LIMIT 6
    That code assumes that the data type of dateStamp is DATETIME. If you (why?) used a Unix timestamp instead, you'll have to convert either it to DATETIME or the DATE_SUB expression to Unix time to make it work. In any case, that's how you get the date 30 days ago.

    Note that as given there, ties can then appear in any order (e.g., if 3 films have counts of 20, no predicting what order they will be in).

    This could easily be refined. Say to give more weight to newer "hits". But you'd need to tell me what algorithm you wanted to use for the weighting.

  • Users who have thanked Old Pedant for this post:

    LJackson (08-02-2011)

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    thanks mate that works a treat

    RE multiple count values, yeah that would be good to have the product with the newer hits first. in terms of what algorithm to use, what options are there? sorry not familiar with algorithms

    thanks

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,233
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Example: hits within last day count triple, those within last week count double, all else within last month count single.

    That's an algorithm.

    If x then y
    If x2 then y2
    etc.

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ah got ya

    so will this only come into force if the count is the same between 2 or more products?

    lets say if the date is:

    less than 1 week ago => 4 pts
    great than 1 weeks ago => 3pts
    greater than 2 weeks ago => 2pts
    greater than 3 weeks ago 1pt

    say, would that work?
    thanks
    Luke

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,233
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    It could come into force if count is same or it could replace count.

    I, personally, would expect it to replace count if the intent is to show "hot new downloads".

  • #10
    Senior Coder
    Join Date
    Jun 2008
    Location
    Cornwall
    Posts
    2,098
    Thanks
    297
    Thanked 12 Times in 12 Posts
    ok cool,

    the reason i ask is because if product 1 has 10 views in the last month and product 2 has 4 views in the past month but are all in the last week the product 2 would be listed first despite it only having half the views of product 1?

    not sure which way would be better tbh?

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,233
    Thanks
    80
    Thanked 4,456 Times in 4,421 Posts
    Can't decide that for you. You have to figure out whether "hotness" is more important.

    And if you really expect that the most views you will have on a product is only 10 in a month, then maybe you should use months instead of weeks for the multipliers?

    Heck, you can easily make much more complex algorithms. Maybe you scored based on the logarithm of the number hits. So 3 hits is a score of 1 (roughly) where as it takes 8 hits (roughly) to get a score of 2, etc. Or inverse logarithms. Or use a scoring lookup table.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •