Read 890 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]]:' ')."</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>