Tuesday 24 April 2018

How to Use Jquery Datatable with AngularJS & PHP


We all know Jquery Datatables plugin is a high level premium jquery plugin which will convert simple HTML table data into tabular grid format. In previous, in one of our web tutorial which we have publish on JQuery Datatables Server Side Processing in PHP and we have received very huge response from our site viewer and they have requested us to make tutorial on Jquery Datatables server side processing by using AngularJS Datatable with PHP. Because Angular Datatable is one of the angular module which has been provide only datatable directive but also datatable options and helpers. With the help of Angular Datatables module, we can make stylish tabular grid listing in our angular web application with functionality like pagination, searching, sorting etc. So here in this post we will discuss on topic like Angular Datatables server side processing with PHP script and MySQL database. In this post we have not only covered tutorial in easy steps but also you can find video tutorial, live demo and source code on how to use jquery datatables plugin with AngularJS and PHP.







Following is the file structure of this tutorial source code.

  • index.html
  • fetch.php

Step 1 - Create Database with Data


Here we have covered topic on JQuery Datatables Server Side processing using AngularJs PHP, for this first we have to create customer table and and insert some customer data into table table.


--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_customer`
--

CREATE TABLE `tbl_customer` (
  `CustomerID` int(11) NOT NULL,
  `CustomerName` varchar(250) NOT NULL,
  `Address` text NOT NULL,
  `City` varchar(250) NOT NULL,
  `PostalCode` varchar(30) NOT NULL,
  `Country` varchar(100) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_customer`
--

INSERT INTO `tbl_customer` (`CustomerID`, `CustomerName`, `Address`, `City`, `PostalCode`, `Country`) VALUES
(1, 'Willie Whited', '2524 White River Way', 'Salt Lake City', '84106', 'USA'),
(2, 'Lisa Squires', 'Fehringer Strasse 27', 'MARIA BUCH', '8750', 'Austria'),
(3, 'Sean Patterson', 'Rue des Ecoles 426', 'Vlierzele', '9520', 'Belgium'),
(4, 'Anna Scott', 'Rua C 66, 1384', 'Goiania-GO', '74305-450', 'Brazil'),
(5, 'Desmond Peterson', '1414 Grey Rd', 'Feversham', 'ON N0C 1C0', 'Canada'),
(6, 'Samuel Hogan', '13, Avenue De Marlioz', 'ARGENTEUIL', '95100', 'France'),
(7, 'John Miller', 'Pappelallee 21', 'Arnsdorf', '09661', 'Germany'),
(8, 'Rose Joyce', 'Via Galileo Ferraris, 38', 'Malavicina MN', '46040', 'Italy'),
(9, 'Phillip Tilton', 'Huibertplaat 120', 'DH  Zwolle', '8032', 'Netherland'),
(10, 'Anita McGurk', '128 St Pauls Court Cloverlea', 'Palmerston North', '4412', 'New Zealand'),
(11, 'John Morgan', '286 Stanza Bopape St', 'Louis Trichardt', '0923', 'South Africa'),
(12, 'Margaret Teets', 'Grossmatt 62', 'Betschwanden', '8777', 'Switzerland'),
(13, 'Dara Adams', '21 Fraserburgh Rd', 'LINNELS', 'NE46 8YB', 'United Kingdom'),
(14, 'Bennie J. Martin', '34 Masthead Drive', 'PARK AVENUE QLD', '4701', 'Australia'),
(15, 'Gladys Ashford', 'Holzstrasse 14', 'SALCHENDORF', '9064', 'Austria'),
(16, 'William Lavallie', 'Heirstraat 31', 'Marchin', '4570', 'Belgium'),
(17, 'Antonio Wayman', '2331 Carlson Road', 'Prince George', 'BC V2L 5E5', 'Canada'),
(18, 'Carol Selders', 'Ysitie 44', 'TAMPERE', '33240', 'Finland'),
(19, 'David Sato', '30, Rue de la Pompe', 'MANOSQUE', '04100', 'France'),
(20, 'Amy Vanmatre', 'Friedrichstrasse 4', 'Dusseldorf Flehe', '40223', 'Germany'),
(21, 'Kenny Todd', 'Corso Porta Nuova, 10', 'Quara RE', '42010', 'Italy'),
(22, 'Marla Alvarez', 'Tielstraat 17', 'Amsterdam-Zuidoost', '1107 RC', 'Netherland'),
(23, 'George Stanley', '95 Belle Verde Drive Rothesay Bay', 'North Shore', '0630', 'New Zealand'),
(24, 'David Bennett', 'Torvbakkgata 219', 'OSLO', '0550', 'Norway'),
(25, 'Donald Garrett', '86 St Denys Road', 'POYS STREET', 'IP17 9TF', 'United Kingdom'),
(26, 'Horace Morgan', '3435 Jarvis Street', 'Buffalo', '14202', 'United States');


Step 2 - Insert Angular, Angular Datatables, Bootstrap and Jquery File


After this we want to include different library files like AngularJs, AngularJS Datatables plugin, Jquery Datatables plugin, Jquery library and Bootstrap lirary link at header of our index page.


     <head>
  <script src="jquery.min.js"></script>
  <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.15/angular.min.js"></script>
  <script src="angular-datatables.min.js"></script>
  <script src="jquery.dataTables.min.js"></script>
  <link rel="stylesheet" href="bootstrap.min.css">
  <link rel="stylesheet" href="datatables.bootstrap.css">
 </head>


Step 3 - Write HTML Code


Now we have to write some HTML code in index.html page. In this code we have define ng-app and ng-controller angular directive which will load module and handle the controller in our AngularJS application.


                   <div ng-app="customerApp" ng-controller="customerController" class="container">

   <br />
   <h3 align="center">How to Use Jquery Datatable with AngularJS & PHP</h3>
   <br />

   <table datatable="ng" dt-options="vm.dtOptions" class="table table-striped table-bordered">
    <thead>
     <tr>
      <th>Sr</th>
      <th>Customer Name</th>
      <th>Address</th>
      <th>City</th>
      <th>Postal Code</th>
      <th>Country</th>
     </tr>
    </thead>
    <tbody>
     <tr ng-repeat="customer in customers">
      <td>{{ $index + 1 }}</td>
      <td>{{ customer.CustomerName }}</td>
      <td>{{ customer.Address }}</td>
      <td>{{ customer.City }}</td>
      <td>{{ customer.PostalCode }}</td>
      <td>{{ customer.Country }}</td>
     </tr>
    </tbody>
   </table>
   <br />
   <br />
  </div>


Step 4 - Write AngularJS code for Fetch Data


After this we have to move to write AngularJS code in which we have load AngularJS datatables module and also handle controller. After this we have send Ajax request to PHP server side script to fetch.php and get customers data from our Mysql database.


<script>

var app = angular.module('customerApp', ['datatables']);

app.controller('customerController', function($scope, $http){
 $http.get('fetch.php').success(function(data, status, headers, config){
  $scope.customers = data;
 });
});

</script>


Step 5 - Fetch Recrods from Mysql Database


Lastly we have to write PHP code in fetch.php file and fetch data from Mysql database table tbl_customer. Here we want to convert data in JSON format for display data on datatables. So for this we have first store data in PHP Array and convert into JSON string by using json_encode() function.


<?php

//fetch.php

$connect = new PDO('mysql:host=localhost;dbname=test', 'root', '');

$query = "SELECT * FROM tbl_customer ORDER BY CustomerID DESC";

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

$statement->execute();

while($row = $statement->fetch(PDO::FETCH_ASSOC))
{
 $data[] = $row;
}

echo json_encode($data);

?>


If you want to see live demo of Jquery Datatables server side processing using AngularJS & PHP, so you have to see demo link below and if you want to download complete source code then also you can find download link below also.






5 comments:

  1. dear sir please make a tutorials of inventory and invoice system using Angular JS PHP & Mysql

    ReplyDelete
  2. To translate the datable into Brazilian Portuguese?

    limaand@gmail.com

    ReplyDelete
  3. when data has ΓΌ character, cannot return any rows. Any idea why ?

    ReplyDelete
  4. How about 1000 records....very slow !!!!

    ReplyDelete
  5. this isnt server side processing & pagination at all! you are showing client side processing.

    ReplyDelete