Abstact
A well matched combination is now available for back and frontend with the advent of AngularJs when paired with PHP.
Complete seperation of frontend and backend is possible when PHP is used to create an API which produces JSON code which can readily be consumed by AngularJs.
This tutorial takes the user through the process of getting data from a database using PHP PDO and MySQL and converting the result set to JSON. The JSON code is then consumed by AngularJs which then iterates over the JSON to produce a HTML table.
Getting Started
For the purposes of this tutorial, a small table is created using MySQL. The table schema follows.
use application;
GRANT ALL ON application.* TO kevin@localhost IDENTIFIED BY 'my_password';
CREATE TABLE users(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
email TEXT NOT NULL
);
INSERT INTO users( id, name, email ) VALUES ( 1, 'Big Kev', 'bigkev@example.com' );
INSERT INTO users( id, name, email ) VALUES ( 2, 'Judy', 'punk@example.net' );
INSERT INTO users( id, name, email ) VALUES ( 3, 'Suzy', 'suzy@example.com' );
INSERT INTO users( id, name, email ) VALUES ( 4, 'Joey', 'sheena@example.org' );
INSERT INTO users( id, name, email ) VALUES ( 5, 'DeeD', 'deedee@example.net' );
With the database and users table in place, the job of getting the data onto a web page.
Getting Started
To fetch our data, the PHP PDO extension provides the best database interface known to mankind.
Create a file named api.php and use the code below for the contents
<?php
// set up the connection variables
$db_name = 'application';
$hostname = '127.0.0.1';
$username = 'kevin';
$password = 'my_password';
// connect to the database
$dbh = new PDO("mysql:host=$hostname;dbname=$db_name", $username, $password);
// a query get all the records from the users table
$sql = 'SELECT id, name, email FROM users';
// use prepared statements, even if not strictly required is good practice
$stmt = $dbh->prepare( $sql );
// execute the query
$stmt->execute();
// fetch the results into an array
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
// convert to json
$json = json_encode( $result );
// echo the json string
echo $json;
?>
The code above is a very basic script, which uses PDO to get fetch all the user records from the users table. Converting the results to a JSON array is a simple task of using the PHP json_encode function to do the work.
Now the the faux API is in place, the resulting JSON code can be consumed by any client. Here, AngularJS is used to consume and display the result set.
The HTML
Of course, for the data to diplayed, a web page is require to hold it. This very basic HTML file is what AngularJS uses to weave its magic.The iteration of the data happens with ng-repeat. ng-repeat iterates over the users JSON array, and outputs each of the properties in the table row, producing a new HTML table row for each user.
Create a file named index.html ( or whatever you like ) and user the below code for the contents.
<!doctype html>
<html ng-app>
<head>
<title>PHP MySQL API Consumed with AngularJS</title>
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.0.8/angular.min.js"></script>
<script src="./data.js"></script>
</head>
<body>
<div ng-controller="GetUsers">
<table>
<thead><tr><th>ID</th><th>Name</th><th>Email</th></tr></thead>
<tbody>
<tr ng-repeat="user in users"><td>{{user.id}}</td><td>{{ user.name }}</td><td>{{user.email}}</td></tr>
</tbody>
</tfoot></tfoot>
</table>
</div>
</body>
Consuming the JSON
This is where the magic happens. Create a file named data.js and put this contents in it.
function GetUsers($scope, $http) {
// this is where the JSON from api.php is consumed
$http.get('http://www.altitude.dev/client/api.php').
success(function(data) {
// here the data from the api is assigned to a variable named users
$scope.users = data;
});
}
Knowing what happens inside this file is important. The function is defined as GetUsers, which is the same name referenced from the HTML file with this line:
This binds the data model to the view (HTML).
Next, the $http.get is used to fetch the data from the api file, and then is assigned to variable named users in the $scope object. The binding to the view (HTML file) ensures that the data is available to the view.
Output
The resulting data looks like this:
| ID | Name | |
|---|---|---|
| 1 | Big Kev | bigkev@example.com |
| 2 | Judy | punk@example.net |
| 3 | Suzy | suzy@example.com |
| 4 | Joey | sheena@example.org |
| 5 | DeeD | deedee@example.net |
Epilogue
From the code above, the marriage of PHP and AngularJS to consume a JSON array produced from MySQL database is a new and exciting way to put together web application. No longer worrying about clumsy PHP frameworks to display data, as AngularJS takes over this task.