PHP & MySQL - math and array problems

Question

I'm trying to add all the values from the grade_points field for example 10, 12.5, 2.1 and then divide it by how many times grade points where entered into the database for example 24.6 / 3.

I know that $total_rating_points is an array but I don't really know how to convert the array so I can add the total grade points and then divide it by how many times points where entered. I was hoping if someone can help me out with this problem? That I have been working on forever.

Here is the code I'm having trouble on.

$sql2 = "SELECT grade_points 
         FROM grades 
         JOIN articles_grades ON grades.id = articles_grades.grade_id
         WHERE articles_grades.users_articles_id = '$page'";

$result = mysqli_query($dbc,$sql2);

if (!mysqli_query($dbc, $sql2)) {
        print mysqli_error($dbc);
        return;
}

$total_rating_points = mysqli_fetch_array($result);


if (!empty($total_rating_points) && !empty($total_ratings)){
    $avg = (round($total_rating_points / $total_ratings,1));
    $votes = $total_ratings;
    echo $avg . "/10  (" . $votes . " votes cast)";
} else {
    echo '(no votes cast)';
}

Here is the full code I'm working on.

function getRatingText(){
    $dbc = mysqli_connect ("localhost", "root", "", "sitename");

    $page = '3';

    $sql1 = "SELECT COUNT(users_articles_id) 
             FROM articles_grades 
             WHERE users_articles_id = '$page'";

    $result = mysqli_query($dbc,$sql1);

    if (!mysqli_query($dbc, $sql1)) {
            print mysqli_error($dbc);
            return;
    }

    $total_ratings = mysqli_fetch_array($result);

    $sql2 = "SELECT grade_points 
             FROM grades 
             JOIN articles_grades ON grades.id = articles_grades.grade_id
             WHERE articles_grades.users_articles_id = '$page'";

    $result = mysqli_query($dbc,$sql2);

    if (!mysqli_query($dbc, $sql2)) {
            print mysqli_error($dbc);
            return;
    }

    $total_rating_points = mysqli_fetch_array($result);


    if (!empty($total_rating_points) && !empty($total_ratings)){
        $avg = (round($total_rating_points / $total_ratings,1));
        $votes = $total_ratings;
        echo $avg . "/10  (" . $votes . " votes cast)";
    } else {
        echo '(no votes cast)';
    }
}

Answer 1

To start off, you need to grab ALL of the values from your query. PHP's library only returns results one row at a time so you need to loop over it and continue to get results until you have them all. So do this:

$resource = mysqli_query($statement);
while ($result = mysql_fetch_array($resource))
{
    $total_results_points[] = $result[0];
}

From there, summing and averaging the array in PHP should be fairly simple:

$average = array_sum($total_rating_points) / count($total_rating_points)

In a nutshell, the array_sum() function returns all the elements in an array added together. The count() function tells you how many elements are in the array. So using the two, you can obtain the mean of the array quite easily.

Answer 2

When you're confused about what's in an array you can use

print_r($total_rating_points); 

To see what's in the array.

EDIT: Responding to comment

You have to use a while. mysqli_fetch_array retrieves a row, not all rows. So something like this would be in order:

$total_rating_points = array(); 
while ($rating_row = mysqli_fetch_array($result))
{
    $total_rating_points[] = $rating_row[0]; //0 might be the wrong index depending on the actual query.
}

print_r($total_rating_points);

You can then use array functions like sum and count as mentioned in some of the other solutions.

Answer 3

Or you could just go:

SELECT AVG(grade_points) 
     FROM grades 
     JOIN articles_grades ON grades.id = articles_grades.grade_id
     WHERE articles_grades.users_articles_id = '$page'

One call to mysql_fetch_array will return the only row returned by this statement, an array with one element, there's your average.