CRUD (Create, Read, Update, Delete, Pagination) Using PHP PDO and Bootstrap
In this article I will discuss how to create an application CRUD (Create, Read, Update, Delete and Pagination ) using pdo php and mysql as the database server. To make the application php pdo we should not have to use mysql but we can use other dbms like postgresql or sqlite. In this tutorial I use the database from mysql because I 'm used to using mysql. If you are not using mysql then you just compose the connection just become your favorite dbms.
--
-- Database: `biodata`
--
CREATE TABLE IF NOT EXISTS `crudpdo` (
`id_pdo` int(11) NOT NULL COMMENT 'Identitas',
`nm_pdo` varchar(45) NOT NULL COMMENT 'Nama',
`gd_pdo` varchar(20) NOT NULL COMMENT 'Jenis Kelamin',
`tl_pdo` varchar(25) NOT NULL COMMENT 'Phone',
`ar_pdo` text NOT NULL COMMENT 'Alamat'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `crudpdo`
ADD PRIMARY KEY (`id_pdo`);
ALTER TABLE `crudpdo`
MODIFY `id_pdo` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identitas';
Download and extract the plugin bootstrap into the folder "crudpdo". So there will be a folder css, js, and fonts. Then create a new folder named "includes" which contains the config.php file, data.inc.php and pagination.inc.php or like syntax below.
config.php
<?php
class Config{
// specify your own database credentials
private $host = "localhost";
private $db_name = "biodata";
private $username = "root";
private $password = "pidie";
public $conn;
// get the database connection
public function getConnection(){
$this->conn = null;
try{
$this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name, $this->username, $this->password);
}catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}
return $this->conn;
}
}
?>
data.inc.php
<?php
class Data{
// database connection and table name
private $conn;
private $table_name = "crudpdo";
// object properties
public $id;
public $nm;
public $gd;
public $tl;
public $ar;
public function __construct($db){
$this->conn = $db;
}
// create product
function create(){
//write query
$query = "INSERT INTO " . $this->table_name . " values('',?,?,?,?)";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $this->nm);
$stmt->bindParam(2, $this->gd);
$stmt->bindParam(3, $this->tl);
$stmt->bindParam(4, $this->ar);
if($stmt->execute()){
return true;
}else{
return false;
}
}
// read products
function readAll($page, $from_record_num, $records_per_page){
$query = "SELECT
*
FROM
" . $this->table_name . "
ORDER BY
nm_pdo ASC
LIMIT
{$from_record_num}, {$records_per_page}";
$stmt = $this->conn->prepare( $query );
$stmt->execute();
return $stmt;
}
// used for paging products
public function countAll(){
$query = "SELECT id_pdo FROM " . $this->table_name . "";
$stmt = $this->conn->prepare( $query );
$stmt->execute();
$num = $stmt->rowCount();
return $num;
}
// used when filling up the update product form
function readOne(){
$query = "SELECT
*
FROM
" . $this->table_name . "
WHERE
id_pdo = ?
LIMIT
0,1";
$stmt = $this->conn->prepare( $query );
$stmt->bindParam(1, $this->id);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$this->nm = $row['nm_pdo'];
$this->gd = $row['gd_pdo'];
$this->tl = $row['tl_pdo'];
$this->ar = $row['ar_pdo'];
}
// update the product
function update(){
$query = "UPDATE
" . $this->table_name . "
SET
nm_pdo = :nm,
gd_pdo = :gd,
tl_pdo = :tl,
ar_pdo = :ar
WHERE
id_pdo = :id";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(':nm', $this->nm);
$stmt->bindParam(':gd', $this->gd);
$stmt->bindParam(':tl', $this->tl);
$stmt->bindParam(':ar', $this->ar);
$stmt->bindParam(':id', $this->id);
// execute the query
if($stmt->execute()){
return true;
}else{
return false;
}
}
// delete the product
function delete(){
$query = "DELETE FROM " . $this->table_name . " WHERE id_pdo = ?";
$stmt = $this->conn->prepare($query);
$stmt->bindParam(1, $this->id);
if($result = $stmt->execute()){
return true;
}else{
return false;
}
}
}
?>
pagination.inc.php
<?php
// the page where this paging is used
echo "<nav><ul class=\"pagination\">";
// button for first page
if($page>1){
echo "<li><a href='{$page_dom}' title='Go to the first page.'>";
echo "«";
echo "</a></li>";
}
// count all products in the database to calculate total pages
$total_rows = $product->countAll();
$total_pages = ceil($total_rows / $records_per_page);
// range of links to show
$range = 2;
// display links to 'range of pages' around 'current page'
$initial_num = $page - $range;
$condition_limit_num = ($page + $range) + 1;
for ($x=$initial_num; $x<$condition_limit_num; $x++) {
// be sure '$x is greater than 0' AND 'less than or equal to the $total_pages'
if (($x > 0) && ($x <= $total_pages)) {
// current page
if ($x == $page) {
echo "<li class='active'><a href=\"#\">$x</a></li>";
}
// not current page
else {
echo "<li><a href='{$page_dom}?page=$x'>$x</a></li>";
}
}
}
// button for last page
if($page<$total_pages){
echo "<li><a href='" .$page_dom . "?page={$total_pages}' title='Last page is {$total_pages}.'>";
echo "»";
echo "</a></li>";
}
echo "</ul></nav>";
?>
Then go back to the folder "crudpdo" and create files like index.php, add.php, update.php and delete.php. This file is a file that contains the template files from the bootstrap and a crud application. For syntax as below.
index.php
<?php
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$records_per_page = 5;
$from_record_num = ($records_per_page * $page) - $records_per_page;
include_once 'includes/config.php';
include_once 'includes/data.inc.php';
$database = new Config();
$db = $database->getConnection();
$product = new Data($db);
$stmt = $product->readAll($page, $from_record_num, $records_per_page);
$num = $stmt->rowCount();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Data CRUD PDO</title>
<!-- Bootstrap -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
<script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
<![endif]-->
</head>
<body>
<p>
</p>
<div class="container">
<p>
<a class="btn btn-primary" href="add.php" role="button">Add Data</a>
</p>
<?php
if($num>0){
?>
<table class="table table-bordered table-hover table-striped">
<caption>Ini adalah data biodata anda</caption>
<thead>
<tr>
<th>#</th>
<th>Name</th>
<th>Gender</th>
<th>Phone</th>
<th>Address</th>
<th>Action</th>
</tr>
</thead>
<tbody>
<?php
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
extract($row);
?>
<tr>
<?php echo "<td>{$id_pdo}</td>" ?>
<?php echo "<td>{$nm_pdo}</td>" ?>
<?php echo "<td>{$gd_pdo}</td>" ?>
<?php echo "<td>{$tl_pdo}</td>" ?>
<?php echo "<td>{$ar_pdo}</td>" ?>
<?php echo "<td width='100px'>
<a class='btn btn-warning btn-sm' href='update.php?id={$id_pdo}' role='button'><span class='glyphicon glyphicon-pencil' aria-hidden='true'></span></a>
<a class='btn btn-danger btn-sm' href='delete.php?id={$id_pdo}' role='button'><span class='glyphicon glyphicon-trash' aria-hidden='true'></span></a>
</td>" ?>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
$page_dom = "index.php";
include_once 'includes/pagination.inc.php';
}
else{
?>
<div class="alert alert-warning alert-dismissible" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>
<strong>Warning!</strong> Data Masih Kosong Tolong Diisi.
</div>
<?php
}
?>
</div>
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="js/jquery.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="js/bootstrap.min.js"></script>
</body>
</html>
add.php
<?php
include_once 'includes/config.php';
$database = new Config();
$db = $database->getConnection();
include_once 'includes/data.inc.php';
$product = new Data($db);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Data CRUD PDO</title>
<!-- Bootstrap -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
<script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
<![endif]-->
</head>
<body>
<p>
</p>
<div class="container">
<p>
<a class="btn btn-primary" href="index.php" role="button">Back View Data</a>
</p>
<?php
if($_POST){
$product->nm = $_POST['nm'];
$product->gd = $_POST['gd'];
$product->tl = $_POST['tl'];
$product->ar = $_POST['ar'];
if($product->create()){
?>
<div class="alert alert-success alert-dismissible" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>
<strong>Success!</strong> Anda Berhasil, <a href="index.php">View Data</a>.
</div>
<?php
}else{
?>
<div class="alert alert-danger alert-dismissible" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>
<strong>Fail!</strong> Anda Gagal, Coba Lagi.
</div>
<?php
}
}
?>
<form method="post">
<div class="form-group">
<label for="nm">Name</label>
<input type="text" class="form-control" id="nm" name="nm">
</div>
<div class="form-group">
<label for="gd">Gender</label>
<input type="text" class="form-control" id="gd" name="gd">
</div>
<div class="form-group">
<label for="tl">Phone</label>
<input type="text" class="form-control" id="tl" name="tl">
</div>
<div class="form-group">
<label for="ar">Alamat</label>
<textarea class="form-control" rows="3" id="ar" name="ar"></textarea>
</div>
<button type="submit" class="btn btn-success">Submit</button>
</form>
</div>
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="js/jquery.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="js/bootstrap.min.js"></script>
</body>
</html>
update.php
<?php
include_once 'includes/config.php';
$id = isset($_GET['id']) ? $_GET['id'] : die('ERROR: missing ID.');
$database = new Config();
$db = $database->getConnection();
include_once 'includes/data.inc.php';
$product = new Data($db);
$product->id = $id;
$product->readOne();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Data CRUD PDO</title>
<!-- Bootstrap -->
<link href="css/bootstrap.min.css" rel="stylesheet">
<!-- HTML5 shim and Respond.js for IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
<script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
<![endif]-->
</head>
<body>
<p>
</p>
<div class="container">
<p>
<a class="btn btn-primary" href="index.php" role="button">Back View Data</a>
</p>
<?php
if($_POST){
$product->nm = $_POST['nm'];
$product->gd = $_POST['gd'];
$product->tl = $_POST['tl'];
$product->ar = $_POST['ar'];
if($product->update()){
?>
<script>window.location.href='index.php'</script>
<?php
}else{
?>
<div class="alert alert-danger alert-dismissible" role="alert">
<button type="button" class="close" data-dismiss="alert" aria-label="Close"><span aria-hidden="true">×</span></button>
<strong>Fail!</strong> Anda Gagal, Coba Lagi.
</div>
<?php
}
}
?>
<form method="post">
<div class="form-group">
<label for="nm">Name</label>
<input type="text" class="form-control" id="nm" name="nm" value='<?php echo $product->nm; ?>'>
</div>
<div class="form-group">
<label for="gd">Gender</label>
<input type="text" class="form-control" id="gd" name="gd" value='<?php echo $product->gd; ?>'>
</div>
<div class="form-group">
<label for="tl">Phone</label>
<input type="text" class="form-control" id="tl" name="tl" value='<?php echo $product->tl; ?>'>
</div>
<div class="form-group">
<label for="ar">Alamat</label>
<textarea class="form-control" rows="3" id="ar" name="ar"><?php echo $product->ar; ?></textarea>
</div>
<button type="submit" class="btn btn-success">Submit</button>
</form>
</div>
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="js/jquery.min.js"></script>
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="js/bootstrap.min.js"></script>
</body>
</html>
delete.php
// check if value was posted
// include database and object file
include_once 'includes/config.php';
include_once 'includes/data.inc.php';
// get database connection
$database = new Config();
$db = $database->getConnection();
// prepare product object
$product = new Data($db);
// set product id to be deleted
$product->id = isset($_GET['id']) ? $_GET['id'] : die('ERROR: missing ID.');
// delete the product
if($product->delete()){
echo "<script>location.href='index.php'</script>";
}
// if unable to delete the product
else{
echo "<script>alert('Gagal menghapus data')</script>";
}
?>
Complete, run in your favorite browser such as Mozilla Firefox, Google Chrome, Opera web browser, Apple Safari and Microsoft Internet Explorer.