CODEWITHSUNDEEP
phphtmlmysql
elcicko
elcicko

Reputation: 117

Grouping Day in Schedule Table

please help me, i have a problem to design this schedule table. this is my current design https://www.dropbox.com/s/wcyqgkk7i0bm2cz/%202015-01-29%2000-19-46.png?dl=0 and i have no idea to make it like this https://www.dropbox.com/s/5ppchqypixix3no/result.jpg?dl=0 .

This is my query

SELECT a.*, b.`nama_kelas`, `c`.`nama_hari` FROM `tbl_jadwal` AS `a`
LEFT JOIN `tbl_kelas` AS `b` ON a.`id_kelas` = b.`id_kelas`
LEFT JOIN `tbl_hari` AS `c` ON a.`id_hari` = c.`id_hari`
ORDER BY `a`.`id_kelas`,a.`id_hari`,a.`id_jam_pel`,a.`kode_guru`,a.`id_mapel`

Hopefully if someone can help me with CSS too. but the most important thing is to grouping the day (sorry, the day's name language is Indonesian).

you can check my SQLfiddle : http://sqlfiddle.com/#!2/1837f

Please help me. Thank you

Upvotes: 0

Views: 57

Answers (1)

OneHoopyFrood
OneHoopyFrood

Reputation: 3959

What you're trying to do can't be done directly with SQL. It can't group across rows like that.

The best thing to do is break it into multiple queries like so:

Get the schedule: sqlfiddle

SELECT 
       h.id_hari,
       jp.jam_pel,
       g.nama_lengkap
FROM   tbl_jadwal j 
       LEFT JOIN tbl_kelas k
              ON j.id_kelas = k.id_kelas
       LEFT JOIN tbl_hari h 
              ON j.id_hari = h.id_hari
       LEFT JOIN tbl_jam_pel jp
              ON j.id_jam_pel = jp.id_jam_pel
       LEFT JOIN tbl_guru g
              ON j.kode_guru = g.kode_guru
ORDER  BY k.id_kelas, 
          h.id_hari, 
          jp.id_jam_pel, 
          j.kode_guru, 
          j.id_mapel

This next one lets you know how many hour blocks are in each day. You could also do this in your code by looping and counting if you prefer. The only constraint is that you know this number before the output loop begins. This means if you do this in code you'll need two loops. One to count, then one to display.

I've left all the JOINS in place here because I don't know all your constraints for what you're trying to show. You can eliminate them if you wish but this will make sure that you only count the rows for the above schedule.

Get the number of hours set to the each day: sqlfiddle

SELECT 
       h.id_hari,
       h.nama_hari,
       COUNT(h.id_hari) AS jam_untuk_hari
FROM   tbl_jadwal j 
       LEFT JOIN tbl_kelas k
              ON j.id_kelas = k.id_kelas
       LEFT JOIN tbl_hari h 
              ON j.id_hari = h.id_hari
       LEFT JOIN tbl_jam_pel jp
              ON j.id_jam_pel = jp.id_jam_pel
       LEFT JOIN tbl_guru g
              ON j.kode_guru = g.kode_guru
GROUP BY h.id_hari

You'll need to do the same sort of thing to get the number of hours in each lesson.

Next, to display this properly you'll use the rowspan attribute of a td element. Here's some pseudocode:

FOR hour in hours
    IF is first row of day THEN OUTPUT:
        <td rowspan="NUMBER_OF_HOURS_IN_DAY">NAMA_HARI</td>

    OUTPUT:
    <td>JAM</td>
    <td>NAMA_GURU</td> <!-- Here's where you'll need to do the same thing as above -->

The rowspan attribute is what allows you to group things like you're intending.

The final result would look something like this:

<table>
    <tr>
        <td rowspan="3">1</td>
        <td>2</td>
        <td>3</td>
    </tr>
    <tr>
        <td>2</td>
        <td>3</td>
    </tr>
    <tr>
        <td>2</td>
        <td>3</td>
    </tr>
    <tr>
        <td>1</td>
        <td>2</td>
        <td>3</td>
    </tr>
    <tr>
        <td>1</td>
        <td>2</td>
        <td>3</td>
    </tr>
</table>

Upvotes: 1

Related Questions