How to implement a jQuery data table in PHP with MySQL - programming fields (2023)

jQuery provides a very powerful function to calldata table. The data table is an open source plugin provided by jQuery. Basically, in a bootstrap table or HTML table, there is no way to sort the data by columns. No search option and no other options. Simply the table is used to display the data in the form of rows and columns. However, if you want to make your table more flexible, attractive and functional, you can do it with thejQuery data tableplugin. Today I'm here with a tutorial on Datatable implementation in PHP.

Data table in PHP

Mainly, the data table offers various features that are not available in any other table. It is an open source jQuery library. It is mobile friendly and offerspagination,data sorting,seek,column order,etc. You can apply this jquery plugin to the bootstrap table which must contain some records.

Handling Ajax PHP forms with jQuery - Submit form without update

So here I will create a project in which I will retrieve data from the database table. I will connect my application from MySQL database. I have some records that I am going to retrieve in a spreadsheet.

create database

Open phpMyAdmin and in the SQL section enter the following code to create the database.

CREATE DATABASE data_table;

Next you need to create a table. Here I have created a table namedProducts

create table

CREATE TABLE „products“ ( „product_id“ int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT, „product_name“ varchar(100) NOT NULL, „sku“ varchar(50) NOT NULL, „brand“ varchar(50) NOT NULL, „ Menge` int(11) NOT NULL, `price_per_unit` varchar(50) NOT NULL, `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Drop-down selection filter in PHP using jQuery Ajax

Dumping data for product table

---- Dumping data for table `products`--INSERT INTO `products` (`product_id`, `product_name`, `sku`, `brand`, `quantity`, `price_per_unit`, `created_on`, ` updated_on`). ) VALUES(1, 'Black Tea', 'BT101', 'Red Label', 43, '430', '2019-09-08 20:19:25', '2019-09-08 20:19:25' ),(2, 'Green Tea', 'BT102', 'Tata Tea', 54, '200', '2019-09-08 20:19:25', '2019-09-08 20:19:25' ),(3, 'Fruit Tea', 'BT103', 'Tata Tea', 25, '250', '2019-09-08 20:19:25', '2019-09-08 20:19:25' ) ,(4, 'Herbal Tea', 'BT104', 'Red Label', 36, '350', '2019-09-08 20:19:25', '2019-09-08 20:19:25' ) ,(5, 'White Tea', 'BT105', 'Tata Tea', 17, '453', '2019-09-08 20:19:25', '2019-09-08 20:19:25' ) ,(6, 'Oolong Tea', 'BT106', 'Tata Tea', 28, '640', '2019-09-08 20:19:25', '2019-09-08 20:19:25 ' ),(7, 'Rooibos Tea', 'BT107', 'Tata Tea', 18, '450', '2019-09-08 20:19:25', '2019-09-08 20:19:25 ' ),(8, 'Mad Angles', 'BT109', 'Bingo', 15, '207', '2019-09-08 20:19:25', '2019-09-08 20:19:25' ),(9, 'Black Tea', 'BT101', 'Red Label', 43, '430', '2019-09-08 20:33:40', '2019-09-08 20:33:40' ),(10, 'Green Tea', 'BT102', 'Tata Tea', 54, '200', '2019-09-08 20:33:40', '2019-09-08 20:33:40' ),(11, 'Obs ttee', 'BT103', 'Tata Tea', 25, '250', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),(12, 'Herbal Tea', 'BT104', 'Red Label', 36, '350', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),(13, 'White Tea', 'BT105', 'Tata Tea', 17, '453', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),(14, 'Oolong -Tea', 'BT106', 'Tata Tea', 28, '640', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),(15, ' Rooibos Tea', 'BT107', 'Tata Tea', 18, '450', '2019-09-08 20:33:40', '2019-09-08 20:33:40'),(16, ' Mad Angles', 'BT109', 'Bingo', 15, '207', '2019-09-08 20:33:40', '2019-09-08 20:33:40');

Create database connection

Before moving to the data part, you need to establish a database connection. Create a new file nameddb-config.phpthen paste the following code. Now don't forget to replace the credentials according to your database name, username and password.

// db-config.php<?php class DBController { private $hostname = "localhost"; privat $username = "root"; privates $password = "root"; private $db = "data_table"; //Verbindung erstellen öffentliche Funktion connect() { $conn = new mysqli($this->hostname, $this->username, $this->password, $this->db)or die("Database connection error." . $conn->connect_error); gib $conn zurück; } // Verbindung schließen public function close($conn) { $conn->close(); } }?>

PHP import of CSV file data into MySQL database with preview

Create a class file

I will follow the object oriented principle to get the data from the database table. Now create a new file in your project directory where we will write the database query.

// product-controller.php<?php class ProductsController { // Konstruktorfunktion __construct($conn) { $this->conn = $conn; } // Produktdaten abrufen public function index() { $data = array(); $sql = "SELECT * FROM products"; $result = $this->conn->query($sql); if($result->num_rows > 0) { $data = mysqli_fetch_all($result, MYSQLI_ASSOC); } $db->close($conn); $daten zurückgeben; } }?>

Now create a new file namedindex.phpin which we will create a bootstrap table. In this table we show the records retrieved via the class file above(product-controller.php).

// index.php<?php require_once './config/db-config.php'; require_once './controller/product-controller.php';$db = new DBController();$conn = $db->connect();$dCtrl = new ProductsController($conn);$products = $dCtrl->index ();?><!DOCTYPE html><html><head><title>Datentabellen-Implementierung in PHP</title><!-- Bootstrap 4 CSS --><link rel="stylesheet" href="https:/ /maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"></head><body><div class="container mt-5"><div class="row"><div class="col-xl-12 col-lg-12 col-md-12 col-12 m-auto"><table class="table table-bordered table-hovered table-striped" id="productTable"><thead ><th> Produkt-ID </th><th> Produktname </th><th> SKU </th><th> Marke </th><th> Menge </th><th> Preis/Einheit < /th></thead><tbody><?php foreach($products as $product) : ?><tr><td> <?php echo $product['product_id']; ?> </td><td> <?php echo $product['product_name']; ?> </td><td> <?php echo $product['sku']; ?> </td><td> <?php echo $product['brand']; ?> </td><td> <?php echo $produkt['menge']; ?> </td><td> <?php echo $product['price_per_unit']; ?> </td></tr><?php endforeach; ?></tbody></table></div></div></div><script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"> </script><script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script><script src="https ://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script><script type="text/javascript" charset="utf8" src="https://cdn .datatables.net/1.10.19/js/jquery.dataTables.js"></script></body></html>

If you run the above code, you will see the result as below. Here the data is displayed on a single page no matter how much data you have in the database table. It prints all data on a single page.

This would be a reason for the slow speed of the website if you have a large amount of data in your database. Therefore, to overcome this, we use pagination that breaks the data into multiple pages.

Uploading PHP files using jQuery and Ajax

Implement DataTable PHP

Now in the table above I will implement the datatable php. So, first of all, download the minified version of CSS and jQuery file from the official websitehttps://datatables.net. You can also use CDN. I will use CDN here.

<!-- CDN-CSS-Datentabelle --><link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.css"> <!-- CDN jQuery Datatable --><script type="text/javascript" charset="utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"> </script>

• Add the CSS between the open and closeKopftag of HTML.

• Also add the jQuery CDN below before the end of the body tag.

• In the last step, add these two lines of script to apply the data table in the HTML table.

// Apply data table in HTML table<script>$(document).ready(function() { $('#productTable').DataTable();});</script>

In the above code, I started a script code using jQuery where I assigned the data table in the HTML table with the id attribute.

So, after adding the above script and CDN theindex.phpThe file will be as shown below.

<?php require_once './config/db-config.php'; require_once './controller/product-controller.php';$db = new DBController();$conn = $db->connect();$dCtrl =new ProductsController($conn);$products = $dCtrl->index ();?><!DOCTYPE html><html><head><title>Datentabellen-Implementierung in PHP</title><!-- Bootstrap 4 CSS --><link rel="stylesheet" href="https:/ /maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css"><link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10 .19/css/jquery.dataTables.css"> </head><body><div class="container mt-5"><div class="row"><div class="col-xl-12 col- lg-12 col-md-12 col-12 m-auto"><table class="table table-bordered table-hovered table-striped" id="productTable"><thead><th> Produkt-ID </th> <th> Produktname </th><th> SKU </th><th> Marke </th><th> Menge </th><th> Preis/Einheit </th></thead><tbody> <?php foreach($products as $product) : ?><tr><td> <?php echo $product['product_id']; ?> </td><td> <?php echo $product['product_name']; ?> </td><td> <?php echo $product['sku']; ?> </td><td> <?php echo $product['brand']; ?> </td><td> <?php echo $produkt['menge']; ?> </td><td> <?php echo $product['price_per_unit']; ?> </td></tr><?php endforeach; ?></tbody></table></div></div></div><script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"> </script><script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.9/umd/popper.min.js"></script><script src="https ://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js"></script><!-- CDN jQuery Datatable --><script type="text/javascript" charset=" utf8" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.js"></script></body></html><!-- Skript --->< script>$(document).ready(function() { $('#productTable').DataTable();});</script>

After running the code above, you can see that the data table has been applied to our existing table.

In the data table result, you have the various functions like pagination, number of records to display, search for specific records, total number of records displayed in the table, and sorting the data in ascending and descending order.

Check if email is available in php with ajax andjQuery

Diploma

We have successfully implemented the jQuery data table in PHP. After applying the data table, we got the different functions in the normal table. I hope this will help you to manage the tables with large number of records. If you need help with this tutorial, please ask in the comments section. I'll help you there.

Related