I created a function to add and return the average rating.
Rating Function
// Adding AddRating
final function AddRating($rt_target_id, $u_id, $rt_vote,$moduleName){
$error = 0;
if(!empty($moduleName) && $rt_target_id != 0 && $u_id != 0){
$tableName = '';
switch ($moduleName) {
case 'promotion':
$tableName = 'ws_promotion';
break;
default:
$tableName = 'invalid';
}
if($tableName!='invalid'){
$addRating = "INSERT INTO ws_rating (u_id, rt_target_id, rt_vote, rt_table_name, date_added)
VALUES ('".$u_id."','".$rt_target_id."','".$rt_vote."','".$tableName."', '".date("Y-m-d H:i:s")."')";
$dataInsert = mysqli_query($this->connection,$addRating);
if($dataInsert){
$Rating = "select avg(rt_vote) as Rating from `ws_rating` where `rt_target_id` = ".$rt_target_id. " and `rt_table_name` = '".$tableName."'";
$RatingQuery = mysqli_query($this->connection,$Rating);
$rowRating = mysqli_fetch_assoc($RatingQuery);
$response['rating'] = $rowRating;
}
echo json_encode($response);
}
}// end if
}
Table
---------------------------------------------------------------------
|rt_id | u_id | rt_target_id | rt_vote | rt_table_name | date_added |
-------------------------------------------------------------------
|1 | 1 | 2 | 5 | promotion_tb | 2016-03-24 |
---------------------------------------------------------------------
I added 12432
rows with same data in above table. Below select
query is giving this 4.9936
result of added rows 12432
.
SELECT AVG(rt_vote) AS Rating FROM `ws_rating` WHERE `rt_target_id` = 2 AND `rt_table_name` = 'ws_promotion'
I wanted to know whether my table structure and function are correct. In the future, if there are 1 million rows then will my function return the correct average?