PHP – jquery datatables with mysql database example from scratch

PHP – jquery datatables with mysql database example from scratch

In this post we will give you information about PHP – jquery datatables with mysql database example from scratch. Hear we will give you detail about PHP – jquery datatables with mysql database example from scratchAnd how to use it also give you demo for it if it is necessary.

Today, i am going to share with you how to use jQuery datatable plugin in php mysql project. i will also describe small example of data table with server side scripting, here i will use PHP and MySQL to get data from server side. So you have to simple follow few step and will get small quick example.

DataTables is a jquery library that provide pagination, instant search and multi-column ordering to HTML table. DataTables easily use with normal jquery ui project, bootstrap. We can simply get records from server side using ajax.

In this example, i created “users” table with id, name, email field in mysql database. i will create index.html page run ajax request to server and server side fetch that data and display on that page using datatables. It is very simple example, using this you can make more complex too.

So after run example successfully, you will get layout like as bellow attach screen shot. Just follow few step and get layout.

Layout:

Step 1: Create users table

In first step we will create new new table “users” in database. You can use following SQL Query for create “users” table, after create “users” table make sure you have to add some dummy records. So let’s create using bellow sql query:

users table:

CREATE TABLE 'users' (

'id' int(10) UNSIGNED NOT NULL,

'name' varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,

'email' varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Step 2: Create config.php File

In this step, we require to create database configuration file, here we will set database name, username and password. So let’s create “config.php” file on your root directory and put bellow code:

config.php

<?php


define (DB_USER, "root");

define (DB_PASSWORD, "root");

define (DB_DATABASE, "phpdemo");

define (DB_HOST, "localhost");


$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_DATABASE);


?>

Also see:Codeigniter 3 and AngularJS CRUD with Search and Pagination Example.

Step 3: Create index.html File

In this step, we will create index.html file, in this file we will write code for display data using datatables library. So let’s create index.html file on your root directory and put bellow code.


index.html

<!DOCTYPE html>

<html>

<head>

<title>PHP - Jquery Datatables Example</title>

<script type="text/javascript" src="https://code.jquery.com/jquery-1.9.1.min.js"></script>

<link rel="stylesheet" type="text/css" href="http://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/css/jquery.dataTables.css">

<script type="text/javascript" charset="utf8" src="https://ajax.aspnetcdn.com/ajax/jquery.dataTables/1.9.4/jquery.dataTables.min.js"></script>

</head>

<body>


<div >

<h2>PHP - Jquery Datatables Example</h2>

<table id="my-example">

<thead>

<tr>

<th>Id</th>

<th>Name</th>

<th>Email</th>

</tr>

</thead>

</table>

</div>


</body>


<script type="text/javascript">

$(document).ready(function() {

$('#my-example').dataTable({

"bProcessing": true,

"sAjaxSource": "pro.php",

"aoColumns": [

{ mData: 'id' } ,

{ mData: 'name' },

{ mData: 'email' }

]

});

});

</script>

</html>

Step 4: Create pro.php File

In last step, we need to create pro.php file, in this file we will write sql query and fetch records from mysql database.

pro.php

&tl;?php


require('config.php');


$sql = "SELECT * FROM users";

$result = $mysqli->query($sql);


while($row = $result->fetch_array(MYSQLI_ASSOC)){

$data[] = $row;

}


$results = ["sEcho" => 1,

"iTotalRecords" => count($data),

"iTotalDisplayRecords" => count($data),

"aaData" => $data ];


echo json_encode($results);


?>

Let’s run bellow command on your root directory for quick run:

php -S localhost:8000

Now you can open bellow URL on your browser:

Also see:PHP MySQL – Simple Image Gallery CRUD example from scratch

http://localhost:8000/index.html

I hope it can help you….

Hope this code and post will helped you for implement PHP – jquery datatables with mysql database example from scratch. if you need any help or any feedback give it in comment section or you have good idea about this post you can give it comment section. Your comment will help us for help you more and improve us. we will give you this type of more interesting post in featured also so, For more interesting post and code Keep reading our blogs

For More Info See :: laravel And github

Leave a Comment

Your email address will not be published. Required fields are marked *

  +  17  =  21

We're accepting well-written guest posts and this is a great opportunity to collaborate : Contact US