Jump to content

Ratings Query Coding?


amwd07

Recommended Posts

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

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.