Q:

PHP. Building multidimensional array from result table

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.

A:

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

