This tutorial tackles how to a PHP Web Application with CRUD(create, read, update, and delete) operation using the two MySQLi Extensions which are OOP and Procedural. Also included, the data table library which is an extension that uses jquery to beautify and add functionality to HTML Table. PDF Generator using the TCPDF library is also included in this tutorial.
Getting Started
First of all, this tutorial requires the following software and plugins/liraries.
Before proceeding to the coding part, make sure that you have already started the "Apache" and "MySQL" server in your XAMPP's Control Panel.
Database Creation
- Open the PHPMyAdmin in a browser . i.e. http://localhost/phpmyadmin
- Create a new database naming
mydatabase.
- Next, go to the SQL Tab of the PHPMyAdmin
- Copy the code SQL Code below and Paste it in the provided text box in the SQL Tab.
(1, 'neovic', 'devierte', 'silay city'),
(2, 'gemalyn', 'cepe', 'carmen, bohol'),
(3, 'lee', 'apilinga', 'bacolod'),
(4, 'julyn', 'divinagracia', 'eb magalona'),
(5, 'cristine', 'demapanag', 'talisay');
You can also Import the provided SQL file that is included in the source code zip file.
Database Connection
Now, we will create new file naming connection.php. Copy and Paste the code below in you created file. Make sure to modify the database credentials according to your database setup.
<?php
//for MySQLi OOP
$conn = new mysqli('localhost', 'root', '', 'mydatabase');
if($conn->connect_error){
die("Connection failed: " . $conn->connect_error); }
////////////////
//for MySQLi Procedural
// $conn = mysqli_connect('localhost', 'root', '', 'mydatabase');
// if(!$conn){
// die("Connection failed: " . mysqli_connect_error());
// }
////////////////
?>
Creating the Interface
Copy and Paste the below codes and save the files as the given file name above the scripts.
index.php
<?php
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>CRUD Operation using PHP/MySQLi with DataTable and 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'>×</button>
".$_SESSION['error']."
</div>
";
unset($_SESSION['error']); }
if(isset($_SESSION['success'])){ echo
"
<div class='alert alert-success text-center'>
<button class='close'>×</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');
}
/////////////////
//use for MySQLi Procedural
// $query = mysqli_query($conn, $sql);
// while($row = mysqli_fetch_assoc($query)){
// 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>
add_modal.php
<!-- 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">×</button> <div class="container-fluid"> <form method="POST" action="add.php"> <div class="row form-group"> <label class="control-label modal-label">Firstname:
</label> <input type="text" class="form-control" name="firstname" required> <div class="row form-group"> <label class="control-label modal-label">Lastname:
</label> <input type="text" class="form-control" name="lastname" required> <div class="row form-group"> <label class="control-label modal-label">Address:
</label> <input type="text" class="form-control" name="address" required> <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>
edit_delete_modal.php
<!-- 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">×</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">×</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>
Creating the Operations Scripts
Copy and paste the following codes and save the files as the file name above.
add.php
<?php
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';
}
///////////////
//use for MySQLi Procedural
// if(mysqli_query($conn, $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'); ?>
edit.php
<?php
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';
}
///////////////
//use for MySQLi Procedural
// if(mysqli_query($conn, $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');
?>
delete.php
<?php
include_once('connection.php');
$sql = "DELETE FROM members WHERE id = '".$_GET['id']."'";
//use for MySQLi OOP
if($conn->query($sql)){
$_SESSION['success'] = 'Member deleted successfully';
}
////////////////
//use for MySQLi Procedural
// if(mysqli_query($conn, $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'); ?>
Generating PDF using TCPDF
Copy the code below and paste it into the blank file in your text editor and save it as print_pdf.php
<?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>
";
}
////////////////
//use for MySQLi Procedural
// $query = mysqli_query($conn, $sql);
// while($row = mysqli_fetch_assoc($query)){
// $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');
?>
Note: configure the require_once function path according to your TCTPDF Library path. In my case, it is compiled in a folder naming tcpdf
Demo
That's it. You are good to go. I hope this will help you for your future PHP Projects.
P.S. If you have any comments or suggestions on how to improve this, please don't hesitate to comment below or send me a message.
Happy Coding :)Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.
Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.
FOR YOUR OWN SAFETY, PLEASE:
1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.
