Ratings Query Coding?

[amwd07]

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  ???

[amwd07]

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 ?

[amwd07]

Please can anyone here help me, or suggest maybe how I can do this ?

[amwd07]

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' ???

[amwd07]

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