Top 10 list query question?

Hi,

I need a little help with my top 10 best rated query. Right now I have:

$sql = "SELECT * FROM tblPix, tblVoting WHERE tblVoting.picID = tblPix.picID ORDER BY picRating DESC LIMIT 10";

This one works but not as I want to. I want to take into account the num of votes. The tblVoting has a field called numOfVotes.

So a rating of 5 with 10 votes will be above a rating of 5 with 6 votes.

Any suggestions??

 

You have to set a rule. Maybe a threshold such as nothing with less than 10 votes will be considered?

Eric

 

$sql = "SELECT * FROM tblPix, tblVoting WHERE tblVoting.picID = tblPix.picID ORDER BY picRating DESC, numOfVotes DESC LIMIT 10";

That'll make 5 with 10 votes come before 5 with 6 votes, but won't make 5 with 10 votes come before 6 with 1 vote - you'd have to calculate a weighted value based on number of votes and rating.

 

@void that's the one with the two ORDER BY columns Thanks.

Must dive into that one.

 

Have fun with the weighting and report back if you're stuck

 

is this the best practice for table joins in MySQL?

SELECT * FROM tblPix, tblVoting

I generally do inner or left outer... (Just interested which is better here)

 

Not sure about best practice, but it's what I use. Don't forget to add

WHERE tblVoting.picID = tblPix.picID

Code (markup):

though!

 

@void, I use the same "simple" method as you do. Although I have to read up on the query possibilities of mysql. Like ccoonen points out. I have used it one or twice for a project, but I can't remember why

Still got to do the weighted issue

 

Will require two queries.

First for count.

then deduct 10 from the count and then use the limit thing.

 

@Nick_Mayhem - do you mean this, or am I missing the point completely?

$sql = "SELECT * FROM tblPix, tblVoting
WHERE tblVoting.picID = tblPix.picID
AND numOfVotes >= 10
ORDER BY picRating DESC, numOfVotes DESC LIMIT 10";

Code (markup):

That wouldn't quite do what I was thinking with the weighting idea where something with 1000 votes of 5 scores higher than 10 votes of 6. Maybe that's not a great idea anyway

 

For the weighted voting I was more thinking in the line of Void.

But I couldn't find a weighted variable, but I found something that triggers my mind.
http://www.codeproject.com/useritems/CodeProject_s_Voting_seq.asp

Have to say it's been a long time I have done this kind of math.

Anyways, here's a quote

Now in my case I don't have registrered members so I can't use their status as a weight.

But what about using total visits from an user based on their ip??