What's new

Serverside datatable api

J

jay41

Forum Veteran
Joined
Nov 13, 2016
Posts
1,927
Reaction
472
Points
658
Age
24
Create serverside processing datatable api using php, mysql, html, css and jquery
 
Sure, I can give you a basic example of how to create a serverside processing datatable API using PHP, MySQL, HTML, CSS, and jQuery. Here are the steps:

1. Database setup
First, you need to create a MySQL database and a table to store the data you want to display in the datatable. Here's an example SQL query to create a table:

Code: 
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) NOT NULL,
  `office` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `salary` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. Server-side code
Create a PHP script to handle the server-side processing of the datatable. In this script, you will query the database to retrieve the data and then return it as JSON to the datatable.

Code: 
<?php

// Include database connection file
include "db_connect.php";

// Define datatable columns
$columns = array(
    0 => 'id',
    1 => 'name',
    2 => 'position',
    3 => 'office',
    4 => 'age',
    5 => 'start_date',
    6 => 'salary'
);

// Get total number of records in the table
$totalRecords = mysqli_query($connection, "SELECT COUNT(*) FROM employees")->fetch_row()[0];

// Get total number of records after filtering
$searchQuery = '';
if(!empty($_POST['search']['value'])){
    $searchQuery = " WHERE (name LIKE '%".$_POST['search']['value']."%' OR position LIKE '%".$_POST['search']['value']."%' OR office LIKE '%".$_POST['search']['value']."%' OR age LIKE '%".$_POST['search']['value']."%' OR start_date LIKE '%".$_POST['search']['value']."%' OR salary LIKE '%".$_POST['search']['value']."%')";
}
$totalFiltered = mysqli_query($connection, "SELECT COUNT(*) FROM employees".$searchQuery)->fetch_row()[0];

// Get data according to datatable parameters
$limit = '';
if(isset($_POST['length'])){
    $limit = " LIMIT ".$_POST['start'].", ".$_POST['length'];
}
$order = '';
if(isset($_POST['order'])){
    $order = " ORDER BY ".$columns[$_POST['order'][0]['column']]." ".$_POST['order'][0]['dir'];
}
$data = array();
$query = "SELECT * FROM employees".$searchQuery.$order.$limit;
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_array($result)){
    $nestedData = array();
    $nestedData[] = $row['id'];
    $nestedData[] = $row['name'];
    $nestedData[] = $row['position'];
    $nestedData[] = $row['office'];
    $nestedData[] = $row['age'];
    $nestedData[] = date('d-m-Y', strtotime($row['start_date']));
    $nestedData[] = '$'.$row['salary'];
    $data[] = $nestedData;
}

// Return data as JSON
$jsonData = array(
    "draw"            => intval($_POST['draw']),
    "recordsTotal"    => intval($totalRecords),
    "recordsFiltered" => intval($totalFiltered),
    "data"            => $data
);
echo json_encode($jsonData);

?>

3. HTML markup
Create an HTML file to display the datatable on the frontend. Include the necessary CSS and JS files for the datatable and jQuery.

Code: 
<!DOCTYPE html>
<html>
<head>
    <title>Server-side Processing Datatable API</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css">
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
</head>
<body>

<table id="employeeData" class="display" style="width:100%">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start Date</th>
            <th>Salary</th>
        </tr>
    </thead>
</table>

<script type="text/javascript">
$(document).ready(function() {
    $('#employeeData').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "server.php",
            "type": "POST",
            "data": function ( d ) {
                d.search.value = $('#searchInput').val();
            }
        },
        "columns": [
            { "data": "0" },
            { "data": "1" },
            { "data": "2" },
            { "data": "3" },
            { "data": "4" },
            { "data": "5" },
            { "data": "6" }
        ]
    });

    $('#searchInput').keyup(function(){
        $('#employeeData').DataTable().ajax.reload();
    });
});
</script>

<input type="text" id="searchInput" placeholder="Search...">

</body>
</html>

That's it! This code should give you a basic example of how to create a serverside processing datatable API using PHP, MySQL, HTML, CSS, and jQuery. You can customize the code to fit your specific needs.
 
🤖 said:
Sure, I can give you a basic example of how to create a serverside processing datatable API using PHP, MySQL, HTML, CSS, and jQuery. Here are the steps:

1. Database setup
First, you need to create a MySQL database and a table to store the data you want to display in the datatable. Here's an example SQL query to create a table:

Code: 
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) NOT NULL,
  `office` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `salary` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. Server-side code
Create a PHP script to handle the server-side processing of the datatable. In this script, you will query the database to retrieve the data and then return it as JSON to the datatable.

Code: 
<?php

// Include database connection file
include "db_connect.php";

// Define datatable columns
$columns = array(
    0 => 'id',
    1 => 'name',
    2 => 'position',
    3 => 'office',
    4 => 'age',
    5 => 'start_date',
    6 => 'salary'
);

// Get total number of records in the table
$totalRecords = mysqli_query($connection, "SELECT COUNT(*) FROM employees")->fetch_row()[0];

// Get total number of records after filtering
$searchQuery = '';
if(!empty($_POST['search']['value'])){
    $searchQuery = " WHERE (name LIKE '%".$_POST['search']['value']."%' OR position LIKE '%".$_POST['search']['value']."%' OR office LIKE '%".$_POST['search']['value']."%' OR age LIKE '%".$_POST['search']['value']."%' OR start_date LIKE '%".$_POST['search']['value']."%' OR salary LIKE '%".$_POST['search']['value']."%')";
}
$totalFiltered = mysqli_query($connection, "SELECT COUNT(*) FROM employees".$searchQuery)->fetch_row()[0];

// Get data according to datatable parameters
$limit = '';
if(isset($_POST['length'])){
    $limit = " LIMIT ".$_POST['start'].", ".$_POST['length'];
}
$order = '';
if(isset($_POST['order'])){
    $order = " ORDER BY ".$columns[$_POST['order'][0]['column']]." ".$_POST['order'][0]['dir'];
}
$data = array();
$query = "SELECT * FROM employees".$searchQuery.$order.$limit;
$result = mysqli_query($connection, $query);
while($row = mysqli_fetch_array($result)){
    $nestedData = array();
    $nestedData[] = $row['id'];
    $nestedData[] = $row['name'];
    $nestedData[] = $row['position'];
    $nestedData[] = $row['office'];
    $nestedData[] = $row['age'];
    $nestedData[] = date('d-m-Y', strtotime($row['start_date']));
    $nestedData[] = '$'.$row['salary'];
    $data[] = $nestedData;
}

// Return data as JSON
$jsonData = array(
    "draw"            => intval($_POST['draw']),
    "recordsTotal"    => intval($totalRecords),
    "recordsFiltered" => intval($totalFiltered),
    "data"            => $data
);
echo json_encode($jsonData);

?>

3. HTML markup
Create an HTML file to display the datatable on the frontend. Include the necessary CSS and JS files for the datatable and jQuery.

Code: 
<!DOCTYPE html>
<html>
<head>
    <title>Server-side Processing Datatable API</title>
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css">
    <script type="text/javascript" src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
</head>
<body>

<table id="employeeData" class="display" style="width:100%">
    <thead>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Position</th>
            <th>Office</th>
            <th>Age</th>
            <th>Start Date</th>
            <th>Salary</th>
        </tr>
    </thead>
</table>

<script type="text/javascript">
$(document).ready(function() {
    $('#employeeData').DataTable({
        "processing": true,
        "serverSide": true,
        "ajax": {
            "url": "server.php",
            "type": "POST",
            "data": function ( d ) {
                d.search.value = $('#searchInput').val();
            }
        },
        "columns": [
            { "data": "0" },
            { "data": "1" },
            { "data": "2" },
            { "data": "3" },
            { "data": "4" },
            { "data": "5" },
            { "data": "6" }
        ]
    });

    $('#searchInput').keyup(function(){
        $('#employeeData').DataTable().ajax.reload();
    });
});
</script>

<input type="text" id="searchInput" placeholder="Search...">

</body>
</html>

That's it! This code should give you a basic example of how to create a serverside processing datatable API using PHP, MySQL, HTML, CSS, and jQuery. You can customize the code to fit your specific needs.
Click to expand...
Create table with page and show entries using jquery and php
 
jay41 said:
Create table with page and show entries using jquery and php
Click to expand...
Sure, I can give you a basic example of how to create a table with pagination and show entries using jQuery and PHP. Here are the steps:

1. Database setup
First, you need to create a MySQL database and a table to store the data you want to display in the table. Here's an example SQL query to create a table:

Code: 
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) NOT NULL,
  `office` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `salary` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. Server-side code
Create a PHP script to handle the server-side processing of the table. In this script, you will query the database to retrieve the data and return it as JSON to the frontend.

Code: 
<?php

// Include database connection file
include "db_connect.php";

// Get total number of records
 
🤖 said:
Sure, I can give you a basic example of how to create a table with pagination and show entries using jQuery and PHP. Here are the steps:

1. Database setup
First, you need to create a MySQL database and a table to store the data you want to display in the table. Here's an example SQL query to create a table:

Code: 
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `position` varchar(255) NOT NULL,
  `office` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `salary` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

2. Server-side code
Create a PHP script to handle the server-side processing of the table. In this script, you will query the database to retrieve the data and return it as JSON to the frontend.

Code: 
<?php

// Include database connection file
include "db_connect.php";

// Get total number of records
Click to expand...
Complete this code
 
jay41 said:
Complete this code
Click to expand...
Sure! Here's the complete PHP code for the server-side script:

PHP: 
<?php

// Include database connection file
include "db_connect.php";

// Get total number of records
$totalRecords = mysqli_query($connection, "SELECT COUNT(*) as total FROM employees")->fetch_assoc()['total'];

// Define variables for pagination
$limit = isset($_POST['limit']) ? $_POST['limit'] : 10;
$page = isset($_POST['page']) ? $_POST['page'] : 1;
$start = ($page - 1) * $limit;

// Get data from database
$query = "SELECT * FROM employees LIMIT $start, $limit";
$result = mysqli_query($connection, $query);

// Create an array to hold data
$data = array();
while($row = mysqli_fetch_assoc($result)) {
    $data[] = $row;
}

// Return data as JSON
echo json_encode(array(
    'totalRecords' => $totalRecords,
    'data' => $data
));

?>

This script retrieves the total number of records and the requested page and limit from the frontend. It then queries the database to retrieve the data for the current page and returns it as JSON to the frontend.

3. Frontend code
Create an HTML file to display the table on the frontend. Include the necessary CSS and JS files for pagination and jQuery.

```html
<!DOCTYPE html>
<html>
<head>
<title>Table with Pagination and Show Entries</title>
<link rel="stylesheet" href="You do not have permission to view the full content of this post. Log in or register now.">
<script src="You do not have permission to view the full content of this post. Log in or register now."></script>
<script src="You do not have permission to view the full content of this post. Log in or register now."></script>
</head>
<body>

<div class="container">
<h2>Table with Pagination and Show Entries</h2>
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Position</th>
<th>Office</th>
<th>Age</th>
<th>Start Date</th>
<th>Salary</th>
</tr>
</thead>
<tbody></tbody>
</table>
<div class="row">
<div class="col-sm-6">
<div class="form-inline">
<label for="showEntries">Show entries:</label>
<select id="showEntries" class="form-control">
<option value="10">10</option>
<option value="25">25</option>
<option value="50">50</option>
<option value="100">100</option>
</select>
</div>
</div>
<div class="col-sm-6">
<ul id="pagination" class="pagination pull-right"></ul>
</div>
</div>
</div>

<script>
$(document).ready(function() {
var limit = 10;
var page = 1;
var totalPages = 1;

// Get initial data
getData();

// Handle show entries change
$('#showEntries').change(function() {
limit = $(this).val();
getData();
 

Similar threads

Popular Tags

android api app arduino bot c c++ capstone capstone project code coding coding and programming css database developer development help how how to html in java javascript language laravel mag mysql need patulong php program programmer programming programming language project python question react script sql system thesis tutorial udemy vb.net visual basic web web development website with

About this Thread

  • 5
    Replies
  • 280
    Views
  • 2
    Participants
Last reply from:
🤖

New Topics

Popular On This Forum

Online statistics

Members online
1,225
Guests online
5,325
Total visitors
6,550
Back
Top