How to display multiple record as table head and final based on table head

Question

I confused about the title that i want to ask. I'm working on this, when i want to see students report the value for each theme.

Below is my subject table on database.

+-----------+----------------------+----------------------+
| SubjectID |      SubjectName     |       ThemeName      |
+-----------+----------------------+----------------------+
|     1     |       Subject1       |        Myself        |
|     2     |       Subject1       |       My Hobbies     |
|     3     |       Subject1       |      My Activity     |
|     4     |       Subject1       |       My Family      |
|     5     |       Subject2       |     My Experience    |
|     6     |       Subject2       |      Environment     |
|     7     |       Subject2       |        Things        |
|     8     |       Subject2       |     Nature Incident  |
+-----------+----------------------+----------------------+

And this one is table value for each theme.

+-----------+-----------+-----------+------------+
|  ValueID  | StudentID | SubjectID | FinalScore |
+-----------+-----------+-----------+------------+
|     1     |    112    |     1     |     80     |
|     2     |    112    |     2     |     90     |
|     3     |    112    |     3     |     50     |
|     4     |    112    |     4     |     70     |
|     5     |    113    |     1     |     60     |
|     6     |    113    |     2     |     40     |
|     7     |    113    |     3     |     50     |
|     8     |    113    |     4     |     90     |
+-----------+-----------+-----------+------------+

I want to display on my html just like table below

+----+-----------+------------+------------+------------+------------+
| No | StudentID | ThemeName1 | ThemeName2 | ThemeName3 | ThemeName3 |
+----+-----------+------------+------------+------------+------------+
| 1  |    112    |     80     |     90     |     50     |     70     |
| 2  |    113    |     60     |     40     |     50     |     90     |
+----+-----------+------------+------------+------------+------------+

This table that i'm working now. Ignore for an empty , i will delete it soon.

<table cellspacing="0" border="0" class="table display table-bordered" id="table1">
                <thead>
                    <tr>
                        <th width="1%"><center>No</th>
                        <th width="10%"><center>Nama Siswa</th>
                        <th width="12%"><center>Nama Mapel</th>
                        <th width="25%"><center>Diri Sendiri</th>
                        <th width="5%"><center>Kegemaranku</th>
                        <th width="5%"><center>Kegiatanku</th>
                        <th width="5%"><center>Keluargaku</th>
                        <th width="5%"><center>TG4</th>
                        <th width="1%"><center>Aksi</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                        $no = 1;
                        $query = mysql_query("SELECT NISN, MAX(CASE WHEN KodeMapel = 29 THEN NilaiAkhir END) AS Diri Sendiri, MAX(CASE WHEN KodeMapel = 30 THEN NilaiAkhir END) AS Kegemaranku, MAX(CASE WHEN KodeMapel = 31 THEN NilaiAkhir END) AS Kegiatanku, MAX(CASE WHEN KodeMapel = 32 THEN NilaiAkhir END) AS Keluargaku FROM pengetahuan
                        GROUP BY NISN");
                        while ($pengetahuan = mysql_fetch_array($query)) 
                        {
                            $kode       = $pengetahuan['KodeP'];
                            $nisn       = $pengetahuan['NISN'];
                            $namasiswa  = $pengetahuan['NamaSiswa'];
                            $namatema   = $pengetahuan['NamaTema'];
                            $namamapel  = $pengetahuan['NamaMapel'];
                            $na         = $pengetahuan['NilaiAkhir'];
                    ?>  
                        <tr>
                            <td>
                                <center>
                                    <?php echo $no++; ?>
                                </center>
                            </td>
                            <td>
                                <center><?php echo $namasiswa; ?>
                            </td>
                            <td>
                                <center><?php echo $namamapel; ?>
                            </td>
                            <td>
                                <?php echo $na; ?>
                            </td>
                            <td>
                            <center>

                            </td>
                            <td>
                            <center>

                            </td>
                            <td>
                            <center>
                                <?php 

                                ?>
                            </td>
                            <td>
                            <center>
                                <?php 

                                ?>
                            </td>
                            <td>
                            <center>
                                <?php 

                                ?>
                            </td>
                            <td>
                            <center>
                                <?php

                                ?>
                            </td>
                            <td>
                            <center>
                                <?php

                                ?>
                            </td>
                            <td>
                                <center>
                                    <button data-id="<?php echo "$kode"; ?>" type="button" class="btn btn-info btn-sm" data-toggle="modal" data-target="#myModal12"><i class="fa fa-pencil fa-fw"></i> Tambah Nilai</button>
                                    <a href="Pengetahuan/Tema4/action.php?KodeP= <?php echo $$kode; ?>" class="btn btn-danger btn-sm" id="submit" type="submit"/>
                                    <i class="fa fa-trash fa-fw"></i> Hapus </a>
                                </center>   
                            </td>       
                        </tr>
                    <?php                               
                        }
                    ?>
                </tbody>
            </table>

Is it possible to do? Thank you

If theme names could be more than this, then consider implementing `dynamic pivot`, Check [this question](https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) for the same. — Utsav, May 25 '17 at 04:38

score 0 · Accepted Answer · answered May 25 '17 at 04:30

0

You can use the following pivot query to get the general table you want in your output:

SELECT
    StudentID,
    MAX(CASE WHEN SubjectID = 1 THEN FinalScore END) AS ThemeName1,
    MAX(CASE WHEN SubjectID = 2 THEN FinalScore END) AS ThemeName2,
    MAX(CASE WHEN SubjectID = 3 THEN FinalScore END) AS ThemeName3,
    MAX(CASE WHEN SubjectID = 4 THEN FinalScore END) AS ThemeName4
FROM yourTable
GROUP BY StudentID

To generate the No column, you can handle this in your PHP code when you iterate over the result set.

answered May 25 '17 at 04:30

Tim Biegeleisen

502,043
27
286
360

It returns warning message : mysql_fetch_array() expects parameter 1 to be resource, boolean given in. I'm using while to do the query. – gray bullet171 May 25 '17 at 05:03
I can't see your code so therefore I cannot help you. – Tim Biegeleisen May 25 '17 at 05:08
See [here](http://php.net/manual/en/function.mysql-fetch-array.php) for documentation on `mysql_fetch_array()`. From what I read, this function/API is deprecated and you should upgrade. – Tim Biegeleisen May 25 '17 at 05:23
My view is that, in the context of application code, this answer is not useful. – Strawberry May 25 '17 at 08:23
If the answer is accepted, it means it works for OP and that is a sufficient evidence that the answer *is* useful. – Wiktor Stribiżew Oct 17 '19 at 07:53

How to display multiple record as table head and final based on table head

1 Answers1