CRUD Operation using PHP/MySQLi with DataTable and TCPDF Tutorial

Link522 · Thursday at 12:16 AM

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. CREATE
  
  TABLE
  
  `members`
  
  (
2. `id`
  
  int
  
  (
  
  11
  
  )
  
  NOT
  
  NULL
  
  ,
3. `firstname`
  
  varchar
  
  (
  
  30
  
  )
  
  NOT
  
  NULL
  
  ,
4. `lastname`
  
  varchar
  
  (
  
  30
  
  )
  
  NOT
  
  NULL
  
  ,
5. `address`
  
  text
  
  NOT
  
  NULL
6. )
  
  ENGINE
  
  =
  
  InnoDB
  
  DEFAULT
  
  CHARSET
  
  =
  
  latin1;
8. INSERT
  
  INTO
  
  `members`
  
  (
  
  `id`
  
  ,
  
  `firstname`
  
  ,
  
  `lastname`
  
  ,
  
  `address`
  
  )
  
  VALUES
9. (
  
  1
  
  ,
  
  'neovic'
  
  ,
  
  'devierte'
  
  ,
  
  'silay city'
  
  )
  
  ,
10. (
  
  2
  
  ,
  
  'gemalyn'
  
  ,
  
  'cepe'
  
  ,
  
  'carmen, bohol'
  
  )
  
  ,
11. (
  
  3
  
  ,
  
  'lee'
  
  ,
  
  'apilinga'
  
  ,
  
  'bacolod'
  
  )
  
  ,
12. (
  
  4
  
  ,
  
  'julyn'
  
  ,
  
  'divinagracia'
  
  ,
  
  'eb magalona'
  
  )
  
  ,
13. (
  
  5
  
  ,
  
  'cristine'
  
  ,
  
  'demapanag'
  
  ,
  
  'talisay'
  
  )
  
  ;
15. ALTER
  
  TABLE
  
  `members`
16. ADD
  
  PRIMARY KEY
  
  (
  
  `id`
  
  )
  
  ;
17. ALTER
  
  TABLE
  
  `members`
18. MODIFY
  
  `id`
  
  int
  
  (
  
  11
  
  )
  
  NOT
  
  NULL
  
  AUTO_INCREMENT
  
  ,
  
  AUTO_INCREMENT
  
  =
  
  6
  
  ;
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
session_start

(

)

;
?>
<!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>
<script>
$(document).ready(function(){
//inialize datatable
$('#myTable').DataTable();
//hide alert
$(document).on('click', '.close', function(){
$('.alert').hide();
})
});
</script>
</body>
</html>

add_modal.php

<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

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

>

edit_delete_modal.php

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

;
}
///////////////
//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
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'

;
}
///////////////
//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
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'

;
}
////////////////
//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.

Download

You must upgrade your account or reply in the thread to view the hidden content.

CRUD Operation using PHP/MySQLi with DataTable and TCPDF Tutorial

More options

Link522

452,496

332,845

332,853