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.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)