Read 150 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();  
}
?>