Operasi CRUD PHP menggunakan Extensi PDO

Selamat siang sobat blogger kali ini kami akan share bagaimana membuat operasi crud PHP emnggunakna extensi PDO. Sebelum itu teman-teman harus mengetahui terlebih dahulu, apa itu PDO,


"PDO (PHP Data Objects) adalah interface universal yang disediakan PHP untuk “berkomunikasi” dengan database server. Maksud istilah “interface universal” disini adalah bahwa PDO tidak terikat dengan aplikasi database tertentu. Apabila saat ini kita menggunakan database MySQL dan dikemudian hari ingin bermigrasi menggunakan PostgreSQL, kita hanya tinggal mengganti cara pemanggilan awal PDO dan seluruh kode program yang ada bisa langsung digunakan untuk database baru."

Step 1 - kita buat adalah membuat databasenya, Berikut databasenya dengan nama 'user'
 -- phpMyAdmin SQL Dump
-- version 4.7.7
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Jul 24, 2018 at 11:37 AM
-- Server version: 10.0.35-MariaDB
-- PHP Version: 5.6.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `armg3295_user`
--

-- --------------------------------------------------------

--
-- Table structure for table `tblusers`
--

CREATE TABLE `tblusers` (
  `id` int(11) NOT NULL,
  `FirstName` varchar(150) NOT NULL,
  `LastName` varchar(150) NOT NULL,
  `EmailId` varchar(120) NOT NULL,
  `ContactNumber` char(11) NOT NULL,
  `Address` varchar(255) NOT NULL,
  `PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tblusers`
--

INSERT INTO `tblusers` (`id`, `FirstName`, `LastName`, `EmailId`, `ContactNumber`, `Address`, `PostingDate`) VALUES
(1, 'Abi', 'iwa', 'arman@student.ta.ac.id', '123', 'Malino92', '2018-07-24 04:35:04');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblusers`
--
ALTER TABLE `tblusers`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblusers`
--
ALTER TABLE `tblusers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 

Step 2 - kalian harus membuat koneksi kedatabase, buat file dengan nama dbconfig.php

 <?php
// DB credentials.
define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PASS','');
define('DB_NAME','user');
// Establish database connection.
try
{
$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,DB_USER, DB_PASS);
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}
?> 

Step 3 - Buat file dengan nama insert.php berfungsi sebagai form untuk menginput data kedalam database

 <?php
// Memanggil file koneksi kedabase
require_once'dbconfig.php';
if(isset($_POST['insert']))
{

// Value posting 
  $fname=$_POST['firstname'];
  $lname=$_POST['lastname'];
  $emailid=$_POST['emailid'];
  $contactno=$_POST['contactno'];
  $address=$_POST['address'];

// Quenry untuk insert
  $sql="INSERT INTO tblusers(FirstName,LastName,EmailId,ContactNumber,Address) VALUES(:fn,:ln,:eml,:cno,:adrss)";

//Permintaan untuk eksekusi
  $query = $dbh->prepare($sql);

// Ikat parameternya
  $query->bindParam(':fn',$fname,PDO::PARAM_STR);
  $query->bindParam(':ln',$lname,PDO::PARAM_STR);
  $query->bindParam(':eml',$emailid,PDO::PARAM_STR);
  $query->bindParam(':cno',$contactno,PDO::PARAM_STR);
  $query->bindParam(':adrss',$address,PDO::PARAM_STR);
// Query Eksekusi
  $query->execute();

// Periksa apakah pemasangan benar-benar berfungsi. Jika ID yang dimasukkan terakhir lebih besar dari nol, penyisipannya berfungsi.
  $lastInsertId = $dbh->lastInsertId();
  if($lastInsertId)
  {
// Pesan jika inser data sukses
    echo "<script>alert('Record inserted successfully');</script>";
    echo "<script>window.location.href='index.php'</script>"; 
  }
  else 
  {
// Pesan jika insert data tidak berhasil
    echo "<script>alert('Something went wrong. Please try again');</script>";
    echo "<script>window.location.href='index.php'</script>"; 
  }
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Chart</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  
</head>
<body>
  <br/>
  <div class="container">
    <nav class="navbar navbar-inverse">
      <div class="container-fluid">
       <div class="navbar-header">
        <a class="navbar-brand" href="#">Operasi CRUD PHP menggunakan Extensi PDO</a>
      </div>
    </div>
  </nav>

  <form name="insertrecord" method="post">
    <div class="row">
      <div class="col-md-4"><b>First Name</b>
        <input type="text" name="firstname" class="form-control" required>
      </div>
      <div class="col-md-4"><b>Last Name</b>
        <input type="text" name="lastname" class="form-control" required>
      </div>
    </div>

    <div class="row">
      <div class="col-md-4"><b>Email id</b>
        <input type="email" name="emailid" class="form-control" required>
      </div>
      <div class="col-md-4"><b>Contactno</b>
        <input type="text" name="contactno" class="form-control" maxlength="10" required>
      </div>
    </div>  

    <div class="row">
      <div class="col-md-8"><b>Address</b>
        <textarea class="form-control" name="address" required></textarea>
      </div>
    </div>  

    <div class="row" style="margin-top:1%">
      <div class="col-md-8">
        <input type="submit" name="insert" value="Submit">
      </div>
    </div>         
  </form>

</div>
</div>
</body>
</html> 

Step 4- Buat file dengan nama index.php yang berfungsi sebagai form untuk memanggil/menempatkan form yang ada di database.

 <?php
// Memanggil file koneksi kedabase
require_once'dbconfig.php';

// Code untuk record Deleted
if(isset($_REQUEST['del']))
{
//Memanggil id baris
  $uid=intval($_GET['del']);

//Query untuk menghapus
  $sql = "delete from tblusers WHERE  id=:id";

// Persiapan query untuk eksekusi
  $query = $dbh->prepare($sql);

// Ikat Parameter
  $query-> bindParam(':id',$uid, PDO::PARAM_STR);

// Query eksekusi
  $query -> execute();

// Pesan sesudah updae
  echo "<script>alert('Record Updated successfully');</script>";

// Kode untuk redirect
  echo "<script>window.location.href='index.php'</script>"; 
}


?>
<!DOCTYPE html>
<html lang="en">
<head>
  <title>Chart</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  
</head>
<body>
  <br/>
  <div class="container">
    <nav class="navbar navbar-inverse">
      <div class="container-fluid">
       <div class="navbar-header">
        <a class="navbar-brand" href="#">Operasi CRUD PHP menggunakan Extensi PDO</a>
      </div>
    </div>
  </nav>
  <a href="insert.php"><button class="btn btn-primary"> Insert Record</button></a>
  <div class="table-responsive">                
    <table id="mytable" class="table table-bordred table-striped">                 
      <thead>
        <th>No</th>
        <th>First Name</th>
        <th>Last Name</th>
        <th>Email</th>
        <th>Contact</th>
        <th>Address</th>
        <th>Posting Date</th>
        <th>Edit</th>
        <th>Delete</th>
      </thead>
      <tbody>
        
        <?php 
        $sql = "SELECT FirstName,LastName,EmailId,ContactNumber,Address,PostingDate,id from tblusers";
//Persiapan untuk Query
        $query = $dbh->prepare($sql);
// Jalankan Query
        $query->execute();
// Tetapkan data yang Anda tarik dari database (pada langkah sebelumnya) ke variabel.
        $results=$query->fetchAll(PDO::FETCH_OBJ);
// Untuk serial number initalization
        $cnt=1;
        if($query->rowCount() > 0)
        {
//Jika kueri mengembalikan paling tidak satu rekaman, ini dapat mengulangi rekaman dalam loop foreach:
          foreach($results as $result)
          {               
            ?>  
            <tr>
              <td><?php echo htmlentities($cnt);?></td>
              <td><?php echo htmlentities($result->FirstName);?></td>
              <td><?php echo htmlentities($result->LastName);?></td>
              <td><?php echo htmlentities($result->EmailId);?></td>
              <td><?php echo htmlentities($result->ContactNumber);?></td>
              <td><?php echo htmlentities($result->Address);?></td>
              <td><?php echo htmlentities($result->PostingDate);?></td>

              <td><a href="update.php?id=<?php echo htmlentities($result->id);?>"><button class="btn btn-primary btn-xs"><span class="glyphicon glyphicon-pencil"></span></button></a></td>

              <td><a href="index.php?del=<?php echo htmlentities($result->id);?>"><button class="btn btn-danger btn-xs" onClick="return confirm('Do you really want to delete');"><span class="glyphicon glyphicon-trash"></span></button></a></td>
            </tr>
            

            <?php 
// Untuk nomor auto increment
            $cnt++;
          }} ?>
        </tbody>      
      </table>
    </div>
  </div>
</body>
</html> 

Step 5- Buat file dengan nama update.php berfungsi sebagai form untuk update data yang di tampilkan dari database.

 <?php
require_once'dbconfig.php';
if(isset($_POST['update']))
{

  $userid=intval($_GET['id']); 
  $fname=$_POST['firstname'];
  $lname=$_POST['lastname'];
  $emailid=$_POST['emailid'];
  $contactno=$_POST['contactno'];
  $address=$_POST['address'];

  $sql="update tblusers set FirstName=:fn,LastName=:ln,EmailId=:eml,ContactNumber=:cno,Address=:adrss where id=:uid";
  $query = $dbh->prepare($sql);
  $query->bindParam(':fn',$fname,PDO::PARAM_STR);
  $query->bindParam(':ln',$lname,PDO::PARAM_STR);
  $query->bindParam(':eml',$emailid,PDO::PARAM_STR);
  $query->bindParam(':cno',$contactno,PDO::PARAM_STR);
  $query->bindParam(':adrss',$address,PDO::PARAM_STR);
  $query->bindParam(':uid',$userid,PDO::PARAM_STR);

  $query->execute();
  echo "<script>alert('Record Updated successfully');</script>";
  echo "<script>window.location.href='index.php'</script>"; 
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Chart</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  
</head>
<body>
  <br/>
  <div class="container">
    <nav class="navbar navbar-inverse">
      <div class="container-fluid">
       <div class="navbar-header">
        <a class="navbar-brand" href="#">Operasi CRUD PHP menggunakan Extensi PDO</a>
      </div>
    </div>
  </nav>

  <?php 
  $userid=intval($_GET['id']);
  $sql = "SELECT FirstName,LastName,EmailId,ContactNumber,Address,PostingDate,id from tblusers where id=:uid";
  $query = $dbh->prepare($sql);
  $query->bindParam(':uid',$userid,PDO::PARAM_STR);
  $query->execute();
  $results=$query->fetchAll(PDO::FETCH_OBJ);
  $cnt=1;
  if($query->rowCount() > 0)
  {
    foreach($results as $result)
    {               
      ?>
      <form name="insertrecord" method="post">
        <div class="row">
          <div class="col-md-4"><b>First Name</b>
            <input type="text" name="firstname" value="<?php echo htmlentities($result->FirstName);?>" class="form-control" required>
          </div>
          <div class="col-md-4"><b>Last Name</b>
            <input type="text" name="lastname" value="<?php echo htmlentities($result->LastName);?>" class="form-control" required>
          </div>
        </div>

        <div class="row">
          <div class="col-md-4"><b>Email id</b>
            <input type="email" name="emailid" value="<?php echo htmlentities($result->EmailId);?>" class="form-control" required>
          </div>
          <div class="col-md-4"><b>Contactno</b>
            <input type="text" name="contactno" value="<?php echo htmlentities($result->ContactNumber);?>" class="form-control" maxlength="10" required>
          </div>
        </div>  

        <div class="row">
          <div class="col-md-8"><b>Address</b>
            <textarea class="form-control" name="address" required><?php echo htmlentities($result->Address);?></textarea>
          </div>
        </div>  
      <?php }} ?>

      <div class="row" style="margin-top:1%">
        <div class="col-md-8">
          <input type="submit" name="update" value="Update">
        </div>
      </div> 
    </form>
  </div>
</div>

</body>
</htm 

Jika teman-teman ingin melihat demony alangsung silkan klik button demo dibawah ini.


Sekian share kali ini semoga apa yang kami shar bermanfaat bagi anda semua, jangan lupa share dan tinggalkan komentar jika temna-teman belum paham, dan kami menginginkan anda meninggalkan komentar apa saja yang mestikami posting di lain waktu.



Belum ada tanggapan untuk "Operasi CRUD PHP menggunakan Extensi PDO"

Post a Comment