Tuesday, 20 October 2015

Membuat Relasi Tabel dengan PHP

October 20, 2015    

Assalamualaikoooom...
hehehehe apa kabar semuanya ..?
kali ini saya akan berbagi tutorial untuk membuat relasi antar tabel menggunakan PHP
agak canggung sedikit sih, setelah
udah sekian lama ga  bagi2 tutorial hehehehe..
langsung ae weees ..

pertama kita buat database dan tabelnya dulu .

langsung aja aku kasi sintaq "sql"nya

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

 CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `test` ;

 -- -----------------------------------------------------
-- Table `test`.`product`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test`.`product` (
  `id_produk` INT NOT NULL AUTO_INCREMENT ,
  `nama` VARCHAR(45) NULL ,
  `harga` DOUBLE NULL ,
  `jenis` VARCHAR(20) NULL ,
  PRIMARY KEY (`id_produk`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `test`.`buku`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test`.`buku` (
  `id_produk` INT NOT NULL ,
  `penulis` VARCHAR(45) NULL ,
  `penerbit` VARCHAR(45) NULL ,
  `isbn` VARCHAR(45) NULL ,
  `tgl_terbit` VARCHAR(45) NULL ,
  PRIMARY KEY (`id_produk`) ,
  INDEX `fk_buku_1` (`id_produk` ASC) ,
  CONSTRAINT `fk_buku_1`
    FOREIGN KEY (`id_produk` )
    REFERENCES `test`.`product` (`id_produk` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `test`.`album`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test`.`album` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `judul` VARCHAR(100) NULL ,
  `artis` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `test`.`lagu`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test`.`lagu` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `id_album` INT NULL ,
  `no_track` INT NULL ,
  `judul` VARCHAR(100) NULL ,
  `durasi` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_lagu_1` (`id_album` ASC) ,
  CONSTRAINT `fk_lagu_1`
    FOREIGN KEY (`id_album` )
    REFERENCES `test`.`album` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `test`.`mahasiswa`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test`.`mahasiswa` (
  `nim` VARCHAR(40) NOT NULL ,
  `nama` VARCHAR(45) NULL ,
  `jurusan` VARCHAR(45) NULL ,
  PRIMARY KEY (`nim`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `test`.`mata_kuliah`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test`.`mata_kuliah` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `kode` VARCHAR(45) NULL ,
  `nama` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `test`.`mahasiswa_mk`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test`.`mahasiswa_mk` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `nim` VARCHAR(40) NULL ,
  `id_mk` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `fk_mahasiswa_mk_1` (`nim` ASC) ,
  INDEX `fk_mahasiswa_mk_2` (`id_mk` ASC) ,
  CONSTRAINT `fk_mahasiswa_mk_1`
    FOREIGN KEY (`nim` )
    REFERENCES `test`.`mahasiswa` (`nim` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_mahasiswa_mk_2`
    FOREIGN KEY (`id_mk` )
    REFERENCES `test`.`mata_kuliah` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;


dari sintak sql di atas akan membuat struktur database seperti berikut
*Lihat pada database "test"


berikut adalah tabel yang akan kita relasi

Tabel "buku"

tabel"product"


1. Coding index.php

<html>
<head>
<title>Satu ke Satu</title>
</head>
<body>
<a href="tampil.php">Daftar Buku</a>
<br/>
<a href="form.php">Tambah Buku</a>
</body>
</html>


 2. Coding tampil.php

 <?php
$conn = mysql_connect("localhost","root","");
mysql_select_db("test",$conn);
$sql = "select * from product a inner join buku b on a.id_produk=b.id_produk";
$result = mysql_query($sql);
?>
<html>
<head>
<title>Aplikasi CRUD PHP</title>
    <link href="style.css" type="text/css" rel="stylesheet" />
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"><style type="text/css">
<!--
body {
background-image: url(../32351-samurai-silhouette-2560x1440-minimalistic-wallpaper.jpg);
}
-->
</style></head>
<body>
 <strong> <td align="center"><div align="center"><font size="6" color="BLACK" face="Wallpoet" style="text-shadow: 5px 0px 30px red;" > [#] APLIKASI PENDATAAN BUKU PERPUSTAKAAN [#]<br> 
   </font>
       <font face="Courier New" size="3" font style="text-shadow: 0px 0px 20px red;" color="BLACK">[+] Rizky Fajar Maulidan [+] <br>
       </font>
     </div>
   <p></p>
<table border="1" align="center" cellpadding="5" cellspacing="0">
<tr bgcolor="#00FFFF">
<th>Nama</th>
<th>Harga</th>
<th>Penulis</th>
<th>Penerbit</th>
<th>Category</th>
<th>Tanggal Terbit</th>
<th>Aksi</th>
  </tr>
<?php while($buku = mysql_fetch_array($result)){?>
<tr>
<td><?php echo $buku['nama'];?></td>
<td><?php echo $buku['harga'];?></td>
<td><?php echo $buku['penulis'];?></td>
<td><?php echo $buku['penerbit'];?></td>
<td><?php echo $buku['jenis'];?></td>
<td><?php echo $buku['tgl_terbit'];?></td>
<!-- tambah link edit & delete -->
<td>
<a href="edit.php?id=<?php echo $buku['id_produk'];?>">Edit</a> | 
<a href="delete.php?id=<?php echo $buku['id_produk'];?>">Delete</a>
</td>
</tr>
<?php }?>
</table>
</body>

</html>
3. Coding edit.php

 <?php
$conn = mysql_connect("localhost","root","");
mysql_select_db("test",$conn);
if($_POST){// jika tombol update ditekan
//update data buku
$sql = "update product set nama='{$_POST['nama']}',harga='{$_POST['harga']}' 
where id_produk='{$_POST['id_produk']}'";
mysql_query($sql);
//update data buku
$sql = "update buku set penulis='{$_POST['penulis']}',penerbit='{$_POST['penerbit']}',
isbn='{$_POST['isbn']}',tgl_terbit='{$_POST['tgl_terbit']}' where id_produk='{$_POST['id_produk']}'";
mysql_query($sql);
echo "Data telah di edit";
}
$idProduk = (int) $_GET['id'];
$sql = "select * from product p inner join buku b on p.id_produk=b.id_produk where p.id_produk='$idProduk'";
$result = mysql_query($sql);
$product = mysql_fetch_array($result);
?>
<form name="form1" action="" method="post">
<dl>
<dt>Nama</dt>
<dd><input type="text" name="nama" value="<?php echo $product['nama'];?>"/></dd>
<dt>Harga</dt>
<dd><input type="text" name="harga" value="<?php echo $product['harga'];?>"/></dd>

</dd>
<dt>Penulis</dt>
<dd><input type="text" name="penulis" value="<?php echo $product['penulis'];?>"/></dd>
<dt>Penerbit</dt>
<dd><input type="text" name="penerbit" value="<?php echo $product['penerbit'];?>"/></dd>
<dt>ISBN</dt>
<dd><input type="text" name="isbn" value="<?php echo $product['isbn'];?>"/></dd>
<dt>Tanggal Terbit</dt>
<dd><input type="text" name="tgl_terbit" value="<?php echo $product['tgl_terbit'];?>"/></dd>
<dt></dt>
<dd><input type="submit" value="Update"/></dd>
</dl>
<input type="hidden" name="id_produk" value="<?php echo $product['id_produk'];?>"/>
</form>
4. Coding simpan.php

 <?php
if($_POST){
$conn = mysql_connect("localhost","root","");
mysql_select_db("test",$conn); 
//menyimpan ke table product
$sql = "insert into product (nama,harga,jenis) values ('{$_POST['nama']}','{$_POST['harga']}','{$_POST['jenis']}')";
mysql_query($sql) or die('Gagal menyimpan produk');
//mencari id produk
$sql = "select max(id_produk) as last_id from product limit 1";
$hasil = mysql_query($sql);
$row = mysql_fetch_array($hasil);
$lastId = $row['last_id'];
//menyimpan data buku ke table buku
$sql = "insert into buku (id_produk,penulis,penerbit,isbn,tgl_terbit) 
values ('$lastId','{$_POST['penulis']}','{$_POST['penerbit']}','{$_POST['isbn']}','{$_POST['tgl_terbit']}')";
mysql_query($sql) or die('Gagal menyimpan data buku');
echo 'data tersimpan';
}


 5. Coding form.php
<form name="form1" action="simpan.php" method="post">
<dl>
<dt>Nama</dt>
<dd><input type="text" name="nama"/></dd>
<dt>Harga</dt>
<dd><input type="text" name="harga"/></dd>
<dt>Jenis</dt>
<dd>
<select name="jenis">
<option value="BUKU">Sejarah</option>
<option value="NOVEL">Novel</option>
<option value="DESAIN">Desain Grafis</option>
</select>
</dd>
<dt>Penulis</dt>
<dd><input type="text" name="penulis"/></dd>
<dt>Penerbit</dt>
<dd><input type="text" name="penerbit"/></dd>
<dt>ISBN</dt>
<dd><input type="text" name="isbn"/></dd>
<dt>Tanggal Terbit</dt>
<dd><input type="text" name="tgl_terbit"/></dd>
<dt></dt>
<dd><input type="submit" value="Simpan"/></dd>
</dl>
</form>
6. Coding delete.php
<?php
$idProduk = (int) $_GET['id'];
if($idProduk){
$conn = mysql_connect("localhost","root","");
mysql_select_db("test",$conn);
//delete data buku terlebih dahulu
mysql_query("delete from buku where id_produk='{$idProduk}'");
//delte data produk
mysql_query("delete from product where id_produk='{$idProduk}'");
}
//kembali ke halaman tampil.php
header("Location: tampil.php");
exit;


 Output 


Sekian dulu ya ..
semoga bermanfaat  ^_^
selamat belajar....