...

View Full Version : left join or inner join?



LJackson
08-01-2011, 12:10 AM
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

$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

$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

LJackson
08-01-2011, 12:22 AM
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

$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

Old Pedant
08-01-2011, 01:23 AM
Look here:
http://www.codingforums.com/showthread.php?p=818192#post818192



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


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:


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.

LJackson
08-01-2011, 12:22 PM
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


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


$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

Old Pedant
08-02-2011, 12:51 AM
*AS A GUESS*, I think you want this:



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.

LJackson
08-02-2011, 02:18 AM
thanks mate that works a treat :D

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

Old Pedant
08-02-2011, 04:22 AM
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.

LJackson
08-02-2011, 04:58 PM
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

Old Pedant
08-02-2011, 08:59 PM
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".

LJackson
08-03-2011, 02:33 PM
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?

Old Pedant
08-03-2011, 08:58 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum