What you wish to do is known as "pivoting" your data and is something for which some other RDBMS have native support, but MySQL does not (by design, as the developers feel that such manipulations belong in the presentation layer).
However, you have a few options:
Construct a rather horrible MySQL query to perform the pivoting operation manually:
SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP FROM gp NATURAL JOIN ( SELECT student_id, grade AS MAT111 FROM result WHERE course_code = 'MAT111' ) AS tMAT111 NATURAL JOIN ( SELECT student_id, grade AS MAT112 FROM result WHERE course_code = 'MAT112' ) AS tMAT112 NATURAL JOIN ( -- etc.
If you choose to go down this path, you can make your life slightly easier by generating this query automatically, using either a looping construct in PHP or a prepared statement in MySQL.
Do the above as a one-off operation so that the structure of your MySQL database is changed to more closely reflect this desired layout (easy once table is converted, but may impact other uses of the database):
CREATE TABLE StudentGrades (PRIMARY KEY('Matriculation')) SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP -- etc. as above
Alternatively, you can create a
VIEWwhich is a sort of "virtual table" structured in this way based on the underlying table.Pivot the data manually in PHP (relatively tedious).