1. Buat Tabel Lookup
Buat tabel baru dengan nama jenis_barang seperti script di bawah ini :
USE `db_latihan`;
CREATE TABLE IF NOT EXISTS `jenis_barang` (
`id_jenis` int(11) NOT NULL AUTO_INCREMENT,
`jenis_barang` varchar(50) DEFAULT '0',
`user_name` varchar(25) DEFAULT NULL,
`tgl_proses` datetime DEFAULT NULL,
PRIMARY KEY (`id_jenis`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
INSERT INTO `jenis_barang` (`id_jenis`, `jenis_barang`, `user_name`, `tgl_proses`) VALUES
(1, 'LAPTOP', 'ADMIN', '2013-11-18 15:34:09'),
(2, 'STORAGE', 'ADMIN', '2013-11-18 15:34:22'),
(3, 'AKSESORIS', 'ADMIN', '2013-11-18 15:34:33');
Buat field baru dengan nama id_jenis di tabel barang lengkap dengan kunci tamu-nya (foreign key)
ALTER TABLE `barang`
ADD COLUMN `id_jenis` INT NULL DEFAULT NULL AFTER `nama_barang`,
ADD CONSTRAINT `FK_barang_jenis_barang`
FOREIGN
KEY
(`id_jenis`) REFERENCES `jenis_barang` (`id_jenis`);
Sesuaikan isi field id_jenis di tabel barang (tidak NULL) seperti gambar berikut :
2. Ubah Tampilan Daftar Barang
Ubah script file barang_tampil.php sehingga menjadi tampilan berikut :
<?php
include ("koneksi.php");
$query = "select b.*,
jb.jenis_barang from barang b left outer join jenis_barang jb on b.id_jenis =
jb.id_jenis";
$result = mysql_query($query);
$total = mysql_num_rows($result);
?>
<html>
<head>
<title>tampil barang</title>
</head>
<body>
<h2>DAFTAR BARANG</h2>
<p><a href="barang_tambah.php">TAMBAH</a> | <a href="barang_cari.php">CARI</a></p>
<table border="1" cellspacing="1" cellpadding="1" class="display" id="example">
<tr>
<td>ID BARANG</td>
<td>NAMA BARANG</td>
<td>JENIS</td>
<td>SATUAN</td>
<td>HARGA BELI</td>
<td>HARGA JUAL</td>
<td>JUMLAH STOK</td>
<td>USER NAME</td>
<td>TGL. PROSES</td>
<td>PROSES</td>
</tr>
<?php while ($data = mysql_fetch_array($result)) { ?>
<tr>
<td><?php echo $data['id_barang']; ?></td>
<td><?php echo $data['nama_barang']; ?></td>
<td><?php echo $data['jenis_barang']; ?></td>
<td><?php echo $data['satuan']; ?></td>
<td><?php echo $data['harga_beli']; ?></td>
<td><?php echo $data['harga_jual']; ?></td>
<td><?php echo $data['jumlah_stok']; ?></td>
<td><?php echo $data['user_name']; ?></td>
<td><?php echo $data['tgl_proses']; ?></td>
<td><a href="barang_ubah.php?id=<?php echo $data['id_barang']; ?>">UBAH</a> |
<a href="barang_hapus_query.php?id=<?php echo $data['id_barang']; ?>"
onclick="return confirm('HAPUS BARANG : <?php echo $data['nama_barang']; ?> ?')">HAPUS</a></td>
</tr>
<?php } ?>
</table>
<p>JUMLAH : <?php echo $total; ?> DATA</p>
</body>
</html> 3. Ubah Tampilan Tambah Barang
Ubah script file barang_tambah.php
<?
include ("koneksi.php");
$query = "select
id_jenis, jenis_barang from jenis_barang";
$result = mysql_query($query);
?>
<html>
<head>
<title>tambah barang</title>
</head>
<body>
<h2>TAMBAH BARANG</h2>
<form method="post" action="barang_tambah_query.php">
<table border="1" cellspacing="1" cellpadding="1">
<tr>
<td>ID BARANG :</td>
<td><input type="text" name="id_barang" disabled></td>
</tr>
<tr>
<td>NAMA BARANG :</td>
<td><input type="text" name="nama_barang"></td>
</tr>
<tr>
<td>JENIS :</td>
<td><select name="id_jenis">
<?php
while ($data = mysql_fetch_array($result)) {
?>
<option value="<?php echo $data['id_jenis']; ?>">
<?php echo $data['jenis_barang']; ?></option>
<?php
}
?></select></td>
</tr>
<tr>
<td>SATUAN :</td>
<td><input type="text" name="satuan"></td>
</tr>
<tr>
<td>HARGA BELI :</td>
<td><input type="text" name="harga_beli"></td>
</tr>
<tr>
<td>HARGA JUAL :</td>
<td><input type="text" name="harga_jual"></td>
</tr>
<tr>
<td>JUMLAH STOK :</td>
<td><input type="text" name="jumlah_stok"></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="submit" value="SIMPAN">
<input type="button" value="BATAL" onclick="self.history.back();"></td>
</tr>
</table>
</form>
</body>
</html>
Ubah script file barang_tambah_query.php
<?php
include("koneksi.php");
$query = "insert into
barang
(id_barang,
nama_barang, id_jenis, satuan,
harga_beli, harga_jual,
jumlah_stok, user_name, tgl_proses)
values (NULL, '$_POST[nama_barang]',
'$_POST[id_jenis]', '$_POST[satuan]',
'$_POST[harga_beli]',
'$_POST[harga_jual]', '$_POST[jumlah_stok]', 'ADMIN',
now())";
$result = mysql_query($query);
if ($result) {
header("location:barang_tampil.php");
}
else {
echo "proses simpan gagal !.";
}
?>
4. Ubah Tampilan Ubah Barang
Ubah script file barang_ubah.php
<?php
include ("koneksi.php");
$query = "select *
from barang where id_barang = '$_GET[id]'";
$result = mysql_query($query);
$data = mysql_fetch_array($result);
$query_jenis = "select
id_jenis, jenis_barang from jenis_barang";
$result_jenis = mysql_query($query_jenis);
?>
<html>
<head>
<title>ubah barang</title>
</head>
<body>
<h2>UBAH BARANG</h2>
<form method="post" action="barang_ubah_query.php">
<table border="1" cellspacing="1" cellpadding="1">
<tr>
<td>ID BARANG :</td>
<td><input type="text" name="id_barang" readonly="true" value="<?php echo $data['id_barang']; ?>"></td>
</tr>
<tr>
<td>NAMA BARANG :</td>
<td><input type="text" name="nama_barang" value="<?php echo $data['nama_barang']; ?>"></td>
</tr>
<tr>
<tr>
<td>JENIS :</td>
<td><select name="id_jenis">
<?php
while ($data_jenis = mysql_fetch_array($result_jenis)) {
if ($data_jenis['id_jenis'] == $data['id_jenis']) {
?>
<option value="<?php echo $data_jenis['id_jenis']; ?>" selected>
<?php echo $data_jenis['jenis_barang']; ?></option>
<?php
} else {
?>
<option value="<?php echo $data_jenis['id_jenis']; ?>" >
<?php echo $data_jenis['jenis_barang']; ?></option>
<?php
}
}
?></select></td>
</tr>
<tr>
<td>SATUAN :</td>
<td><input type="text" name="satuan" value="<?php echo $data['satuan']; ?>"></td>
</tr>
<tr>
<td>HARGA BELI :</td>
<td><input type="text" name="harga_beli" value="<?php echo $data['harga_beli']; ?>"></td>
</tr>
<tr>
<td>HARGA JUAL :</td>
<td><input type="text" name="harga_jual" value="<?php echo $data['harga_jual']; ?>"></td>
</tr>
<tr>
<td>JUMLAH STOK :</td>
<td><input type="text" name="jumlah_stok" value="<?php echo $data['jumlah_stok']; ?>"></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="submit" value="SIMPAN">
<input type="button" value="BATAL" onclick="self.history.back();"></td>
</tr>
</table>
</form>
</body>
</html>
Ubah script file barang_ubah_query.php
<?php
include("koneksi.php");
$query = "update
barang set
nama_barang = '$_POST[nama_barang]',
id_jenis = '$_POST[id_jenis]',
satuan = '$_POST[satuan]',
harga_beli = '$_POST[harga_beli]',
harga_jual = '$_POST[harga_jual]',
jumlah_stok = '$_POST[jumlah_stok]',
user_name =
'ADMIN',
tgl_proses =
now()
where id_barang =
'$_POST[id_barang]' ";
$result = mysql_query($query);
if ($result) {
header("location:barang_tampil.php");
}
else {
echo "proses simpan gagal !.";
}
?>
Sekarang coba jalankan browser dan ketik di url http://localhost/crud/barang_tampil.php dan lakukan proses tambah dan ubah dengan tambahan field id_jenis untuk jenis barang. Saatnya untuk bilan Lookup Field itu MUDAAAH.
Semoga Bermanfaat
Salam Edukasi dan TI
No comments:
Post a Comment