CRUD (Create, Read, Update, Delete, Pagination) Using PHP PDO and Bootstrap

Php pdo 00:34:00

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.

To make sure the webserver application in your computer is already live. To use better use apache2 webserver or xampp. If you do not like the xampp you can use other web server such as IIS and others. Create a folder inside the webroot and with the name "crudpdo" or up to you. Then create a new database and a new table or as syntax sql below.

--
-- 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>

    
    <link href="css/bootstrap.min.css" rel="stylesheet">

    
    
    
</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>

    
    <script src="js/jquery.min.js"></script>
    
    <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>

    
    <link href="css/bootstrap.min.css" rel="stylesheet">

    
    
    
</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>

    
    <script src="js/jquery.min.js"></script>
    
    <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>

    
    <link href="css/bootstrap.min.css" rel="stylesheet">

    
    
    
</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>

    
    <script src="js/jquery.min.js"></script>
    
    <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.

CRUD (Create, Read, Update, Delete, Pagination) Using PHP PDO and Bootstrap

Please Subscribe

Do You Want Translate

Search here

Popular Posts

Category