php rows in mysql as column

Question

I use codeigniter as framework. I want to make a table (in view file), which will show the exams as columns, and the students grades as rows.

First i made two tables in mysql: - one for the exams - one for the grades

My table in mysql for the exams is as follow:

table_exams
exams_id | title       | class_id
---------+-------------+---------
1        | mathematics | 5
2        | biology     | 5
3        | gymnastic   | 5
4        | pyhsics     | 3

and the second table in mysql for the grades is as follow:

table_grades
grades_id | exams_id | student_name | grade
----------+----------+--------------+------
1         | 1        | Jackson      | 5
2         | 1        | Lisa         | 4
3         | 2        | jackson      | 2
4         | 2        | Lisa         | 4
5         | 3        | Lisa         | 1

In php view file I want to get this:

if i want to see the exams for class 5

First column is the student name. the other columns are the exams (from mysql exams table). For each student in that class i want to get a row with the grades (getting from mysql table grades) like this:

studentname | mathematics | biology | gymnastic
------------+-------------+---------+---------------
Jackson     | 5           | 2       | no exams made
Lisa        | 4           | 4       | 1

i tried the following code for the headers

<th><?php echo get_phrase('student');?></th>
$this->db->where('class_id', 5);
$exams = $this->db->get('student_tb_exams')->result_array();
foreach($exams as $row):?>
    echo $row['title']; 
endforeach;

and for the table body

<tr>
<?php                   
    $this->db->select('*'); 
    $this->db->from('student_tb_grades');
    $this->db->join('student_tb_exams','student_tb_grades.exams_id = student_tb_exams.exams_id','INNER'); 
    $this->db->where('class_id', 5);
    $grades = $this->db->get()->result_array();
    foreach($grades as $row):?>
        <td><?php echo $row['student_id'];?></td>
        <?php echo $row['grade'];?></td>
    endforeach;

but i get:

studentname | mathematics | biology | gymnastic     |
------------+-------------+---------+---------------
Jackson     | 5           |         |               |
Lisa        | 4           |         |               |
Jackson     | 2           |         |               |
Lisa        | 4           |         |               |
Jackson     |             |         |               |
Lisa        | 1           |         |               |

How can i get one row for each student and the grades in the right column? So grade for math in the mathematics column, grade for biology in the biology column.

This has been answered here already [http://stackoverflow.com/questions/21548789/codeigniter-active-record-for-joining-the-tables/28326364#28326364][1] [1]: http://stackoverflow.com/questions/21548789/codeigniter-active-record-for-joining-the-tables/28326364#28326364 — Dave, Feb 04 '15 at 21:09

score 0 · Answer 1 · answered Feb 04 '15 at 20:41

0

I think your problem is in your SQL : $this->db->where('class_id', 5); Why you don't JOIN in exams_id for get each user all exams after you just need get grade for exams and user. And for get juste 2 row Jackson and Lisa you need to make GROUPE BY in you sql on student_name

answered Feb 04 '15 at 20:41

simon

1,180
3
12
33

if i use groupe_by i will get one row for Jackson and one row for Lisa, but it will only set the grade under column mathematics, the other columns will be empty – Cengiz Acar Feb 04 '15 at 20:45

php rows in mysql as column

1 Answers1