PHP. Building multidimensional array from result table

Question

I have two mysql tables with students and school subjects that look like this:

Subjects

id  |  name
=================
1   |  History
2   |  English
3   |  Science
4   |  Geography

Students

id  |  name
=================
1   |  Sergey
2   |  Dmitriy
3   |  Vladislav

The third table establishes a many-to-many relationship between Students and Subjects tables and also has marks column:

Marks

id  |  student_id  |  subject_id  |  mark
==========================================
1   |      1       |      2       |  3
2   |      2       |      2       |  5
3   |      3       |      3       |  4
4   |      1       |      4       |  5
5   |      3       |      4       |  3
6   |      2       |      3       |  4
7   |      3       |      2       |  3
8   |      1       |      1       |  4

The goal is to build a multi-dimensional array that would end up looking like below. It has to return "null" value in "mark" key if there is no such student and subject match. So it should have 12 elements for 3 students and 4 subjects (3 multiply 4).

I tried this sql query but is doesn't return records with "null" value, but records only from "Marks" table:

SELECT marks.id AS id, 
   students.name AS student,
   marks.mark AS mark,
   subjects.name AS subject
FROM students 
    LEFT OUTER JOIN marks ON students.id = marks.student
    LEFT OUTER JOIN subjects ON marks.subject = subject.id

Desired array:

Array
(
    [
        "id" => 1,
        "student" => "Sergey",
        "subject" => "History",
        "mark" => 4,
    ],
    [
        "id" => 2,
        "student" => "Sergey",
        "subject" => "English",
        "mark" => 3,
    ],
    ...
    [
        "id" => 9,
        "student" => "Vladislav",
        "subject" => "History",
        "mark" => null,
    ],
    ...
    [
        "id" => 12,
        "student" => "Vladislav",
        "subject" => "Geography",
        "mark" => 3,
    ],
)

Does anyone know how to do this? Thank you and hope you can help me.

Mathias-S · Accepted Answer · 2019-12-06 17:52:30Z

1

So it should have 12 elements for 3 students and 4 subjects (3 multiply 4).

What you are asking here is for a CROSS JOIN between students and subjects which will give you the cartesian product of these two tables. This is the important part. By cross joining students and subjects, you get the combination of all students and all subjects.

Then, you can LEFT JOIN that part with the marks table, like this:

SELECT marks.id AS mark_id, 
   students.name AS student,
   marks.mark AS mark,
   subjects.name AS subject
FROM students CROSS JOIN subjects
   LEFT JOIN marks
      ON students.id = marks.student_id AND marks.subject_id = subjects.id;

See working example at http://sqlfiddle.com/#!9/57ffd8/1

answered Dec 6, 2019 at 17:52

Mathias-S

8054 silver badges10 bronze badges

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

Comments

GMB · Accepted Answer · 2019-12-06 17:57:03Z

I would approach this by generating a cartesian product of students and subjects with a cross join: this gives you all possible combinations of students and subjects. Then, you can bring the marks with a left join:

select 
    marks.id as id, 
    students.name as student,
    marks.mark as mark,
    subjects.name as subject
from students 
cross join subjects
left join marks on marks.student_id = students.id and marks.subject_id = subjects.id

Demo on DB Fiddle:

  id | student   | mark | subject  
---: | :-------- | ---: | :--------
   1 | Sergey    |    3 | English  
   2 | Dmitriy   |    5 | English  
   3 | Vladislav |    4 | Science  
   4 | Sergey    |    5 | Geography
   5 | Vladislav |    3 | Geography
   6 | Dmitriy   |    4 | Science  
   7 | Vladislav |    3 | English  
   8 | Sergey    |    4 | History  
null | Dmitriy   | null | History  
null | Vladislav | null | History  
null | Sergey    | null | Science  
null | Dmitriy   | null | Geography

TorentinC · Accepted Answer · 2019-12-06 18:04:03Z

After reading your post my take on your problem is that no request can yield exactly the result you want.

Let's analyze the semantic of your tables :

Subjects defines all the subjects
Students defines all the students
Marks records all the results of tests taken by Students in Subjects

Thus, in your MySQL query you're asking the engine to first select all students existing

FROM students

Then you want to retrieve all the existing marks for those students or null if no mark at all exists for a student

LEFT OUTER JOIN marks ON students.id = marks.student

Note that you've writtent marks.student but defined marks.student_id above, typo assumed here.

The next step is where the trick is. At this point you'll have a result looking like this :

id | student   | mark 
=====================
1  | Sergey    | 3
2  | Dmitriy   | 5

Note : I shortened the results for readability purposes

Your next move desired is to join the Subjects table to add their labels. But joining Subjects table as you do here ...

LEFT OUTER JOIN subjects ON marks.subject = subject.id

Note that once again typo is assumed here, marks.subject instead of marks.subject_id

... will only yield the labels for Subjects which id are in any lines previously yielded by joining the Marks table.

Solution :

I suggest you define a table recording which subject any given student takes. Something like this :

Attends
id | student_id | subject_id
============================
1  | 1          | 2
2  | 2          | 4

Your request would look like this :

SELECT Marks.id AS id,
Students.name AS student,
Marks.mark AS mark,
Subjects.name AS subject
FROM Students 
JOIN Attends ON Students.id = Attends.student_id
JOIN Subjects ON Subjects.id = Attends.subject_id
LEFT JOIN marks ON Marks.subject = Subjects.id

EDIT : As pointed out by GMB & Mathias-S, a cross join may be easier as it doesn't require any new table.

Collectives™ on Stack Overflow

PHP. Building multidimensional array from result table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related