How to Use DataTable in PHP
Do you want to integrate DataTable in your website and projects? DataTable add advanced controls to your HTML tables. In this article, we show you how to use DataTable in PHP. DataTable nowadays a very popular for listing of records in the projects. It is an open source and easy to use.
For our tutorial, we will create a table in the database and will display table records in the DataTable. The final output will look as below:
Getting Started
Open your phpMyAdmin and create a database with name (school) and create a table (students) in it using the below query:
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) NOT NULL,
`last_name` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
We have created a simple table in database with the columns like first_name, last_name, and age. Add dummy entries in your database table.
Create a conn.php file and write a below code for database connection and save it.
<?php
$con=mysqli_connect("localhost","root","","school");
if ($con->connect_errno) {
echo "Error: " . $con->connect_error;
}
?>
Make sure to replace the placeholders with the actual values.
Let’s create an index.php file and add the code below in it and save it at same place where you save your conn.php file.
<?php
require_once('conn.php'); // To connect the database.
$sql = "SELECT id, first_name, last_name, age FROM students"; //query for fetching record
$result = $conn->query($sql);
$arr_users = [];
if ($result->num_rows > 0) {
$arr_users = $result->fetch_all(MYSQLI_ASSOC); //php veriable to store the fetched record.
}
?>
We have fetched all data from the students table and assigned them to the PHP variable $arr_users. Next, we will loop through this variable and print data in simple HTML table.
<table id="usetTable" class="display">
<thead>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Actions</th>
</thead>
<tbody>
<?php if(!empty($arr_users)) { ?>
<?php foreach($arr_users as $user) { ?>
<tr>
<td><?php echo $user['first_name']; ?></td>
<td><?php echo $user['last_name']; ?></td>
<td><?php echo $user['age']; ?></td>
<td><a href="#" class="btn”> Edit </a><a href="#" class="btn1”> Delete</a></td>
</tr>
<?php } ?>
<?php } ?>
</tbody>
</table>
Output of the above html and php code.
Now How to apply DataTable on web page.
In order to integrate dataTable into our page. We have to use the below minified version of libraries you can include these libraries with the usual scripts and links element as you would do for any other jquery and css libraries:
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.css"/>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.js"></script>
<!--In order to make your dataTable beautiful we need to use bootstrap library.-->
<link rel="stylesheet" type="text/css" href"https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"/>
Here is the Images before and after adding the bootstrap library.
Image before adding bootstrap library.
Image after adding bootstrap library.
Here is a Code setup image after adding the above libararies.
Use of DataTable in PHP is straight-forward. We will fetch records from our database, display it in a table through the loop, apply DataTable.
Our final code is as follows;
<?php
require_once('conn.php');
$sql = "SELECT id, first_name, last_name, age FROM students";
$result = $con->query($sql);
$arr_users = [];
if ($result->num_rows > 0) {
$arr_users = $result->fetch_all(MYSQLI_ASSOC);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<title>Datatable</title>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.css"/>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css">
</head>
<body>
<div class="container mt-5">
<table id="usetTable" class="table">
<thead>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Actions</th>
</thead>
<tbody>
<?php if(!empty($arr_users)) { ?>
<?php foreach($arr_users as $user) { ?>
<tr>
<td><?php echo $user['first_name']; ?></td>
<td><?php echo $user['last_name']; ?></td>
<td><?php echo $user['age']; ?></td>
<td><a href="#" class="btn btn-info" ><i class="fa fa-edit"></i>Edit
</a><a href="#" class="btn btn-danger" ><i class="fa fa-trash">
</i>Delete</a></td>
</tr>
<?php } ?>
<?php } ?>
</tbody>
</table>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.16/datatables.min.js"></script>
<!—below script we use to initialize the datatable using dataTable id-->
<script>
$(document).ready(function() {
$('#usetTable').DataTable();
} );
</script>
</body>
</html>
After adding the above code here is the final output of dataTable in php.
Final Output Image
We hope you may learn how to use DataTable in PHP. Please share your thoughts in the comment section below.