CRUD PHP Data Object (PDO)

PHP Data Object atau yang sering dikenal PDO merupakan database akses layer untuk PHP versi 5.xx. PDO ini dapat digunakan untuk beberapa driver database yang disediakan pada komputer kita. misalnya MySQL, ODBC, Oracle, PostgreSQL, SQLite dan yang lainnya. kita dapat melihat driver yang tersedia pada PDO dengan cara :

print_r(PDO::getAvailableDrivers());

maka akan muncul array seperti berikut :

Array ( [0] => mysql [1] => odbc [2] => sqlite [3] => sqlite2 )

itu artinya pada komputer kita ada driver mysql, odbc, sqlite dan sqlite2, kali ini yang kita bahas adalah mengkoneksikan database mysql menggunakan PDO.

Pertama, kita buat folder pada htdocs untuk project kita, misalnya folder pdo. Kemudian buat struktur folder dan file seperti berikut :

sebelumnya kita buat database dengan nama pdo_data dan tabel dengan nama m_data

CREATE TABLE `m_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nama` text NOT NULL,
  `kelas` text NOT NULL,
  `jurusan` text NOT NULL,
  `tempat_lahir` text NOT NULL,
  `tanggal_lahir` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

kemudian kita akan mulai koding dari file ke file :D

crud.pdo.class.php

<?php

class pdo_class extends PDO{
    private $engine;
    private $host;
    private $database;
    private $user;
    private $pass;
       
    private $result;    
     
    public function __construct()
        {
        $this->engine   = 'mysql';
        $this->host     = 'localhost';
        $this->database = 'pdo_data';
        $this->user     = 'root';
        $this->pass     = '';
               
        $dns = $this->engine.':dbname='.$this->database.";host=".$this->host;
        parent::__construct( $dns, $this->user, $this->pass );
    }
       
    public function insert($table,$rows=null)
    {
            $command = 'INSERT INTO '.$table;
            $row = null; $value=null;
            foreach ($rows as $key => $nilainya)
            {
              $row  .=",".$key;
              $value        .=", :".$key;
            }
           
            $command .="(".substr($row,1).")";
            $command .="VALUES(".substr($value,1).")";
             
       
            $stmt =  parent::prepare($command);
            $stmt->execute($rows);
            $rowcount = $stmt->rowCount();
            return $rowcount;
    }
       
    public function delete($tabel,$where=null)
    {
            $command = 'DELETE FROM '.$tabel;
           
            $list = Array(); $parameter = null;
            foreach ($where as $key => $value)
            {
              $list[] = "$key = :$key";
              $parameter .= ', ":'.$key.'":"'.$value.'"';
            }
            $command .= ' WHERE '.implode(' AND ',$list);
       
            $json = "{".substr($parameter,1)."}";
            $param = json_decode($json,true);
                           
            $query = parent::prepare($command);
            $query->execute($param);
            $rowcount = $query->rowCount();
    return $rowcount;
    }
       
    public function update($tabel, $fild = null ,$where = null)
    {
        $update = 'UPDATE '.$tabel.' SET ';
        $set=null; $value=null;
        foreach($fild as $key => $values)
        {
             $set .= ', '.$key. ' = :'.$key;
             $value .= ', ":'.$key.'":"'.$values.'"';
        }
        $update .= substr(trim($set),1);
        $json = '{'.substr($value,1).'}';
        $param = json_decode($json,true);

        if($where != null)
        {
        $update .= ' WHERE '.$where;
        }

        $query = parent::prepare($update);
        $query->execute($param);
        $rowcount = $query->rowCount();
    return $rowcount;
    }
       
    public function select($table, $rows, $where = null, $order = null, $limit= null)
    {
        $command = 'SELECT '.$rows.' FROM '.$table;
        if($where != null)
            $command .= ' WHERE '.$where;
        if($order != null)
            $command .= ' ORDER BY '.$order;            
        if($limit != null)
            $command .= ' LIMIT '.$limit;
                       
            $query = parent::prepare($command);
            $query->execute();
           
            $posts = array();
            while($row = $query->fetch(PDO::FETCH_ASSOC))
            {
                     $posts[] = $row;
            }
            return $this->result = $posts;
    }

    public function getResult()
    {
        return $this->result;
    }
       
       
}
 
?>

index.php

<?php
include "__class/crud.pdo.class.php";
$db = new pdo_class();
?>
<!DOCTYPE HTML>
<html>
    <head>
        <title>PDO Project</title>
        <link href="css.css" rel="stylesheet">
    </head>
    <body>
        <div class="container-menu">
            <div class="box-menu">
                <a href="?m=data"><div class="menu">Data</div></a>
                <a href="?m=tambah"><div class="menu">Tambah</div></a>
            </div>
        </div>
        <div class="container-body">
            <div class="container">
            <?php
                if(!isset($_GET['m'])){
                    include "hal/data.php";
                }elseif($_GET['m']=="data"){
                    include "hal/data.php";
                }elseif($_GET['m']=="lihat"){
                    include "hal/lihat.php";
                }else if($_GET['m']=="tambah"){
                    include "hal/tambah.php";
                }else if($_GET['m']=="ubah"){
                    include "hal/ubah.php";
                }else{
                    ?>
                        <h1 align="center">Error 404 : Page Not Found :(</h1>
                    <?php
                }
                ?>
            </div>
        </div>
        <div class="footer">
            <div class="teks-footer">Copyrights &copy; Arief Setya</div>
        </div>
    </body>
</html>

css.css

input, textarea{
	padding:3px;
	width:100%;
}
select{
	width:103%;
	padding:3px;
}
button{
	padding:7px;
	color: white;
	background-color: #0099ee;
	border:1px solid #0077ee;
	font-size: 13pt;
	margin-right: 5px;
}
a{
	text-decoration: none;
}
body{
	margin: 0;
	clear: both;
}
*{
	outline: none;
	font-family: 'Candara';
}
table{
	width:100%;
}
.container-menu{
	min-width: 1000px;
	height:60px;
	background-color:#0099ee;
	overflow: hidden;
}
.box-menu{
	width: 1000px;
	height: 60px;
	margin: 0 auto;
}
.menu{
	border-top:7px solid #0099ee;
	width: auto;
	height: 60px;
	float: left;
	text-align: center;
	cursor: pointer;
	padding-top:10px; 
	color:white;
	font-size: 15pt;
	padding-left: 10px;
	padding-right: 10px;
	transition:background-color .2s, border-top .2s, color .2s, transform .2s;
}
.menu:hover{
	border-top:7px solid #66FF66;
	transition:background-color .2s, border-top .2s, color .2s, transform .2s;
}
.menu:active{
	border-top:7px solid #0099ee;
	background-color: #66FF66;
	color:black;
	transition:background-color .2s, border-top .2s, color .2s, transform .2s;
}
.container-body{

	width: 100%;
	background-color: #fbfbfb;
}
.container{
	width: 1000px;
	margin: 10px auto;
}
.footer{
	height: 50px;
	background-color: #0099ee;
}
.teks-footer{
	padding-top: 15px;
	text-align: center;
	color: white;
}

data.php

<h2>Semua Data</h2>
<hr>
<table>
	<thead>
		<th>No.</th>
		<th>Nama</th>
		<th>Kelas</th>
		<th>Jurusan</th>
		<th>Tempat Lahir</th>
		<th>Tanggal Lahir</th>
		<th colspan="3">Tindakan</th>
	</thead>
	<?php
		$db->select("m_data","*");
		$data = $db->getResult();
		for($i=0;$i<sizeof($data);$i++){
			?>
			<tbody>
				<tr align="center">
					<td><?php echo $i+1;?></td>
					<td><?php echo $data[$i]['nama'];?></td>
					<td><?php echo $data[$i]['kelas'];?></td>
					<td><?php echo $data[$i]['jurusan'];?></td>
					<td><?php echo $data[$i]['tempat_lahir'];?></td>
					<td><?php echo $data[$i]['tanggal_lahir'];?></td>
					<td><a href="?m=lihat&id=<?php echo $data[$i]['id'];?>">Lihat</a></td>
					<td><a href="?m=ubah&id=<?php echo $data[$i]['id'];?>">Ubah</a></td>
					<td><a onclick="return confirm('Apakah Anda yakin ingin menghapus data <?php echo $data[$i]['nama'];?>?')" href="proses/hapus.php?id=<?php echo $data[$i]['id'];?>">Hapus</a></td>
				</tr>
			</tbody>
			<?php
		}
		if(sizeof($data)=="0"){
			?>
			<tbody>
				<tr>
					<td colspan="9" align="center"><span style="font-weight:bold">Data not found</span></td>
				</tr>
			</tbody>
			<?php
		}


	?>
</table>

lihat.php

<?php
$db->select("m_data","*","id=".$_GET['id']);
$data = $db->getResult();
$row = $data[0];
echo "Detail Data<hr>";
echo "Nama : ".$row['nama']."<br>";
echo "Kelas : ".$row['kelas']."<br>";
echo "Jurusan : ".$row['jurusan']."<br>";
echo "Tempat Lahir : ".$row['tempat_lahir']."<br>";
echo "Tanggal Lahir : ".$row['tanggal_lahir']."<br>";	
?>
<hr>
Tindakan : <a href="?m=ubah&id=<?php echo $_GET['id'];?>">Ubah</a> atau <a onclick="return confirm('Apakah Anda yakin ingin menghapus data <?php echo $row['nama'];?>?')" href="proses/hapus.php?id=<?php echo $_GET['id'];?>">Hapus</a>

tambah.php

<h2>Tambah Data</h2>
<hr>
<form method="POST" action="proses/save.php">
	<table style="width:500px;">
		<tr>
			<td>Nama</td>
			<td><input required type="text" name="nama"></td>
		</tr>
		<tr>
			<td>Kelas</td>
			<td><select name="kelas">
					<option value="X">X ( Sepuluh )</option>
					<option value="XI">XI ( Sebelas )</option>
					<option value="XII">XII ( Dua Belas )</option>
				</select></td>
		</tr>
		<tr>
			<td>Jurusan</td>
			<td><select name="jurusan">
					<option value="RPL">Rekayasa Perangkat Lunak</option>
					<option value="PM">Pemasaran</option>
					<option value="AP">Administrasi Perkantoran</option>
					<option value="AK">Akuntansi</option>
				</select></td>
		</tr>
		<tr>
			<td>Tempat Lahir</td>
			<td><input required type="text" name="tempat_lahir"></td>
		</tr>
		<tr>
			<td>Tanggal Lahir</td>
			<td><input required type="date" name="tanggal_lahir"></td>
		</tr>
		<tr>
			<td></td>
			<td><button type="submit">Simpan</button><button type="reset">Kosongkan</button></td>
		</tr>
	</table>
</form>

ubah.php

<?php
$db->select("m_data","*","id=".$_GET['id']);
$data = $db->getResult();
$row = $data[0];
?>
<h2>Ubah Data</h2>
<hr>
	<form method="POST" action="proses/update.php">
		<table style="width:500px;">
			<tr>
				<td>Nama</td>
				<td><input type="hidden" name="id" value="<?php echo $row['id'];?>"><input value="<?php echo $row['nama'];?>" required type="text" name="nama"></td>
			</tr>
			<tr>
				<td>Kelas</td>
				<td><select name="kelas">
						<option <?php if($row['kelas']=="X"){echo"selected";} ?> value="X">X ( Sepuluh )</option>
						<option <?php if($row['kelas']=="XI"){echo"selected";} ?> value="XI">XI ( Sebelas )</option>
						<option <?php if($row['kelas']=="XII"){echo"selected";} ?> value="XII">XII ( Dua Belas )</option>
					</select></td>
			</tr>
			<tr>
				<td>Jurusan</td>
				<td><select name="jurusan">
						<option <?php if($row['jurusan']=="RPL"){echo"selected";} ?> value="RPL">Rekayasa Perangkat Lunak</option>
						<option <?php if($row['jurusan']=="PM"){echo"selected";} ?> value="PM">Pemasaran</option>
						<option <?php if($row['jurusan']=="AP"){echo"selected";} ?> value="AP">Administrasi Perkantoran</option>
						<option <?php if($row['jurusan']=="AK"){echo"selected";} ?> value="AK">Akuntansi</option>
					</select></td>
			</tr>
			<tr>
				<td>Tempat Lahir</td>
				<td><input value="<?php echo $row['tempat_lahir'];?>" required type="text" name="tempat_lahir"></td>
			</tr>
			<tr>
				<td>Tanggal Lahir</td>
				<td><input value="<?php echo $row['tanggal_lahir'];?>" required type="date" name="tanggal_lahir"></td>
			</tr>
			<tr>
				<td></td>
				<td><button type="submit">Ubah</button><button type="reset">Kosongkan</button></td>
			</tr>
		</table>
	</form>

hapus.php

<?php
include "../__class/crud.pdo.class.php";
$db = new pdo_class();

if(isset($_GET['id'])){
	$data['id'] = $_GET['id'];
	$db->delete('m_data',$data);
	header("location:../index.php");
}
?>

save.php

<?php
include "../__class/crud.pdo.class.php";
$db = new pdo_class();

if(isset($_POST['nama'])){
	$data['nama'] = $_POST['nama'];
	$data['kelas'] = $_POST['kelas'];
	$data['jurusan'] = $_POST['jurusan'];
	$data['tempat_lahir'] = $_POST['tempat_lahir'];
	$data['tanggal_lahir'] = $_POST['tanggal_lahir'];
	$db->insert('m_data',$data);
	header("location:../index.php");
}
?>

update.php

<?php
include "../__class/crud.pdo.class.php";
$db = new pdo_class();

if(isset($_POST['nama'])){
	$data['nama'] = $_POST['nama'];
	$data['kelas'] = $_POST['kelas'];
	$data['jurusan'] = $_POST['jurusan'];
	$data['tempat_lahir'] = $_POST['tempat_lahir'];
	$data['tanggal_lahir'] = $_POST['tanggal_lahir'];
	$db->update('m_data',$data,"id='".$_POST['id']."'");
	header("location:../index.php");
}
?>

dan tampilan hasil dari kodingan diatas adalah seperti berikut ini




Thursday, 17 Sep 2015 12:02 AM Arief Setya
Sign In
Sign In to Your Account :)
Sign In or Sign Up
 
Follow Us on Twitter
Find Us on Facebook