Hey guise, I have a uni assignment; setting up backend code for a tour company website. Everything's gone swimmingly but I've run into issues implementing a system for rating the tour packages.

Implementation for the rating system: user clicks rate button, the rating score is added to a running total and the tally of ratings is incremented by one. The average rating is returned by using php to divide the running total by the tally of ratings. I don't have a table of individual rating scores, just three fields
TotalRatings = the cumulative total of ratings
RatingNo = the tally of ratings submitted, this increments by one when a new rating is submitted.
Tour_Rating = the result of TotalRatings divide by RatingNo.

the handle for my database is $dbh and the Tour_Id is submitted along with the rating value from the form.

My code is below.

        if ($_POST[Tour_Rating] >= 1 && $_POST[Tour_Rating] <= 5)
        try {
        $rating =$dbh -> query( "SELECT TotalRatings FROM Tours WHERE Tour_Id = '$_POST[Tour_Id]'");
        $count = $dbh-> query( "SELECT RatingNo FROM Tours WHERE Tour_Id = '$_POST[Tour_Id]'");

        $postRating = intval( $_POST[Tour_Rating]);
        $ratings = intval($rating);
        $ratings += $postRating;

        $counts = intval($count);

        $avgRatings = $ratings/$counts;
        $avgRating = intval($avgRatings);

        } catch (exception $e){
        echo "sql queries failed";
        echo $e;

        $sql = "UPDATE Tours SET TotalRatings = '$ratings', RatingNo = '$counts', Tour_Rating = '$avgRating' WHERE Tour_Id = '$_POST[Tour_Id]' ";


I've put intval($var) on all my variables because I kept getting "unable to convert to string" errors when executing the $sql line.
The variables aren't updating correctly in my database, the $counts field (RatingNo) isn't incrementing and the $ratings field (TotalRatings) appears to be displaying the input rating (from form, $postRating) plus 1.

Do I need to have the code block in the try catch as a function? or will it reexecute every time a new value is posted from the form?
If so can I use something like ->exec($function) to execute it?

Not sure whats wrong with it, any ideas?

(nb, sorry if these questions are basic, we've been taught very little php in this subject so I've been going off of online resources, hurray for regional universities.)

5 Years
Discussion Span
Last Post by Pablo_3

Perhaps I'm a slightly wrong here, but I'd have the ratings as a separate table:

tour_id (FK) | user_id (FK) | rating (tinyint [1])

This stops the same user rating a tour twice. Also you can get the COUNT of rating and AVG of rating via a simple sql statement. EG:

SELECT COUNT(*) AS count_ratings, AVG(rating) AS ave_rating FROM ratings WHERE tour_id = 22

Edited by diafol

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.