ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

How to retrieve data from the MYSQL database in (OOP) PHP and display using Angular JS - Part 1

Updated on October 23, 2015

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

© 2015 pearlbells

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article