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

Nested loops to access your database is not a good habit to get into. Ideally, you want to make a single trip to the database and gather all required data. In this case, use an INNER JOIN if you want to omit users who didn't give review, or use LEFT JOIN if you want to include users who didn't give a review. Your unconditional declaration of the $product_array indicates that you should use a LEFT JOIN.

There is no need to manually fetch() records from the result set object -- just use a foreach() on the object and access the data as if each row was an associative array.

As for the grouping by userId, if you weren't accumulating comments strings I'd recommend that you use MySQL's AVG() aggregrate function. However, because you need more nuanced functionality, manual operations will be necessary to populate a subarray of comments and keep track of the stars and review counts. I'll demonstrate how to push references into a result array, then only modify those references to update the necessary fields.

Code: (PHPize Demo with my own sample input data)

$sql = <<<SQL
SELECT userId,
       usersName,
       comments,
       stars
FROM cats
LEFT JOIN reviews ON cats.userId = reviews.user_id
ORDER BY cats.usersName, cats.userId
SQL;

$result = [];
foreach ($mysqli->query($sql) as $row) {
    if (!isset($ref[$row['userId']])) {
        $ref[$row['userId']] = [
            'id' => $row['userId'],
            'name' => $row['usersName'],
            'comments' => $row['stars'] === null ? [] : [$row['comments']], // if a review, create single-element array
            'stars' => $row['stars'] === null ? [] : [$row['stars']], // if a review, create single-element array
            'reviews' => $row['stars'] === null ? 0 : 1, // if a review, start count at 1
            'starSum' => $row['stars'],
            'average' => $row['stars'],
        ];
        $result[] = &$ref[$row['userId']];
    } else {
        $ref[$row['userId']]['comments'][] = $row['comments']; // push new value into subarray
        $ref[$row['userId']]['stars'][] = $row['stars'];       // push new value into subarray
        ++$ref[$row['userId']]['reviews'];                     // increment review count
        $ref[$row['userId']]['starSum'] += $row['stars'];      // add new value to stored sum
        $ref[$row['userId']]['average'] = $ref[$row['userId']]['starSum'] / $ref[$row['userId']]['reviews'];  // calculate new average
    }
}
var_export($result);

This should give you every piece of data necessary to display relevant data to the end user.

Output from my sample data:

array (
  0 => 
  array (
    'id' => '7',
    'name' => 'Alan',
    'comments' => 
    array (
      0 => 'super-ordinary',
    ),
    'stars' => 
    array (
      0 => '3',
    ),
    'reviews' => 1,
    'starSum' => '3',
    'average' => '3',
  ),
  1 => 
  array (
    'id' => '5',
    'name' => 'Bill',
    'comments' => 
    array (
      0 => 'pretty not bad',
      1 => 'what a stinker',
    ),
    'stars' => 
    array (
      0 => '4',
      1 => '1',
    ),
    'reviews' => 2,
    'starSum' => 5,
    'average' => 2.5,
  ),
  2 => 
  array (
    'id' => '1',
    'name' => 'Chad',
    'comments' => 
    array (
      0 => 'super mega wow',
    ),
    'stars' => 
    array (
      0 => '5',
    ),
    'reviews' => 1,
    'starSum' => '5',
    'average' => '5',
  ),
  3 => 
  array (
    'id' => '3',
    'name' => 'Dave',
    'comments' => 
    array (
      0 => 'absolutely ace',
      1 => 'twas very good',
    ),
    'stars' => 
    array (
      0 => '5',
      1 => '4',
    ),
    'reviews' => 2,
    'starSum' => 9,
    'average' => 4.5,
  ),
  4 => 
  array (
    'id' => '2',
    'name' => 'Ned',
    'comments' => 
    array (
      0 => 'a nice quality',
      1 => 'I was very meh',
    ),
    'stars' => 
    array (
      0 => '4',
      1 => '2',
    ),
    'reviews' => 2,
    'starSum' => 6,
    'average' => 3,
  ),
  5 => 
  array (
    'id' => '4',
    'name' => 'Newt',
    'comments' => 
    array (
    ),
    'stars' => 
    array (
    ),
    'reviews' => 0,
    'starSum' => NULL,
    'average' => NULL,
  ),
  6 => 
  array (
    'id' => '6',
    'name' => 'Norton',
    'comments' => 
    array (
    ),
    'stars' => 
    array (
    ),
    'reviews' => 0,
    'starSum' => NULL,
    'average' => NULL,
  ),
)

Answer 2

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.