Group data from MySQL query result by one column and create subarrays from some columns and get average from another column

Question

I need to get the sum of $product['stars'] so I can use it to find the average. When I try to use it I am not using it on the array somehow? Also I believe this comes in as a string. Does it need to be converted to INT?

$categories = array();
while ($row_rsCategories = mysqli_fetch_assoc($res)) { 
    $product_array = array();
    $product_array_query = mysqli_query($mysqli,"SELECT id, user_id, client_id, comments, stars FROM reviews WHERE user_id = '" . $row_rsCategories['userId'] . "'");
    while ($product_array_fetch = mysqli_fetch_array($product_array_query)) {
       $product_array[] = array(
           "id" => $product_array_fetch['user_id'],
           "comments" => $product_array_fetch['comments'],
           "stars" => $product_array_fetch['stars']
       );
    }              

    $categories[] = array(
        'id' => $row_rsCategories['userId'],
        'name' => $row_rsCategories['usersName'],
        'products' => $product_array,
    );
}

foreach ($categories as $category) {
    ?>                  
    <div class="col-md-4">
        <div id = "user-square">  
            <?php    
            echo $category['name'];
            ?>
            </br>
            <?php foreach($category['products'] as $product) {
                echo $product['stars'];
                ?> </br>
            <?php }

Answer 1

You could do it by combining array_sum with array_map:

$starsum = array_sum(array_map(function($x) { return $x['stars']; }, $product_array));

But you can also just calculate the sum while you're constructing the array of results:

$starsum = 0;
$rowcount = 0;
while($product_array_fetch = mysqli_fetch_array($product_array_query)) {
    $product_array[] = array("id"=>$product_array_fetch['user_id'],"comments"=>$product_array_fetch['comments'],"stars"=>$product_array_fetch['stars']);
    $starsum += $product_array_fetch['stars'];
    $rowcount++;
}

$categories[] = array(
    'id' => $row_rsCategories['userId'],
    'name' => $row_rsCategories['usersName'],
    'products' => $product_array,
    'avgstars' => ($rowcount == 0) ? 0 : $starsum / $rowcount
);

There's no need to convert the values to integers, PHP will do that automatically when you use arithmetic functions on them.