MySQL fetch 2 related tables into sub array

Question

I have a MySQL database that contains the following tables:

Student table that looks like that:

|------------------------------------|
| studentId | studentName | school   |
|------------------------------------|
| 1         | Student A   | 1        |
| 2         | Student B   | 2        |
| 3         | Student C   | 2        |
|------------------------------------|

And a School table:

|------------------------------|
| schoolId | schoolName | desc |
|------------------------------|
| 1        | School A   | ...  |
| 2        | School B   | ...  |
| 3        | School C   | ...  |
|------------------------------|

Using PHP, I'm trying to fetch an array of all students and include their school information in a sub array.

Array
(
    [0] => Array
        (
            [studentId] => 1
            [studentName] => Student A
            [school] => Array
                (
                    [schoolId] => 1
                    [schoolName] => School A
                    [desc] => ...
                )
        )
    [1] => Array
        (
            [studentId] => 1
            [studentName] => Student A
            [school] => Array
                (
                    [schoolId] => 1
                    [schoolName] => School A
                    [desc] => ...
                )
        )
    [2] => Array
        (
            [studentId] => 1
            [studentName] => Student A
            [school] => Array
                (
                    [schoolId] => 1
                    [schoolName] => School A
                    [desc] => ...
                )
        )
)

I was able to achieve that by doing this

$studentsResult = $conn->query("SELECT * FROM STUDENT");
$studentsArray = $studentsResult->fetch_all(MYSQLI_ASSOC);

$finalArray = array();

foreach ($studentsArray as &$student) { 
    $schoolSQL = "SELECT * FROM SCHOOL WHERE schoolId = ".$student['schoolId'];
    $schoolResult = $conn->query($schoolSQL);
    $schoolArray = $schoolResult->fetch_all(MYSQLI_ASSOC);

    unset($student['schoolId']);
    $student['school'] = $schoolArray[0];
    $finalArray[] = $student;
}
echo '<pre>'; print_r($finalArray);

But I don't think this way is the most efficient for a large database since I'm looping through all the students and executing a query to get school row.

Is there anyway I can do all that by using only SQL?

Use a JOIN to get the school info and the student info together in the same row. Then just loop through like you are now and perform a similar manipulation to create the sub-array. — Patrick Q
– Patrick Q, Commented Nov 18, 2016 at 15:14

Kickstart · Accepted Answer · 2016-11-18 15:20:52Z

2

Do a join, then loop around only adding a student when the student changes. But just add the school details to the students school array.

Something like this

<?php

$studentsResult = $conn->query("SELECT a.studentId,
                                        a.studentName,
                                        b.schoolId,
                                        b.schoolName,
                                        b.desc
                                FROM STUDENT a
                                LEFT OUTER JOIN SCHOOL b
                                ON a.schoolId = b.schoolId");

$prev_student = 0;
$studentsArray = $studentsResult->fetch_all(MYSQLI_ASSOC);

$finalArray = array();

foreach ($studentsArray as &$student) 
{ 
    if ($prev_student != $student['studentId'])
    {
        $finalArray[] = array('studentId' => $student['studentId']
                                'studentName' => $student['studentName']
                                'school' => array());
        $prev_student = $student['studentId'];
    }
    $finalArray[key($finalArray)][] = array('schoolId ' => $student['schoolId']
                                            'schoolName ' => $student['schoolName']
                                            'desc ' => $student['desc']);
}

echo '<pre>'; print_r($finalArray);

answered Nov 18, 2016 at 15:20

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fouad Over a year ago

Thank you, I used your Join SQL query but no need for all this in the PHP loop. I done it using: $finalArray[] = array('studentId' => $student['studentId'], 'studentName' => $student['studentName'],

'school' => array('schoolId ' => $student['schoolId'], 'schoolName ' => $student['schoolName'], 'desc ' => $student['desc']));

Kickstart Over a year ago

@iDev - I did it like that so it would support multiple schools which I assumed you wanted to do (as you wanted the school details within an array for each student).

Xorifelse · Accepted Answer · 2016-11-18 15:31:37Z

1

You should read on joining, very simple to use.

SELECT a.studentName, b.schoolName, b.desc
FROM students AS a
INNER JOIN school AS b
ON a.school = b.schoolId

This way, you combine all the data from multiple tables as 1 result.

echo $row['studentName'], ' is in this school ', $row['schoolName'], ' etc..';

edited Nov 18, 2016 at 15:31

answered Nov 18, 2016 at 15:15

Xorifelse

7,9411 gold badge29 silver badges38 bronze badges

Collectives™ on Stack Overflow

MySQL fetch 2 related tables into sub array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related