amwd07 Posted November 28, 2007 Share Posted November 28, 2007 Hello I hope someone can assist me here I am trying to intergrate a ratings system into my project, the problem I have is I can not find an average ratings, I will try to explain clearly below with coding examples OK I have 3 recordsets 1 for restaurants with various different buildsql (primary key dine_id) I now am trying to intergrate a ratings system with this project so I have 2 additional recordsets in the code below the queries already relate to the right outlet because the code below is within my repeat region <?php do { ////////////////////////////////////////////////// $reviewid = $row_rs1['dine_id']; $orginalreviewid = $reviewid; $reviewid = $reviewid; mysql_select_db($database_connDW, $connDW); $query_rs2 = "SELECT * FROM core_jreviews_comments WHERE pid = '$reviewid'"; $rs2 = mysql_query($query_rs2, $connDW) or die(mysql_error()); $row_rs2 = mysql_fetch_assoc($rs2); $totalRows_rs2 = mysql_num_rows($rs2); $reviewpid = $row_rs2['id']; ?> ////////////////////////////////////////////////////////////////// <?php $overall_rating = 0; $overall_rating_rounded = 0; $count = 0; while ($myrow = mysql_fetch_array($rs2)) { $reviewpid2 = $myrow['id']; mysql_select_db($database_connDW, $connDW); $query_rs3 = "SELECT core_jreviews_ratings.reviewid, core_jreviews_ratings.ratings, core_jreviews_ratings.ratings_qty, core_jreviews_comments.id, core_jreviews_comments.pid, (core_jreviews_ratings.ratings_sum/core_jreviews_ratings.ratings_qty) as avgrating FROM (core_jreviews_ratings LEFT JOIN core_jreviews_comments ON core_jreviews_comments.id=core_jreviews_ratings.reviewid) WHERE reviewid = '$reviewpid2'"; $rs3 = mysql_query($query_rs3, $connDW) or die(mysql_error()); $row_rs3 = mysql_fetch_assoc($rs3); $totalRows_rs3 = mysql_num_rows($rs3); } $overall_rating = @number_format($row_rs3['avgrating'],1); $overall_rating_rounded = round($overall_rating); ?> With this code I am able to work out the average for each row, but this is no good to me I think I need to use 1 query instead of 2 maybe innerjoin then I need something like this ratings_sum / ratings_qty = avgrating avgrating I need to find the average WHERE pid = dine_id //// (dine_id is correct in the repeat region) for example pid 118 avgrating 4.6 pid 118 avgrating 4.2 the average should be 4.4 and all that I can get to come up is 4.2 Please I hope someone can help ??? Quote Link to comment Share on other sites More sharing options...
amwd07 Posted November 28, 2007 Author Share Posted November 28, 2007 I have tried for the past 2 hours to work this out and change my code to the following <?php $overall_rating = 0; $overall_rating_rounded = 0; $count = 0; while ($myrow = mysql_fetch_array($rs2)) { $reviewpid2 = $myrow['id']; mysql_select_db($database_connDW, $connDW); $query_rs3 = "SELECT * FROM core_jreviews_ratings WHERE reviewid = '$reviewpid2'"; $rs3 = mysql_query($query_rs3, $connDW) or die(mysql_error()); $row_rs3 = mysql_fetch_assoc($rs3); $totalRows_rs3 = mysql_num_rows($rs3); $avgrating = $row_rs3['ratings_sum']/$row_rs3['ratings_qty']; $overall_rating = $overall_rating + $avgrating; $count = $count +1; } $overall_rating = ($avgrating/$totalRows_rs2); $overall_rating_rounded = round($overall_rating); ?> I'm now tring to use $count to find an average, not sure if this is correct? Please can anyone out there help me ? Quote Link to comment Share on other sites More sharing options...
amwd07 Posted November 28, 2007 Author Share Posted November 28, 2007 Please can anyone here help me, or suggest maybe how I can do this ? Quote Link to comment Share on other sites More sharing options...
amwd07 Posted November 28, 2007 Author Share Posted November 28, 2007 I have tried the following into the query (core_jreviews_ratings.ratings_sum/core_jreviews_ratings.ratings_qty) as avgrating SUM(avgrating) / COUNT(avgrating) as avgrating2 the problem with this is Unknown column 'avgrating' in 'field list' ??? Quote Link to comment Share on other sites More sharing options...
amwd07 Posted November 28, 2007 Author Share Posted November 28, 2007 To simplify this problem, i have worked out what i need to do but don't know how to get it to work within the query. Select core_jreviews_ratings.reviewid, core_jreviews_ratings.ratings, core_jreviews_ratings.ratings_qty, core_jreviews_comments.id, core_jreviews_comments.pid, core_jreviews_ratings.ratings_sum, (core_jreviews_ratings.ratings_sum/core_jreviews_ratings.ratings_qty) as avgrating SUM (avgrating) / COUNT (avgrating) as avgrating2 ////// this is the problem FROM (core_jreviews_ratings LEFT JOIN core_jreviews_comments ON core_jreviews_comments.id=core_jreviews_ratings.reviewid) WHERE core_jreviews_comments.pid = 118 Quote Link to comment 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.