Sunday 8 August 2021

Server Side Processing Vanilla JavaScript DataTables in PHP


If you are looking for Non jQuery version of DataTables which has been use pure vanilla JavaScript then you have come on the right place because in this tutorial we will show you how to use pure vanilla JavaScript DataTables with server side processing in PHP with dynamic Mysql table data. Currently most of the developer has use pure vanilla javascript in their web development. So if you have load data in jQuery DataTable but still you want to use jQuery DataTable then you have to include jQuery in your web application. But you want to perform all operation which you have perform in jQuery DataTable then here we have use Vanilla JavaScript DataTable in which you can perform all operation like searching, pagination, sorting etc under this Vanilla JavaScript DataTable without jQuery dependencies.

In this tutorial, we will use JSTable Javascript plugin which is lightweight and dependency free javascript and by using this plugin you can make your HTML table more interactive. This JSTable JavaScript library is very similar to jQuery DataTabes library but this JSTable library is not use jQuery but it has been used vanilla javascript.

This JSTable is a pure vanilla JavaScript based datatable component and it has features like we can load dynamic tabular data, we can filter or search data, we can sort data, it has automatically make pagination link and many more. By using JSTable library, we can perform server-side processing of data, which is inspired by jQuery datatables.

Under this tutorial, we will show you how to use this JSTable JavaScript DataTable in our web application and then after we will show you how can we perform server-side processing of data with PHP & Mysql under this JSTable JavaScript DataTable. Below you can find the complete source code of Vanilla DataTable Server-side Processing of Data in PHP.

Server Side Processing Vanilla JavaScript DataTable in PHP






Source Code


index.php



<?php

include('function.php');

?>

<!doctype html>
<html lang="en">
    <head>
        <!-- Required meta tags -->
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">

        <!-- Bootstrap CSS -->
        <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">

        <link rel="stylesheet" type="text/css" href="library/jstable.css" />

        <script src="library/jstable.min.js" type="text/javascript"></script>

        <title>Vanill JavaScript DataTables - Server-side Processing</title>
    </head>
    <body>

        <div class="container">
            <h1 class="mt-5 mb-5 text-center text-success"><b>Vanill JavaScript DataTables - Server-side Processing in PHP</b></h1>

            <div class="card">
                <div class="card-header">Vanilla JavaScript DataTables</div>
                <div class="card-body">
                    <div class="table-responsive">
                        <table id="customer_table" class="table table-bordered table-striped">
                            <thead>
                                <tr>
                                    <th>First Name</th>
                                    <th>Last Name</th>
                                    <th>Email</th>
                                    <th>Gender</th>
                                </tr>
                            </thead>
                            <tbody>
                                <?php echo fetch_top_five_data($connect); ?>
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </body>
</html>

<script>

new JSTable('#customer_table', {
    serverSide:true,
    deferLoading : <?php echo count_all_data($connect); ?>,
    ajax : 'fetch.php'
});

</script>





function.php



<?php

//function.php

$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");

function fetch_top_five_data($connect)
{
	$query = "
	SELECT * FROM customer_table 
	ORDER BY customer_id DESC 
	LIMIT 5
	";

	$result = $connect->query($query);

	$output = '';

	foreach($result as $row)
	{
		$output .= '
		<tr>
			<td>'.$row["customer_first_name"].'</td>
			<td>'.$row["customer_last_name"].'</td>
			<td>'.$row["customer_email"].'</td>
			<td>'.$row["customer_gender"].'</td>
		</tr>
		';
	}

	return $output;
}

function count_all_data($connect)
{
	$query = "SELECT * FROM customer_table";

	$statement = $connect->prepare($query);

	$statement->execute();

	return $statement->rowCount();
}

?>


fetch.php



<?php

//fetch.php

include('function.php');

/*This $_GET variable received in Ajax Request Url*/

//"start"			-	the data offset
$startGET = filter_input(INPUT_GET, "start", FILTER_SANITIZE_NUMBER_INT);
$start = $startGET ? intval($startGET) : 0;

//"length"			-	the length of the returning data
$lengthGET = filter_input(INPUT_GET, "length", FILTER_SANITIZE_NUMBER_INT);
$length = $lengthGET ? intval($lengthGET) : 10;

//"searchQuery"		- 	the search text
$searchQuery = filter_input(INPUT_GET, "searchQuery", FILTER_SANITIZE_STRING);
$search = empty($searchQuery) || $searchQuery === "null" ? "" : $searchQuery;

//"sortColumn"  	-	the index of the to sorted column
$sortColumnIndex = filter_input(INPUT_GET, "sortColumn", FILTER_SANITIZE_NUMBER_INT);

//"sortDirection"	-	the sorting direction (asc or desc)
$sortDirection = filter_input(INPUT_GET, "sortDirection", FILTER_SANITIZE_STRING);

$column = array("customer_first_name", "customer_last_name", "customer_email", "customer_gender");

$query = "SELECT * FROM customer_table ";

$query .= '
	WHERE customer_id LIKE "%'.$search.'%" 
	OR customer_first_name LIKE "%'.$search.'%" 
	OR customer_last_name LIKE "%'.$search.'%" 
	OR customer_email LIKE "%'.$search.'%" 
	OR customer_gender LIKE "%'.$search.'%" 
';

if($sortColumnIndex != '')
{
	$query .= 'ORDER BY ' . $column[$sortColumnIndex].' '.$sortDirection . ' ';
}
else
{
	$query .= 'ORDER BY customer_id DESC ';
}

$query1 = '';

if($length != -1)
{
	$query1 = 'LIMIT ' . $start . ', ' . $length;
}

$statement = $connect->prepare($query);

$statement->execute();

$number_filter_row = $statement->rowCount();

$result = $connect->query($query . $query1);

$data = array();

foreach($result as $row)
{
	$sub_array = array();

	$sub_array[] = $row['customer_first_name'];

	$sub_array[] = $row['customer_last_name'];

	$sub_array[] = $row['customer_email'];

	$sub_array[] = $row['customer_gender'];

	$data[] = $sub_array;
}

$output = array(
	'recordsTotal'			=>	count_all_data($connect),
	'recordsFiltered'		=>	$number_filter_row,
	'data'					=>	$data
);

echo json_encode($output);

?>







38 comments:

  1. Replies
    1. Hi, We have send source code file at your email address, so check it, if you have not received please comment here.

      Delete
  2. Replies
    1. Hi, We have send source code file at your email address, so check it, if you have not received please comment here.

      Delete
  3. Replies
    1. Hi, We have send source code file at your email address, so check it, if you have not received please comment here.

      Delete
  4. Replies
    1. Hi, We have send source code file at your email address, so check it, if you have not received please comment here.

      Delete
  5. Interesante con vanilla, ya no depende de JQuery, pero como hago para filtras con controles externos?.

    ReplyDelete
  6. Replies
    1. Hi, We have send source code file at your email address, so check it, if you have not received please comment here.

      Delete
  7. aliasger@overturesinfotech.com

    ReplyDelete
    Replies
    1. Hi, We have send source code file at your email address, so check it, if you have not received please comment here.

      Delete
  8. Replies
    1. Hi, We have send source code file at your email address, so check it, if you have not received please comment here.

      Delete
  9. BerndGemm@googlemail.com
    Wunderful Script. Can i get the Source Code. Thank you.

    ReplyDelete
  10. Hi, i am from Indonesia, i learn much from your website. Please provide me the source code. My address is fahmi.nsk@gmail.com

    ReplyDelete
    Replies
    1. Hi, We have send source code file at your email address, so check it, if you have not received please comment here.

      Delete
  11. Can you email me the full source code with add, edit & delete functionalities ?

    ReplyDelete
  12. Hi, thanks for your code. it helped me a lot. The table for my project has got a horizontal scroll. I want show data for rows in new lane instead of having to scroll to the side to see the end of the row. How can i do that. Thanks

    ReplyDelete