Read 135 times | Created 2014-04-22 03:07:21 | Updated 2014-04-22 03:07:21 | | |

 

<?php
/*
-- -
USE `test`;

DROP TABLE IF EXISTS `mapel`;
CREATE TABLE IF NOT EXISTS `mapel` (
 `id_mp` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `nama_mp` varchar(30) NOT NULL,
 PRIMARY KEY(`id_mp`)
) ENGINE=MyISAM;

INSERT INTO `mapel`
VALUES(1,'Ujian Masuk');

DROP TABLE IF EXISTS `ujian`;
CREATE TABLE IF NOT EXISTS `ujian` (
 `id_ujian` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `id_mp` int(11) unsigned NOT NULL,
 `nama_ujian` varchar(30) NOT NULL,
 PRIMARY KEY(`id_ujian`)
) ENGINE=MyISAM;

INSERT INTO `ujian`
VALUES
(1,1,'Bahasa Inggris'),
(2,1,'Bahasa Indonesia'),
(3,1,'Matematika'),
(4,1,'IPA Terpadu');

DROP TABLE IF EXISTS `siswa`;
CREATE TABLE IF NOT EXISTS `siswa` (
 `nis` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `nama` varchar(30) NOT NULL,
 PRIMARY KEY(`nis`)
) ENGINE=MyISAM;

INSERT INTO `siswa`
VALUES
(1,'Agus'),
(2,'Budi'),
(3,'Carlie'),
(4,'Dewi'),
(5,'Endah'),
(6,'Fitri'),
(7,'Gita');

DROP TABLE IF EXISTS `nilai`;
CREATE TABLE IF NOT EXISTS `nilai` (
 `id_nilai` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `id_ujian` int(11) unsigned NOT NULL,
 `id_user` int(11) unsigned NOT NULL,
 `nilai` int(11) unsigned  NOT NULL,
 PRIMARY KEY(`id_nilai`)
) ENGINE=MyISAM;

INSERT INTO `nilai`
VALUES
(1,1,1,40),
(2,1,2,100),
(3,1,3,80),
(4,1,4,60);

SELECT 
  a.nis,
  a.nama,
  SUM(IF(d.id_ujian=1,d.nilai,0)) AS ujian_1,
  SUM(IF(d.id_ujian=2,d.nilai,0)) AS ujian_2,
  SUM(IF(d.id_ujian=3,d.nilai,0)) AS ujian_3,
  SUM(IF(d.id_ujian=4,d.nilai,0)) AS ujian_4,
  SUM(IF(ISNULL(d.nilai),0,d.nilai))/4 as rerata
FROM siswa a
  LEFT JOIN ( 
    SELECT b.*
    FROM nilai b
    LEFT JOIN ujian c ON b.id_ujian=c.id_ujian
    WHERE c.id_mp='1'
  ) d  ON a.nis=d.id_user
GROUP BY a.nis
ORDER BY rerata DESC;

-- -*/

$dbhost='localhost';
$dbuser='root';
$dbpass='greatday35';
$dbname='t';
$id_mp=1;
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);
$sql="SELECT id_ujian,nama_ujian
      FROM ujian
      WHERE id_mp='".$id_mp."'
      ORDER BY id_ujian";
$result=$db->query($sql);
$head=array();
$id_head=array();
$sql_nilai='';
while($row=$result->fetch_object()){
  $head[]=$row->nama_ujian;
  $id_head[]=$row->id_ujian;
  $sql_nilai.="SUM(IF(d.id_ujian={$row->id_ujian},d.nilai,0)) AS nilai_{$row->id_ujian},";
}
$result->close();
$sql="SELECT 
        a.nis,
        a.nama,"
        .$sql_nilai
       ."SUM(IF(ISNULL(d.nilai),0,d.nilai))/".count($head)." as rerata
      FROM siswa a
        LEFT JOIN ( 
          SELECT b.*
          FROM nilai b
          LEFT JOIN ujian c ON b.id_ujian=c.id_ujian
          WHERE c.id_mp='".$id_mp."'
        ) d  ON a.nis=d.id_user
      GROUP BY a.nis
      ORDER BY rerata DESC";
$result=$db->query($sql);
?>
<!DOCTYPE html>
<head>
  <title>Rerata</title>
</head>
<body>
  <fieldset>
    <legend>Daftar Nilai Ujian</legend>
    <table border='1'>
      <thead>
        <tr>
          <th>No</th>
          <th>NIS</th>
          <th>NAMA</th>
          <th><?php echo implode("</th><th>",$head);?></th>
          <th>Rata-rata</th>
          <th>Opsi</th>
        </tr>
      </thead>
      <tbody>
      <?php
      $no=1;
      while($row=$result->fetch_assoc()){
        echo "
        <tr>
          <td>".($no++)."</td>
          <td>".str_pad($row['nis'],4,'0', STR_PAD_LEFT)."</td>
          <td>{$row['nama']}</td>";
        for($i = 0;$i < count($head); $i++){
          echo "
          <td align='right'>".number_format($row["nilai_".$id_head[$i]],1)."</td>";
        }      
        echo "
          <td align='right'>".number_format($row['rerata'],3)."</td>
          <td>-</td>
        </tr>";
      }
      ?>  
      </tbody>
    </table>
  </fieldset>
</body>
</html>