1
\$\begingroup\$

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ Your code seem to be susceptible to SQL injections. Please learn to use prepared statements: youtube.com/watch?v=nLinqtCfhKY \$\endgroup\$ – tereško Mar 24 '16 at 12:01
  • \$\begingroup\$ @tereško can we secure our code without using prepared statements ? \$\endgroup\$ – Ayaz Shah Mar 24 '16 at 13:38
  • \$\begingroup\$ probably, but that would be a lot harder to implement \$\endgroup\$ – tereško Mar 24 '16 at 14:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.