What is Datatables & Client side DataTables in PHP?

DataTables is a powerful jQuery plugin that enhances the functionality of HTML tables with advanced features such as sorting, filtering, and pagination. It offers two primary modes of operation: server-side and client-side processing. In this comprehensive guide, we will explore client-side DataTables in PHP, providing detailed explanations and coding examples.

Client side Datatables in PHP?

Introduction

DataTables is widely used in web development for managing and displaying large datasets in a user-friendly manner. It can be implemented in both server-side and client-side modes, each with its advantages and use cases.

How to Implement Client side DataTables in PHP?

1) Implementing Client-Side DataTables in PHP using AJAX

To start with client side DataTables in PHP, you need to include the DataTables library and jQuery in your project. Here’s a basic setup:

1. “index.php” File:
<!DOCTYPE html>
<html>

<head>
    <title>Example of Client side DataTables in PHP</title>

    <!-- Bootstrap CDN -->
    <link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

    <!-- Bootstrap JS CDN -->
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>

    <!-- Datatables CSS CDN -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">

    <!-- JQuery theme -->
    <script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.5.1.js"></script>

    <!-- Datatables JS CDN -->
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>

</head>

<body>
    <div>
        <div class="container">
            <table class="table table-striped" id="table-container">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Website</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Website</th>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>


    <script>
        $(document).ready(function() {
            $('#table-container').DataTable({
                "ajax": "data.php",

                // Define the columns in the table
                "columns": [
                    // The first column displays the 'name' field from the data source 
                    // Sorting functinality enabled
                    {
                        "data": "name",
                        "orderable": true

                    },
                    // The second column displays the 'email' field from the data source 
                    // Sorting functinality enabled
                    // and makes the column not searchable
                    {
                        "data": "email",
                        "orderable": true,
                        "searchable": false

                    },
                    // The third column displays the 'website' field from the data source 
                    // Sorting functinality disabled
                    // and makes the column not searchable
                    {
                        "data": "website",
                        "orderable": false,
                        "searchable": true
                    },

                ],

                // Set the default sorting order for the table
                "order": [
                    // Sort the first column in ascending order
                    [0, "asc"]
                ],

                // Define the options for the table's length dropdown menu
                "lengthMenu": [
                    // The options for the dropdown menu
                    [5, 10, 25, 50, 100, -1],
                    // The labels for the options
                    [5, 10, 25, 50, 100, "All"]
                ],

                // Set the number of rows to display per page
                "pageLength": 5,

                // Make the table responsive
                "responsive": true
            });
        });
    </script>
</body>

</html>

2) Data Format for Client side DataTables in PHP data.php File.

Client-side processing is straightforward as it loads all data into the client’s browser. Here’s an example:

2. “data.php” File:
<?php
$data = [
    ["name" => "Elon Musk", "email" => "elon@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Steve Jobs", "email" => "steve@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Bill Gates", "email" => "bill@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Mark Zuckerberg", "email" => "mark@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Sundar Pichai", "email" => "sundar@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Jeff Bezos", "email" => "jeff@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Larry Page", "email" => "larry@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Satya Nadella", "email" => "satya@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Tim Cook", "email" => "tim@deepdivephp.com", "website" => "deepdivephp.com"],
    ["name" => "Drew Houston", "email" => "drew@deepdivephp.com", "website" => "deepdivephp.com"],
];

echo json_encode(["data" => $data]);

Functionality About Datatables

1) DataTables Sorting and Filtering

Enable sorting and filtering of data within the DataTable for better data manipulation and viewing.

<script>
    $('#myTable').DataTable({
        "ajax": "data.php",
        "columns": [
            // Sorting functinality enabled
            {
                "data": "name",
                "orderable": true

            },
            // Sorting functinality enabled
            {
                "data": "email",
                "orderable": true,

            },
            // Sorting functinality disabled
            {
                "data": "website",
                "orderable": false,
            },
        ],

        // Set the default sorting order for the table
        "order": [
            // Sort the first column in ascending order
            [0, "asc"]
        ],
    });
</script>
Sorting & Filtering in Datatables in PHP

2) DataTables Pagination

Implement pagination to display large data sets in manageable parts.

<script>
 $('#myTable').DataTable({
        "ajax": "data.php",
        "paging": true,
        "pageLength": 5, // Set the number of rows to display per page
    });
</script>
Pagination in Datatables in php

3) DataTables Custom Search

Implement custom search functionality to allow users to filter data based on specific criteria.

1. Table Code

<div class="container">
            <input type="text" class="form-control " id="customSearch" placeholder="Search...">

            <table class="table table-striped" id="table-container">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Website</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Website</th>
                    </tr>
                </tfoot>
            </table>
        </div>

2. Script Code for Custom Search

$(document).ready(function() {
            var table = $('#table-container').DataTable({
                "ajax": "data.php",
                "columns": [{
                        "data": "name"
                    },
                    {
                        "data": "email"
                    },
                    {
                        "data": "website"
                    }
                ]
            })

            $('#customSearch').keyup(function() {
                table.search($(this).val()).draw();
            });
        });

Output:

Custom Search for datatables in PHP

4) Export Datatables in Excel

Enable exporting DataTable data to Excel for offline use and further analysis.

index.php
<!DOCTYPE html>
<html>

<head>
    <title>Example of Client side DataTables in PHP</title>

    <!-- Bootstrap CDN -->
    <link rel="stylesheet" type="text/css" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

    <!-- Bootstrap JS CDN -->
    <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>

    Datatables CSS CDN
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">

    <!-- JQuery theme -->
    <script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.5.1.js"></script>

    <!-- Datatables JS CDN -->
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>

    <!-- Datatables Buttons JS CDN -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/buttons/1.6.2/css/buttons.dataTables.min.css">
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/1.6.2/js/dataTables.buttons.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/buttons/1.6.2/js/buttons.html5.min.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>
</head>

<body>
    <div>
        <div class="container">
            <table class="table table-striped" id="table-container">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Website</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Website</th>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>


    <script>
        $(document).ready(function() {
            $('#table-container').DataTable({
                "ajax": "data.php",
                "columns": [{
                        "data": "name",
                        "orderable": true

                    },
                    {
                        "data": "email",
                        "orderable": true,
                        "searchable": false

                    },
                    {
                        "data": "website",
                        "orderable": false,
                        "searchable": true
                    },

                ],

                // Set the number of rows to display per page
                "pageLength": 5,

               // Export Button Code
                dom: 'Bfrtip',
                buttons: [
                    'excelHtml5'
                ]
            });
        });
    </script>
</body>

</html>
Output:
Datatables Export To Excel
Datatables Export To Excel file

5) Datatables and Bootstrap Integration

Integrate DataTables with Bootstrap for consistent styling and UI components.

Example:

<head>
    <title>DataTables and Bootstrap Integration</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js"></script>
</head>
datatables and bootstrap integration

6) DataTables Editor Integration

Integrate DataTables with an editor to enable inline editing of table data.

<!DOCTYPE html>
<html>

<head>
    <title>Example of Client side DataTables in PHP</title>

    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/jquery.dataTables.css">
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.21/css/dataTables.bootstrap4.min.css">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    <script type="text/javascript" charset="utf8" src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/jquery.dataTables.js"></script>
    <script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.21/js/dataTables.bootstrap4.min.js"></script>

</head>

<body>
    <div>
        <div class="container">
            <table class="table table-striped" id="table-container">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Website</th>
                    </tr>
                </thead>
                <tfoot>
                    <tr>
                        <th>Name</th>
                        <th>Email</th>
                        <th>Website</th>
                    </tr>
                </tfoot>
            </table>
        </div>
    </div>


    <script>
        $(document).ready(function() {
            $('#table-container').DataTable({
                "ajax": "data.php",

                // Define the columns in the table
                "columns": [{
                        "data": "name",
                        "orderable": true

                    },
                    {
                        "data": "email",
                        "orderable": true,
                        "searchable": false

                    },
                    {
                        "data": "website",
                        "orderable": false,
                        "searchable": true
                    },
                ],
                "pageLength": 5,

            });
        });
        $('#table-container').on('click', 'td', function() {
            var cell = $(this);
            var originalContent = cell.text();

            cell.html('<input type="text" value="' + originalContent + '" />');

            $('input', this).on('blur', function() {
                cell.text($(this).val());
            }).focus();
        });
    </script>
</body>

</html>
DataTables Editor Integration Output

FAQ’s

Answer:

  1. Include the DataTables library in your HTML.
  2. Initialize the DataTable using jQuery.
  3. Populate the table with static data or use server-side processing.

Answer: Yes, DataTables can be easily integrated with frameworks like CodeIgniter and Laravel. These frameworks can handle the server-side processing required for DataTables.

 

CodeIgniter Example:

  • Create a controller method to handle server-side processing:
  • public function fetchData()
    {
    // Fetch data from model
    $data = $this->your_model->get_data();
    echo json_encode(array(“data” => $data));
    }

Laravel Example:

  • Create a controller method:
  • public function fetchData()
    {
    $data = YourModel::all();
    return response()->json([‘data’ => $data]);
    }

Answer:

  • Efficiency: Handles large datasets efficiently by fetching data on demand.
  • Performance: Reduces the amount of data loaded into the browser, improving performance.
  • Scalability: Suitable for applications with growing data needs.

Answer: Use server-side processing to fetch data in chunks. Implement pagination, searching, and sorting on the server side.

Answer: Use DataTables’ built-in classes and options to customize the table’s appearance. You can also use CSS to style the table.

Answer:

  • Fixed Header: Keeps the header fixed while scrolling.
  • Row Grouping: Groups rows under a common header.
  • Buttons: Provides export and print functionality.

Answer:

  • Server-Side Processing: Fetch data as needed.
  • Defer Render: Render only the rows in the viewport.
  • Pagination: Display limited rows per page.

Answer:

  • Loading Issues: Ensure paths to DataTables scripts and styles are correct.
  • Performance: Use server-side processing for large datasets.
  • Styling Conflicts: Use DataTables Bootstrap integration if using Bootstrap.

Answer:

  • Step 1: Include DataTables scripts and styles.
  • Step 2: Create an HTML table.
  • Step 3: Initialize DataTable with jQuery.

Yes, DataTables supports AJAX data loading. Use the ajax option to specify the data source.

Answer:

  • Sanitize Inputs: Validate and sanitize user inputs on the server side.
  • Prepared Statements: Use prepared statements to prevent SQL injection.
  • Access Control: Ensure proper authentication and authorization.

Answer:

  • jqGrid: A powerful jQuery grid plugin.
  • Bootstrap Table: A table plugin built for Bootstrap.
  • Handsontable: A JavaScript/HTML5 data grid component.

Leave a Comment

© 2024 Deep Dive PHP
Privacy Policy
Terms
Disclaimer
Affiliate Disclosure