Read 199 times | Created 2013-08-05 11:52:14 | Updated 2013-10-30 00:10:10 | | |

 

<?php
/*********************************   
FILENAME    : mhs.php   
CREATE BY   : cahya dsn   
PURPOSE     : menampilkan nilai mahasiswa
CREATE DATE : 2013-08-05 
UPDATE DATE : 2013-10-30
********************************** 
use test;  
-- --------------------------------------------------------  
-- Table structure for table `tbl_mahasiswa`  
--  
DROP TABLE IF EXISTS `tbl_mahasiswa`;  
CREATE TABLE IF NOT EXISTS `tbl_mahasiswa` (  
  `id_mhs` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nim` varchar(10) NOT NULL,
  `nama_mhs` varchar(15) NOT NULL,
  PRIMARY KEY (`id_mhs`)  
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;  
--  
-- Dumping data for table `tbl_mahasiswa`  
--  
INSERT INTO `tbl_mahasiswa` (`id_mhs`, `nim`,`nama_mhs`) VALUES  
(1, '1301030001','ardila dewi'),  
(2, '1301040002','budi susanto'), 
(3, '1302030004','citra setyanti'),
(4, '1302040002','doni setyanto'),
(5, '1303030001','endah pramesti'),
(6, '1303030002','fajar putranto'),
(7, '1303040001','guruh indarto'),
(8, '1304030001','hamid subekti'),
(9, '1304030002','indah anggraeni'); 
 
DROP TABLE IF EXISTS `tbl_matakuliah`;  
CREATE TABLE IF NOT EXISTS `tbl_matakuliah` (  
  `id_mk` smallint(4) unsigned NOT NULL AUTO_INCREMENT,  
  `kd_mk` varchar(10) NOT NULL,
  `nama_mk` varchar(15) NOT NULL,
  PRIMARY KEY (`id_mk`)  
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;  
 
INSERT INTO `tbl_matakuliah`(`id_mk`,`kd_mk`,`nama_mk`) VALUES
(1,'BHS1307001','Bahasa Inggris'),
(2,'BHS1307002','Bahasa Jepang'),
(3,'BHS1307003','Bahasa Perancis'),
(4,'BHS1307004','Bahasa Jerman');
 
DROP TABLE IF EXISTS `tbl_nilai`;  
CREATE TABLE IF NOT EXISTS `tbl_nilai` (  
  `id_nilai` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_mhs` int(11) unsigned NOT NULL,
  `id_mk` smallint(4) unsigned NOT NULL,
  `nilai` char(1) NOT NULL,
  PRIMARY KEY (`id_nilai`)  
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;  
 
INSERT INTO `tbl_nilai`(`id_mhs`,`id_mk`,`nilai`) VALUES
(1,1,'B'),
(1,2,'C'),
(1,3,'C'),
(2,1,'C'),
(2,2,'B'),
(2,3,'C'),
(3,1,'D'),
(3,2,'A'),
(3,3,'B'),
(4,1,'B'),
(4,2,'A'),
(4,3,'A');
*/
//database configuration
$dbhost='localhost'; 
$dbuser='root'; 
$dbpass=''; 
$dbname='test'; 
//database connection 
$db=new mysqli($dbhost,$dbuser,$dbpass,$dbname);  
?>
<!DOCTYPE html> 
<html> 
  <head> 
    <title>Data Mahasiswa</title> 
    <style type="text/css">  
      *{font-family:calibri,arial,san-serif;font-size:12px;}  
      table {padding:2px;}  
      td,th{padding:2px;border:solid 1px #999;} 
    </style>
  </head>
  <body>
    <table>
    <?php
function show_table($db)
{
    $sql="SELECT 
            mh.nim,
            mh.nama_mhs,
            GROUP_CONCAT(CONCAT('\"',mk.nama_mk,'\":\"',n.nilai,'\"')) AS nilai
          FROM
            tbl_nilai n
            JOIN tbl_mahasiswa mh USING(id_mhs)
            JOIN tbl_matakuliah mk USING(id_mk)
          GROUP BY 
            n.id_mhs";
     $data=array();
     echo "<table>";
     if($result=$db->query($sql))
     {
       $mk=array();
       $i=0;
       while($record=$result->fetch_object())
       {
          $data[]=array(
                'nim'=>$record->nim,
                'nama'=>$record->nama_mhs,
                'nilai'=>json_decode('{'.$record->nilai.'}',true)
                );
          $mk=array_merge($mk,array_keys($data[$i]['nilai']));
          ++$i;
       }
       $result->free();
     }
     $mk=array_unique($mk);
     sort($mk);
     $n=count($mk);
     echo "<tr>
            <th rowspan='2'>No</th>
            <th rowspan='2'>NIM</th>
            <th rowspan='2'>Nama</th>
            <th colspan='$n'>Nilai</th>
           </tr>
           <tr>";
     foreach($mk as $k)
     {
       echo "<th>$k</th>";
     }
     echo "</tr>";
     for($c=0;$c<$i;$c++){
        echo "<tr>
                <td>".($c+1)."</td>
                <td>".$data[$c]['nim']."</td>
                <td>".ucwords($data[$c]['nama'])."</td>";
        for($j=0;$j<$n;$j++){
         echo  "<td>".(isset($data[$c]['nilai'][$mk[$j]])?$data[$c]['nilai'][$mk[$j]]:'&nbsp;')."</td>";
        }
        echo "</tr>";
     }
     echo "</table>";
 }
//tampilkan data yang ada di database
show_table($db);
 
//hapus data pada tabel 'tbl_nilai' dengan id=1
$sql="DELETE FROM tbl_nilai
      WHERE id_nilai=1";
if($db->query($sql))
{
  show_table($db);
}
 
//tambahkan data nilai baru untuk mk dan mahasiswa yg lain
$sql="INSERT INTO tbl_nilai(`id_mhs`,`id_mk`,`nilai`)
      VALUES (5,4,'A')";
if($db->query($sql))
{
  show_table($db);
}
$db->close();
    ?>
    </table>
  </body>
</html>