*************

SQL code: Upload dump Copy Format Clear

CREATE TABLE cats ( userId INT AUTO_INCREMENT, usersName VARCHAR(100), PRIMARY KEY (userId) ); INSERT INTO cats (usersName) VALUES ('Chad'), ('Ned'), ('Dave'), ('Newt'), ('Bill'), ('Norton'), ('Alan'); CREATE TABLE reviews ( id INT AUTO_INCREMENT, user_id INT, client_id INT, comments VARCHAR(100), stars INT, PRIMARY KEY (id) ); INSERT INTO reviews (user_id, client_id, comments, stars) VALUES (3, 5, 'twas very good', 4), (1, 6, 'super mega wow', 5), (2, 2, 'I was very meh', 2), (3, 5, 'absolutely ace', 5), (5, 1, 'what a stinker', 1), (5, 1, 'pretty not bad', 4), (7, 4, 'super-ordinary', 3), (2, 2, 'a nice quality', 4);

SQL Server: Preserve result

Result: Copy Clear

PHP code: Copy Format Clear

<?php // $mysqli is already declared as a working database connection by this sandbox $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']], 'stars' => $row['stars'] === null ? [] : [$row['stars']], 'reviews' => $row['stars'] === null ? 0 : 1, 'starSum' => $row['stars'], 'average' => $row['stars'], ]; $result[] = &$ref[$row['userId']]; } else { $ref[$row['userId']]['comments'][] = $row['comments']; $ref[$row['userId']]['stars'][] = $row['stars']; $ref[$row['userId']]['starSum'] += $row['stars']; ++$ref[$row['userId']]['reviews']; $ref[$row['userId']]['average'] = $ref[$row['userId']]['starSum'] / $ref[$row['userId']]['reviews']; } } var_export($result);

PHP version:

Show: Warnings Notice

Result: Copy Clear

PHPize Online / SQLize Online / SQLtest Online