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 ? 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. 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) | 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). 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 ? 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. 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! Link to comment https://forums.phpfreaks.com/topic/252095-order-by-popularity/#findComment-1292791 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.