Operasi CRUD menggunakan PHP / MySQLi dengan DataTable dan PDF Generator menggunakan TCPDF

//lh4.googleusercontent.com/-eAXyxy2JJKA/AAAAAAAAAAI/AAAAAAAAGS4/OHgzqHzCvtQ/s512-c/photo.jpg
September 03, 2018




Selamat sore sobat blogger kali ini kami akan share Operasi CRUD menggunakan PHP / MySQLi dengan DataTable dan PDF Generator menggunakan TCPDF. Sebenarnya penggunaan kode TCPDF dan FPDF tidak terlalu berbeda semuanya penggunaannya cukup mudah. Pada rtikel sebelumnya kami telah menjelaskan FPDF, tetapi kali ini kami akan mencoba membahas TCPDF.



tetapi sebelum itu teman-teman harus mengetahui apa itu TCPDF "TCPDF adalah salah satu dari sekian banyak aplikasi untuk membuat pdf dari html menggunakan php yang telah dikembangkan dari tahun 2000 sampai dengan sekarang.

Yang pertama yang harus dilakukan adalah membuat database terlebih dahulu, Buat database dengan nama mydatabase kemudian masukkan tabel dibawah ini.
 

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: `mydatabase`
--

CREATE TABLE `members` (
  `id` int(11) NOT NULL,
  `firstname` varchar(30) NOT NULL,
  `lastname` varchar(30) NOT NULL,
  `address` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `members`
--

INSERT INTO `members` (`id`, `firstname`, `lastname`, `address`) VALUES
(1, 'Irsyad Khalid', 'Iad', 'Mamuju'),
(2, 'Abidzar', 'abi', 'Makassar'),
(3, 'Dede', 'ahmad', 'Makassar'),
(4, 'Andi anugrh', 'andi', 'Maros'),
(5, 'Arman', 'maman', 'Mamampang'),
(38, 'Lawan', 'Lewa', 'Matanna'),
(39, 'Laili', 'zulfajriani', 'Malino');

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `members`
--
ALTER TABLE `members`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=40;
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 */;
 

Kedua adalah buat nama file dengan nama connection.php fungsi dari file ini adalah sebagai koneksi kedatabase.

 <?php
 //for MySQLi OOP
$conn = new mysqli('localhost', 'root', '', 'mydatabase');
if($conn->connect_error){
 die("Connection failed: " . $conn->connect_error);
}
?> 

Ketiga buat file dengan nama index.php, file ini berfungsi untuk menampilkan file yang ada pada database dan beberapa fungsi yang menampilkan pop up.

 <?php
session_start();
?>
<!DOCTYPE html>
<html>
<head>
 <meta charset="utf-8">
 <title>PDF Generator using TCPDF</title>
 <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
 <link rel="stylesheet" type="text/css" href="datatable/dataTable.bootstrap.min.css">
 <style>
 .height10{
  height:10px;
 }
 .mtop10{
  margin-top:10px;
 }
 .modal-label{
  position:relative;
  top:7px
 }
</style>
</head>
<body>
 <div class="container">
  <h1 class="page-header text-center">CRUD Operation with DataTable and PDF</h1>
  <div class="row">
   <div class="col-sm-8 col-sm-offset-2">
    <div class="row">
     <?php
     if(isset($_SESSION['error'])){
      echo
      "
      <div class='alert alert-danger text-center'>
      <button class='close'>&times;</button>
      ".$_SESSION['error']."
      </div>
      ";
      unset($_SESSION['error']);
     }
     if(isset($_SESSION['success'])){
      echo
      "
      <div class='alert alert-success text-center'>
      <button class='close'>&times;</button>
      ".$_SESSION['success']."
      </div>
      ";
      unset($_SESSION['success']);
     }
     ?>
    </div>
    <div class="row">
     <a href="#addnew" data-toggle="modal" class="btn btn-primary"><span class="glyphicon glyphicon-plus"></span> New</a>
     <a href="print_pdf.php" class="btn btn-success pull-right"><span class="glyphicon glyphicon-print"></span> PDF</a>
    </div>
    <div class="height10">
    </div>
    <div class="row">
     <table id="myTable" class="table table-bordered table-striped">
      <thead>
       <th>ID</th>
       <th>Firstname</th>
       <th>Lastname</th>
       <th>Address</th>
       <th>Action</th>
      </thead>
      <tbody>
       <?php
       include_once('connection.php');
       $sql = "SELECT * FROM members";

       //use for MySQLi-OOP
       $query = $conn->query($sql);
       while($row = $query->fetch_assoc()){
        echo 
        "<tr>
        <td>".$row['id']."</td>
        <td>".$row['firstname']."</td>
        <td>".$row['lastname']."</td>
        <td>".$row['address']."</td>
        <td>
        <a href='#edit_".$row['id']."' class='btn btn-success btn-sm' data-toggle='modal'><span class='glyphicon glyphicon-edit'></span> Edit</a>
        <a href='#delete_".$row['id']."' class='btn btn-danger btn-sm' data-toggle='modal'><span class='glyphicon glyphicon-trash'></span> Delete</a>
        </td>
        </tr>";
        include('edit_delete_modal.php');
       }

       ?>
      </tbody>
     </table>
    </div>
   </div>
  </div>
 </div>
 <?php include('add_modal.php') ?>

 <script src="jquery/jquery.min.js"></script>
 <script src="bootstrap/js/bootstrap.min.js"></script>
 <script src="datatable/jquery.dataTables.min.js"></script>
 <script src="datatable/dataTable.bootstrap.min.js"></script>
 <!-- generate datatable on our table -->
 <script>
  $(document).ready(function(){
 //inialize datatable
 $('#myTable').DataTable();

    //hide alert
    $(document).on('click', '.close', function(){
     $('.alert').hide();
    })
});
</script>
</body>
</html> 

Ke empat buat file dengan add.php file ini berfungsi untuk menginput data kedalam database.

 <?php
session_start();
include_once('connection.php');

if(isset($_POST['add'])){
 $firstname = $_POST['firstname'];
 $lastname = $_POST['lastname'];
 $address = $_POST['address'];
 $sql = "INSERT INTO members (firstname, lastname, address) VALUES ('$firstname', '$lastname', '$address')";

  //use for MySQLi OOP
 if($conn->query($sql)){
  $_SESSION['success'] = 'Member added successfully';
 }
 else{
  $_SESSION['error'] = 'Something went wrong while adding';
 }
}
else{
 $_SESSION['error'] = 'Fill up add form first';
}

header('location: index.php');
?> 

Kelima buat file dengan nama add_modal.php file ini berfungsi sebagai modal atau dalam bootsrap mempunyai tampilan pop up. File ini juga berfungsi sebagai form input data.

 <!-- Add New -->
<div class="modal fade" id="addnew" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
 <div class="modal-dialog">
  <div class="modal-content">
   <div class="modal-header">
    <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
    <center><h4 class="modal-title" id="myModalLabel">Add New</h4></center>
   </div>
   <div class="modal-body">
    <div class="container-fluid">
     <form method="POST" action="add.php">
      <div class="row form-group">
       <div class="col-sm-2">
        <label class="control-label modal-label">Firstname:</label>
       </div>
       <div class="col-sm-10">
        <input type="text" class="form-control" name="firstname" required>
       </div>
      </div>
      <div class="row form-group">
       <div class="col-sm-2">
        <label class="control-label modal-label">Lastname:</label>
       </div>
       <div class="col-sm-10">
        <input type="text" class="form-control" name="lastname" required>
       </div>
      </div>
      <div class="row form-group">
       <div class="col-sm-2">
        <label class="control-label modal-label">Address:</label>
       </div>
       <div class="col-sm-10">
        <input type="text" class="form-control" name="address" required>
       </div>
      </div>
     </div> 
    </div>
    <div class="modal-footer">
     <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
     <button type="submit" name="add" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save</a>
     </form>
    </div>

   </div>
  </div>
 </div> 

Ke enam buat file dengan nama edit_delete_modal.php, file ini berfung sebgaai form pop up untuk mengedit data.

 <!-- Edit -->
<div class="modal fade" id="edit_<?php echo $row['id']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
                <center><h4 class="modal-title" id="myModalLabel">Edit Member</h4></center>
            </div>
            <div class="modal-body">
   <div class="container-fluid">
   <form method="POST" action="edit.php">
    <input type="hidden" class="form-control" name="id" value="<?php echo $row['id']; ?>">
    <div class="row form-group">
     <div class="col-sm-2">
      <label class="control-label modal-label">Firstname:</label>
     </div>
     <div class="col-sm-10">
      <input type="text" class="form-control" name="firstname" value="<?php echo $row['firstname']; ?>">
     </div>
    </div>
    <div class="row form-group">
     <div class="col-sm-2">
      <label class="control-label modal-label">Lastname:</label>
     </div>
     <div class="col-sm-10">
      <input type="text" class="form-control" name="lastname" value="<?php echo $row['lastname']; ?>">
     </div>
    </div>
    <div class="row form-group">
     <div class="col-sm-2">
      <label class="control-label modal-label">Address:</label>
     </div>
     <div class="col-sm-10">
      <input type="text" class="form-control" name="address" value="<?php echo $row['address']; ?>">
     </div>
    </div>
            </div> 
   </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
                <button type="submit" name="edit" class="btn btn-success"><span class="glyphicon glyphicon-check"></span> Update</a>
   </form>
            </div>

        </div>
    </div>
</div>

<!-- Delete -->
<div class="modal fade" id="delete_<?php echo $row['id']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
                <center><h4 class="modal-title" id="myModalLabel">Delete Member</h4></center>
            </div>
            <div class="modal-body"> 
             <p class="text-center">Are you sure you want to Delete</p>
    <h2 class="text-center"><?php echo $row['firstname'].' '.$row['lastname']; ?></h2>
   </div>
            <div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
                <a href="delete.php?id=<?php echo $row['id']; ?>" class="btn btn-danger"><span class="glyphicon glyphicon-trash"></span> Yes</a>
            </div>

        </div>
    </div>
</div> 

Ke tujuh buat file dengan nama delete.php, fungsi file ini akan di ambil ketika kita meneka tombol button.

 <?php
session_start();
include_once('connection.php');

if(isset($_GET['id'])){
 $sql = "DELETE FROM members WHERE id = '".$_GET['id']."'";

  //use for MySQLi OOP
 if($conn->query($sql)){
  $_SESSION['success'] = 'Member deleted successfully';
 }

 else{
  $_SESSION['error'] = 'Something went wrong in deleting member';
 }
}
else{
 $_SESSION['error'] = 'Select member to delete first';
}

header('location: index.php');
?> 

Ke delapan buat file dengan nama edit.php fungsi file ini akan di ambil ketika kita menekan button edit.

 <?php
session_start();
include_once('connection.php');

if(isset($_POST['edit'])){
 $id = $_POST['id'];
 $firstname = $_POST['firstname'];
 $lastname = $_POST['lastname'];
 $address = $_POST['address'];
 $sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE id = '$id'";

  //use for MySQLi OOP
 if($conn->query($sql)){
  $_SESSION['success'] = 'Member updated successfully';
 }
 
 else{
  $_SESSION['error'] = 'Something went wrong in updating member';
 }
}
else{
 $_SESSION['error'] = 'Select member to edit first';
}

header('location: index.php');

?> 

Yang terakhir adalah buat file dengan nama print_pdf.php file ini berfungsi mengkonvert file yang ada di database kedalam file PDF menggunakan fungsi TCDF

 <?php
function generateRow(){
 $contents = '';
 include_once('connection.php');
 $sql = "SELECT * FROM members";

  //use for MySQLi OOP
 $query = $conn->query($sql);
 while($row = $query->fetch_assoc()){
  $contents .= "
  <tr>
  <td>".$row['id']."</td>
  <td>".$row['firstname']."</td>
  <td>".$row['lastname']."</td>
  <td>".$row['address']."</td>
  </tr>
  ";
 }
 
 return $contents;
}

require_once('tcpdf/tcpdf.php');  
$pdf = new TCPDF('P', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);  
$pdf->SetCreator(PDF_CREATOR);  
$pdf->SetTitle("Generated PDF using TCPDF");  
$pdf->SetHeaderData('', '', PDF_HEADER_TITLE, PDF_HEADER_STRING);  
$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));  
$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));  
$pdf->SetDefaultMonospacedFont('helvetica');  
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);  
$pdf->SetMargins(PDF_MARGIN_LEFT, '10', PDF_MARGIN_RIGHT);  
$pdf->setPrintHeader(false);  
$pdf->setPrintFooter(false);  
$pdf->SetAutoPageBreak(TRUE, 10);  
$pdf->SetFont('helvetica', '', 11);  
$pdf->AddPage();  
$content = '';  
$content .= '
<h2 align="center">Generated PDF using TCPDF</h2>
<h4>Members Table</h4>
<table border="1" cellspacing="0" cellpadding="3">  
<tr>  
<th width="5%">ID</th>
<th width="20%">Firstname</th>
<th width="20%">Lastname</th>
<th width="55%">Address</th> 
</tr>  
';  
$content .= generateRow();  
$content .= '</table>';  
$pdf->writeHTML($content);  
$pdf->Output('members.pdf', 'I');


?> 

Jika teman-teman ingin melihat dan mendownload file silahkan klik link dibawah ini.

DEMO                                              DOWNLOAD

Sekian share kali ini semoga apa yang kami share bermanfaat bagi anda semua, jika ada yang belum dipahami silahkan tinggalkan komentar dan mulai berdiskusi dengan kami.


Related Post

SUBSCRIBE TO OUR NEWSLETTER

closed