SQLlite values into result array

Question

I have two SQL database tables. How am I able to get result array from data? Since it should be an array of grades which is going to standardDevation function. As I understand, it should look like this ['Alice' => [5, 4, 5], 'Bob' => [2]] if name is unique and I need each person list of grades. Prepare sentence is done, but problem is in first foreach loop.

Student table(id INTEGER PRIMARY KEY, name VARCHAR(255) UNIQUE);
Grade table(id INTEGER, grade INTEGER);

Student table     Grade table
id   name          id    grade
1   Alice           1      5
2   Bob             1      4
3   Carol           1      5
                    2      4

require_once 'functions.php';

$conn = getConnectionWithData('data.sql');

$stmt = $conn->prepare('select * from student inner join grade on grade.id = student.id' );

$stmt->execute();

$result = [];

foreach ($stmt as $row){
    $name = $row['name'];
    if(isset($result[$row['name']])){
        $result[$name] = [$row['grade']];
    }else{
        $result[$name] = [$row['grade']];
    }
}

foreach ($result as $name => $grades) {
    $std = standardDeviation($grades);
    print "$name: $std\n";

Answer 1

Very similar to your code but a different SQL:

-- SQLite syntax
select student.name, group_concat(grade.grade, ',') as grades
from student
inner join grade on grade.id = student.id
group by student.name

The PHP code:

require_once 'functions.php';
$conn = getConnectionWithData('data.sql');

// Extract grades as a comma-separated list of values per student in SQL
$sql = "select name, group_concat(grade, ',') grades from student inner join grade on grade.id = student.id group by name";
// There is no need to prepare a non-parameterized query
$results = $conn->query($sql);

foreach ($results as $result)
{
 // Make an array out of the comma-separated list of values before calling standardDeviation
 $std = standardDeviation(implode(',', $result['grades']));
 print "{$result['name']}: $std\n";
}