Read 309 times | Created 2013-09-08 08:23:11 | Updated 2013-10-30 02:37:53 | | |
<?php /********************************* FILENAME : ipk.r1.php CREATE BY : cahya dsn PURPOSE : calculate ipk r.1 CREATE DATE : 2013-09-08 ********************************** #table creation USE `test`; DROP TABLE IF EXISTS `t_nilai`; CREATE TABLE IF NOT EXISTS `t_nilai` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key untuk table t_nilai', `nim` char(3) NOT NULL COMMENT 'nim', `kode_mk` char(3) NOT NULL COMMENT 'kode_mk', `jml_sks` tinyint(2) unsigned NOT NULL, `angka_nilai` tinyint(2) unsigned NOT NULL, `semester` tinyint(2) unsigned NOT NULL, PRIMARY KEY(`id`) ) ENGINE=MyISAM COMMENT='tabel untuk menyimpan data nilai'; INSERT INTO `t_nilai`(`nim`,`kode_mk`,`jml_sks`,`angka_nilai`,`semester`) VALUES ('001','AB1',2,4,1), ('001','AB2',3,0,1), ('001','AB3',2,2,2), ('001','AB4',3,3,2), ('001','AB2',3,3,3), ('001','AB5',3,2,3), ('002','AB1',2,4,1), ('002','AB2',3,3,1), ('002','AB3',2,3,2), ('002','AB4',3,4,2), ('002','AB5',3,3,3), ('002','AB6',3,3,3); */ $dbhost='localhost'; $dbuser='root'; $dbpass=''; $dbname='test'; $db=new mysqli($dbhost,$dbuser,$dbpass,$dbname); $sql="SELECT nim, SUM(IF(semester=1,jml_sks*angka_nilai,0))/SUM(IF(semester=1,jml_sks,0)) AS ipk1, SUM(IF(semester<=2,jml_sks*angka_nilai,0))/SUM(IF(semester<=2,jml_sks,0)) AS ipk2 FROM t_nilai GROUP BY nim "; $data=array(); if($result=$db->query($sql)) { echo "<table border='1'>" ."<tr><th>nim</th><th>Semester1</th><th>Semester2</th></tr>"; while($record=$result->fetch_object()) { echo "<tr><td>$record->nim</td><td>$record->ipk1</td><td>$record->ipk2</td></tr>"; $data[$record->nim]=array('nim'=>$record->nim,'ipk1'=>$record->ipk1,'ipk2'=>$record->ipk2); } echo "</table>"; $result->close(); } $sql="SELECT nim, SUM(jml_sks*angka_nilai)/SUM(jml_sks) AS ipk3 FROM ( SELECT nim, kode_mk, jml_sks, MAX( angka_nilai ) as angka_nilai , semester FROM `t_nilai` WHERE semester<=3 GROUP BY nim, kode_mk, jml_sks ) nilai GROUP BY nim"; if($result=$db->query($sql)){ echo "<table border='1'>" ."<tr><th>nim</th><th>Semester1</th><th>Semester2</th><th>Semester3</th></tr>"; while($record=$result->fetch_object()) { $data[$record->nim]['ipk3']=$record->ipk3; echo "<tr>" ."<td>".$data[$record->nim]['nim']."</td>" ."<td>".$data[$record->nim]['ipk1']."</td>" ."<td>".$data[$record->nim]['ipk2']."</td>" ."<td>".$data[$record->nim]['ipk3']."</td>" ."</tr>"; } echo "</table>"; $result->close(); } ?>