How to retrieve data from the MYSQL database in (OOP) PHP and display using Angular JS - Part 1
Overview
This tutorial explains how to get data from the MySQL database using the PHP object oriented approach and display the data using Angular JS. This tutorial series also explains how to sort table content by clicking the table header, how to filter/search for a particular item/row from the retrieved data and how to add pagination controls.
This tutorial includes step by step explanation for:
- Creating database
- Establishing a database connection
- Retrieving the data using PHP
- Passing the data to the Angular JS
- Displaying Data using Angular JS in table format
- Implementing the search filter
- Sorting the table data
- Adding the pagination
How to create sample database
As the first step you need to create a database. In this example I have created a database named `tutorial` and table named ‘employee’. The `employee` table contains the following fields. For testing purpose insert some sample data or you can copy and paste the SQL queries below which create the `tutorial` database and the table named `employee`.
Employee Table
Fields
| Data type
| Values
|
|---|---|---|
EmpNo
| Int(11)
| Not Null
|
Ename
| varchar(25)
| Not Null
|
Job
| varchar(25)
| Not Null
|
Hiredate
| date
| Not Null
|
sal
| decimal(10,2)
| Not Null
|
comm
| decimal(10,2)
| Null
|
deptname
| varchar(25)
| Not Null
|
Sample Database
-- -- Database: `tutorial` -- CREATE DATABASE IF NOT EXISTS `tutorial` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `tutorial`; -- -------------------------------------------------------- -- -- Table structure for table `employee` -- CREATE TABLE IF NOT EXISTS `employee` ( `empno` int(11) NOT NULL, `ename` varchar(25) NOT NULL, `job` varchar(25) NOT NULL, `hiredate` date NOT NULL, `sal` decimal(10,2) NOT NULL, `comm` decimal(10,2) NOT NULL, `deptname` varchar(25) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `employee` (`empno`, `ename`, `job`, `hiredate`, `sal`, `comm`, `deptname`) VALUES (7369, 'SMITH', 'CLERK', '2010-12-17', '800.00', '0.00', 'RESEARCH'), (7499, 'ALLEN', 'SALESMAN', '2011-02-20', '1600.00', '300.00', 'Sales'), (7521, 'WARD', 'SALESMAN', '2011-02-22', '1250.75', '500.00', 'Sales'), (7566, 'JONES', 'MANAGER', '2011-04-02', '2975.00', '0.00', 'RESEARCH'), (7654, 'MARTIN', 'SALESMAN', '2011-09-28', '1250.00', '1400.00', 'Sales'), (7698, 'BLAKE', 'MANAGER', '2011-05-01', '2850.00', '0.00', 'Sales'), (7782, 'CLARK', 'MANAGER', '2011-06-09', '2450.00', '0.00', 'ACCOUNTING'), (7788, 'SCOTT', 'ANALYST', '2012-12-09', '3000.00', '0.00', 'RESEARCH'), (7839, 'KING', 'PRESIDENT', '2011-11-17', '5000.00', '0.00', 'ACCOUNTING'), (7844, 'TURNER', 'SALESMAN', '2011-09-08', '1500.00', '0.00', 'Sales'), (7876, 'ADAMS', 'CLERK', '2013-01-12', '1100.00', '0.00', 'RESEARCH'), (7900, 'JAMES', 'CLERK', '2011-12-03', '950.00', '0.00', 'Sales'), (7902, 'FORD', 'ANALYST', '2011-12-03', '3000.00', '0.00', 'RESEARCH'), (7934, 'MILLER', 'CLERK', '2012-01-23', '1300.00', '0.00', 'ACCOUNTING');
Establish a database connection
After creating the database and ‘employee’ table we need to establish a connection between the MYSQL database server and PHP. For establishing the connection I have created a file named `connection.php`.
In 'connection.php' file I have created a class named connection and have declared a function 'dbConnect'.The ‘dbConnect’ function creates an instance of PDO class and establishes a connection to the database. Replace the `username` and `password` with your database username and password.
<?php
class connection
{
public function dbConnect()
{
return new PDO("mysql:host=localhost; dbname=tutorial", "username","password");
}
}
?>Retrieve the data
Once connection to the database is established we need to retrieve the data from the ‘employee’ table. For selecting the data create a file named ‘table.php’.
Inside the 'table.php' file I have created a class named 'emp'. In this class inside the __construct function I have created a PDO object. When we create an object / instance of a class named 'emp' it also establish a connection to the database.
Then I have declared a function named 'employees' which creates the select statement and execute the queries. This function returns the retrieved data as an associative array.
This section include three main functions:
- Includes the connection.php file
- Inside the _construct() function create a new connection object
- Prepare and execute the SQL Statement and fetch all data using ‘fetchAll()’ method
Table.php
include_once 'connection.php';
class emp {
private $db;
public function __construct()
{
$this->db = new connection();
$this->db = $this->db->dbConnect();
}
public function employees() {
$st = $this->db->prepare("select * from employee ");
$st->execute();
$result = $st->fetchAll(PDO::FETCH_ASSOC);
return $result;
}
}Pass the PHP data to the Angular JS
In this section I have created two files 'display.php' and 'emp.js'.In the 'display.php' I have created an object of class named 'emp' to retrieve the results. We need to convert the retrieved results to the json format before passing those data to the AngularJS section. For converting our results to json format here I am using PHP bulit-in function json_encode() .
AngularJS $http is a core service for reading data from web servers. Here I am using $http.get(url) function to read server data.
Here we are getting the json encode data from ‘display.php’ using the $http.get(url) angular function. We need to assign the data to the scope variable ‘names’ after we retrieve data using the $http.get(url) .
Display.php
<?php include_once 'table.php'; $emp = new emp(); $result = $emp->employees(); echo json_encode($result); ?>
Emp.js
var app = angular.module('myApp', []);
app.controller('empCtrl', function($scope, $http) {
$scope.orderByField = 'empno';
$scope.reverseSort = false;
$http.get("display.php")
.success(function (response) {
$scope.names = response;
});
});Include AngularJS Library and Bootstrap
For using Angular JS in our porject we need to include the AngularJS Library. Here I am styling the table using the bootstrap so I include bootstrap and the AngularJS libraries in the <head> section.
Inculde Angular JS Library and Bootstrap
<script src= "http://ajax.googleapis.com/ajax/libs/angularjs/1.3.14/angular.min.js"></script>
<link href="//netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css" rel="stylesheet">Display Data using Angular JS in table format
AngularJS extends HTML with ng-directives. I have used the following 3 directives used to display this application:
- ng-app -> Defines an AngularJS application ( app name : ‘myApp’)
- ng-controller -> Defines the application controller ( ctrl name : ‘empCtrl’)
- ng-repeat -> used to display the repeating items
In our example I have created AngularJS application named 'myApp' and the controller named 'empCtrl'.The 'ng-repeat' directive is used to show the repeating table rows.
<div class="wrap">
<div ng-app="myApp" ng-controller="empCtrl">
<table class="table table-striped">
<tr>
<th>Empno</th>
<th>EmpName</th>
<th>Job</th>
<th>Hire Date</th>
<th>Salary</th>
<th >Comm</th>
<th>Department</th>
</tr>
<tr ng-repeat="x in names " >
<td>{{ x.empno }}</td>
<td>{{ x.ename }}</td>
<td>{{ x.job }}</td>
<td>{{ x.hiredate }}</td>
<td>{{ x.sal | currency :'£' }}</td>
<td>{{ x.comm | currency :'£' }}</td>
<td>{{ x.deptname }}</td>
</tr>
</table>
</div>
</div>
<script src="js/emp.js"></script>Now our table is ready. On checking your browser you will get table as shown in the figure. In the next series I will explain how to sort the table data , filter the table content and how to add pagination.Please check the link below and find my next tutorial.
Display table using AngularJS
Download Zip File
Tutorial Part 2
- How to filter table data,sort and capitalize table content and add pagination using Angular JS
In this series I will explain how to sort the table data by clicking the table header and filter the data using the Angular JS. I will also explain how to add pagination to the table.
© 2015 pearlbells
