EchoFool Posted November 30, 2011 Share Posted November 30, 2011 Hey, I am wondering how to do an ORDER BY involving a seperate table so i can calculate the popularity of a given book? My system has a book and then user votes. Such as: User1 = 4 stars User2 = 2 stars User4 = 5 stars So im trying to ORDER BY most popular from working out the average score for a given book. Is this possible with a simple ORDER BY? What would the syntax be ? Quote Link to comment https://forums.phpfreaks.com/topic/252095-order-by-popularity/ Share on other sites More sharing options...
Zane Posted November 30, 2011 Share Posted November 30, 2011 I am wondering how to do an ORDER BY involving a seperate table What separate table? You only provided very vague information about one table. Quote Link to comment https://forums.phpfreaks.com/topic/252095-order-by-popularity/#findComment-1292495 Share on other sites More sharing options...
EchoFool Posted November 30, 2011 Author Share Posted November 30, 2011 Theres two tables: Books & User Votes So books has: bookId | name | price user votes has: userid | bookid | stars (out of 5) | Quote Link to comment https://forums.phpfreaks.com/topic/252095-order-by-popularity/#findComment-1292496 Share on other sites More sharing options...
fenway Posted November 30, 2011 Share Posted November 30, 2011 Sure -- AVG(stars). Quote Link to comment https://forums.phpfreaks.com/topic/252095-order-by-popularity/#findComment-1292756 Share on other sites More sharing options...
EchoFool Posted November 30, 2011 Author Share Posted November 30, 2011 Oh thats nice and simple - but in an ORDER by how would i do it ? Because user_votes is a different table? Would i left join or inner join the two tables on bookid ? Quote Link to comment https://forums.phpfreaks.com/topic/252095-order-by-popularity/#findComment-1292761 Share on other sites More sharing options...
fenway Posted November 30, 2011 Share Posted November 30, 2011 You'd left join, because that way you can handle a book with no votes. Quote Link to comment https://forums.phpfreaks.com/topic/252095-order-by-popularity/#findComment-1292790 Share on other sites More sharing options...
EchoFool Posted November 30, 2011 Author Share Posted November 30, 2011 Oh good logic!! Thanks for the advice! Quote Link to comment https://forums.phpfreaks.com/topic/252095-order-by-popularity/#findComment-1292791 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.