Demo of a simple CRUD Restful php service used with Angularjs and Mysql

Facebook
Reddit
Twitter
Whatsapp
Cover image

REST represents Representational State Transfer. For web applications REST API is a good architectural choice for the communication between the app and the server. This makes sharing data between different devices and apps easy. Implementing REST is simpler compared to other methods like SOAP, CORBA, WSDL. In this tutorial we will create a RESTful web service using PHP. Also we will create a Customer Manager Application using AngularJS as front end which will consume this web service.

In this project we will have the most simple Create, Read, Update, Delete operations on customers table using REST API. Previously we learnt how to create a simple task manager application using AngularJS PHP and MySQL. This time we will do the same CRUD on database but in a more organised manner and using a RESTful web service using PHP, MySQL.

To run the customer manager web application in your local machine, follow the following 3 simple steps

  1. Download the required files
  2. Import angularcode_customer.sql
  3. Enable rewrite_module in apache server

At first lets start building the REST API using PHP, MySQL and then we will move on to the AngularJS part for the front end part of our Customer Manager Application.

1. Creating the database table for this project

You can import the provided sql file angularcode_customer.sql in the download. We need to keep the application database structure simple, so we only need one table named customer

CREATE DATABASE IF NOT EXISTS angularcode_customer;
USE angularcode_customer;
--
-- Table structure for table `customers`
--
CREATE TABLE IF NOT EXISTS `angularcode_customers` (
`customerNumber` int(11) NOT NULL AUTO_INCREMENT,
`customerName` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`address` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`state` varchar(50) DEFAULT NULL,
`postalCode` varchar(15) DEFAULT NULL,
`country` varchar(50) NOT NULL,
PRIMARY KEY (`customerNumber`)
);
--
-- Dumping data for table `customers`
--
INSERT INTO `angularcode_customers` (`customerNumber`, `customerName`, `email`, `address`, `city`, `state`, `postalCode`, `country`) VALUES
(103, 'Atelier graphique', 'Nantes@gmail.com', '54, rue Royale', 'Nantes', NULL, '44000', 'France'),
(112, 'Signal Gift Stores', 'LasVegas@gmail.com', '8489 Strong St.', 'Las Vegas', 'NV', '83030', 'USA'),
(114, 'Australian Collectors, Co.', 'Melbourne@gmail.com', '636 St Kilda Road', 'Melbourne', 'Victoria', '3004', 'Australia'),
(119, 'La Rochelle Gifts', 'Nantes@gmail.com', '67, rue des Cinquante Otages', 'Nantes', NULL, '44000', 'France'),
(121, 'Baane Mini Imports', 'Stavern@gmail.com', 'Erling Skakkes gate 78', 'Stavern', NULL, '4110', 'Norway'),
(124, 'Mini Gifts Distributors Ltd.', 'SanRafael@gmail.com', '5677 Strong St.', 'San Rafael', 'CA', '97562', 'USA'),
(125, 'Havel & Zbyszek Co', 'Warszawa@gmail.com', 'ul. Filtrowa 68', 'Warszawa', NULL, '01-012', 'Poland'),
(128, 'Blauer See Auto, Co.', 'Frankfurt@gmail.com', 'Lyonerstr. 34', 'Frankfurt', NULL, '60528', 'Germany'),
(129, 'Mini Wheels Co.', 'SanFrancisco@gmail.com', '5557 North Pendale Street', 'San Francisco', 'CA', '94217', 'USA'),
(131, 'Land of Toys Inc.', 'NYC@gmail.com', '897 Long Airport Avenue', 'NYC', 'NY', '10022', 'USA'),
(141, 'Euro+ Shopping Channel', 'Madrid@gmail.com', 'C/ Moralzarzal, 86', 'Madrid', NULL, '28034', 'Spain'),
(145, 'Danish Wholesale Imports', 'Kobenhavn@gmail.com', 'Vinbltet 34', 'Kobenhavn', NULL, '1734', 'Denmark'),
(146, 'Saveley & Henriot, Co.', 'Lyon@gmail.com', '2, rue du Commerce', 'Lyon', NULL, '69004', 'France'),
(148, 'Dragon Souveniers, Ltd.', 'Singapore@gmail.com', 'Bronz Sok.', 'Singapore', NULL, '079903', 'Singapore'),
(151, 'Muscle Machine Inc', 'NYC@gmail.com', '4092 Furth Circle', 'NYC', 'NY', '10022', 'USA'),
(157, 'Diecast Classics Inc.', 'Allentown@gmail.com', '7586 Pompton St.', 'Allentown', 'PA', '70267', 'USA'),
(161, 'Technics Stores Inc.', 'Burlingame@gmail.com', '9408 Furth Circle', 'Burlingame', 'CA', '94217', 'USA'),
(166, 'Handji Gifts& Co', 'Singapore@gmail.com', '106 Linden Road Sandown', 'Singapore', NULL, '069045', 'Singapore'),
(167, 'Herkku Gifts', 'Bergen@gmail.com', 'Brehmen St. 121', 'Bergen', NULL, 'N 5804', 'Norway '),
(168, 'American Souvenirs Inc', 'NewHaven@gmail.com', '149 Spinnaker Dr.', 'New Haven', 'CT', '97823', 'USA');

2. Build the REST API

Now lets fetch data for our database using the RESTful API which is created using PHP Langulage. The REST files are saved inside services folder, so that we can make RESTful calls using

  • $http.get
  • $http.post
  • $http.delete

In our application the path to the REST API is “/AngularCodeCustomerManagerApp/services/”. Now we can make RESTful calls like

  • $http.get('/AngularCodeCustomerManagerApp/services/customers') – Gets all the customers
  • $http.get('/AngularCodeCustomerManagerApp/services/customers/10') – Get the customer with id=10
  • $http.post('/AngularCodeCustomerManagerApp/services/insertCustomer', customer); – Insert a new customer
  • $http.delete('/AngularCodeCustomerManagerApp/services/deleteCustomer/10'); – Delete the customer with id=10

The services folder contains the .htaccess file for friendly URLs. To make this friendly URLs to work, we need to enable the rewrite module of apache.

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-d
RewriteCond %{REQUEST_FILENAME} !-s
RewriteRule ^(.*)$ api.php?x=$1 [QSA,NC,L]
RewriteCond %{REQUEST_FILENAME} -d
RewriteRule ^(.*)$ api.php [QSA,NC,L]
RewriteCond %{REQUEST_FILENAME} -s
RewriteRule ^(.*)$ api.php [QSA,NC,L]
</IfModule>

The api.php file creates those REST calls possible

<?php
require_once("Rest.inc.php");
class API extends REST {
public $data = "";
const DB_SERVER = "127.0.0.1";
const DB_USER = "root";
const DB_PASSWORD = "";
const DB = "angularcode_customer";
private $db = NULL;
private $mysqli = NULL;
public function __construct(){
parent::__construct(); // Init parent contructor
$this->dbConnect(); // Initiate Database connection
}
/*
* Connect to Database
*/
private function dbConnect(){
$this->mysqli = new mysqli(self::DB_SERVER, self::DB_USER, self::DB_PASSWORD, self::DB);
}
/*
* Dynmically call the method based on the query string
*/
public function processApi(){
$func = strtolower(trim(str_replace("/","",$_REQUEST['x'])));
if((int)method_exists($this,$func) > 0)
$this->$func();
else
$this->response('',404); // If the method not exist with in this class "Page not found".
}
private function login(){
if($this->get_request_method() != "POST"){
$this->response('',406);
}
$email = $this->_request['email'];
$password = $this->_request['pwd'];
if(!empty($email) and !empty($password)){
if(filter_var($email, FILTER_VALIDATE_EMAIL)){
$query="SELECT uid, name, email FROM users WHERE email = '$email' AND password = '".md5($password)."' LIMIT 1";
$r = $this->mysqli->query($query) or die($this->mysqli->error.__LINE__);
if($r->num_rows > 0) {
$result = $r->fetch_assoc();
// If success everythig is good send header as "OK" and user details
$this->response($this->json($result), 200);
}
$this->response('', 204); // If no records "No Content" status
}
}
$error = array('status' => "Failed", "msg" => "Invalid Email address or Password");
$this->response($this->json($error), 400);
}
private function customers(){
if($this->get_request_method() != "GET"){
$this->response('',406);
}
$query="SELECT distinct c.customerNumber, c.customerName, c.email, c.address, c.city, c.state, c.postalCode, c.country FROM angularcode_customers c order by c.customerNumber desc";
$r = $this->mysqli->query($query) or die($this->mysqli->error.__LINE__);
if($r->num_rows > 0){
$result = array();
while($row = $r->fetch_assoc()){
$result[] = $row;
}
$this->response($this->json($result), 200); // send user details
}
$this->response('',204); // If no records "No Content" status
}
private function customer(){
if($this->get_request_method() != "GET"){
$this->response('',406);
}
$id = (int)$this->_request['id'];
if($id > 0){
$query="SELECT distinct c.customerNumber, c.customerName, c.email, c.address, c.city, c.state, c.postalCode, c.country FROM angularcode_customers c where c.customerNumber=$id";
$r = $this->mysqli->query($query) or die($this->mysqli->error.__LINE__);
if($r->num_rows > 0) {
$result = $r->fetch_assoc();
$this->response($this->json($result), 200); // send user details
}
}
$this->response('',204); // If no records "No Content" status
}
private function insertCustomer(){
if($this->get_request_method() != "POST"){
$this->response('',406);
}
$customer = json_decode(file_get_contents("php://input"),true);
$column_names = array('customerName', 'email', 'city', 'address', 'country');
$keys = array_keys($customer);
$columns = '';
$values = '';
foreach($column_names as $desired_key){ // Check the customer received. If blank insert blank into the array.
if(!in_array($desired_key, $keys)) {
$$desired_key = '';
}else{
$$desired_key = $customer[$desired_key];
}
$columns = $columns.$desired_key.',';
$values = $values."'".$$desired_key."',";
}
$query = "INSERT INTO angularcode_customers(".trim($columns,',').") VALUES(".trim($values,',').")";
if(!empty($customer)){
$r = $this->mysqli->query($query) or die($this->mysqli->error.__LINE__);
$success = array('status' => "Success", "msg" => "Customer Created Successfully.", "data" => $customer);
$this->response($this->json($success),200);
}else
$this->response('',204); //"No Content" status
}
private function updateCustomer(){
if($this->get_request_method() != "POST"){
$this->response('',406);
}
$customer = json_decode(file_get_contents("php://input"),true);
$id = (int)$customer['id'];
$column_names = array('customerName', 'email', 'city', 'address', 'country');
$keys = array_keys($customer['customer']);
$columns = '';
$values = '';
foreach($column_names as $desired_key){ // Check the customer received. If key does not exist, insert blank into the array.
if(!in_array($desired_key, $keys)) {
$$desired_key = '';
}else{
$$desired_key = $customer['customer'][$desired_key];
}
$columns = $columns.$desired_key."='".$$desired_key."',";
}
$query = "UPDATE angularcode_customers SET ".trim($columns,',')." WHERE customerNumber=$id";
if(!empty($customer)){
$r = $this->mysqli->query($query) or die($this->mysqli->error.__LINE__);
$success = array('status' => "Success", "msg" => "Customer ".$id." Updated Successfully.", "data" => $customer);
$this->response($this->json($success),200);
}else
$this->response('',204); // "No Content" status
}
private function deleteCustomer(){
if($this->get_request_method() != "DELETE"){
$this->response('',406);
}
$id = (int)$this->_request['id'];
if($id > 0){
$query="DELETE FROM angularcode_customers WHERE customerNumber = $id";
$r = $this->mysqli->query($query) or die($this->mysqli->error.__LINE__);
$success = array('status' => "Success", "msg" => "Successfully deleted one record.");
$this->response($this->json($success),200);
}else
$this->response('',204); // If no records "No Content" status
}
/*
* Encode array into JSON
*/
private function json($data){
if(is_array($data)){
return json_encode($data);
}
}
}
// Initiiate Library
$api = new API;
$api->processApi();
?>

3. Make our web page angular ready

The main index.html file, which is the starting point of our application

<!DOCTYPE html>
<html ng-app="myApp" ng-app lang="en">
<head>
<meta charset="utf-8">
<link href="css/bootstrap.min.css" rel="stylesheet">
<style type="text/css">
ul>li, a{cursor: pointer;}
</style>
<title>Creating a Simple RESTful PHP web service which is consumed by a AngularJS application</title>
</head>
<body>
<div class="navbar navbar-default" id="navbar">
<div class="container" id="navbar-container">
<div class="navbar-header">
<a href="http://angularcode.com" class="navbar-brand">
<small>
<i class="glyphicon glyphicon-log-out"></i>
AngularCode / AngularJS Demos
</small>
</a><!-- /.brand -->
</div><!-- /.navbar-header -->
<div class="navbar-header pull-right" role="navigation">
<a href="http://angularcode.com/demo-of-a-simple-crud-restful-php-service-used-with-angularjs-and-mysql/" class="btn btn-sm btn-danger nav-button-margin"> <i class="glyphicon glyphicon-book"></i>&nbsp;Tutorial Link</a>
<a href="http://angularcode.com/download-link/?url=https://app.box.com/s/58ozc62qhypmhtaij33c" class="btn btn-sm btn-warning nav-button-margin"> <i class="glyphicon glyphicon-download"></i>&nbsp;Download Project</a>
</div>
</div>
</div>
<div>
<div class="container">
<br/>
<blockquote><h4><a href="http://angularcode.com/demo-of-a-simple-crud-restful-php-service-used-with-angularjs-and-mysql/">A simple demonstration of CRUD RESTful php service that can be used with Angularjs & mysql</a></h4></blockquote>
<br/>
<div ng-view="" id="ng-view"></div>
</div>
</div>
<script src="js/angular.min.js"></script>
<script src="js/angular-route.min.js"></script>
<script src="app/app.js"></script>
</body>
</html>

customer.html- This file lists all the customers

<div class="row">
<div class="col-md-12" ng-show="customers.length > 0">
<nav class= "navbar navbar-default" role= "navigation" >
<div class= "navbar-header" >
<a class="btn btn-lg btn-success" href="#/edit-customer/0"><i class="glyphicon glyphicon-plus"></i>&nbsp;Add new Customer</a>
</div>
</nav>
<table class="table table-striped table-bordered">
<thead>
<th>Customer Name&nbsp;</th>
<th>Email&nbsp;</th>
<th>Address&nbsp;</th>
<th>City&nbsp;</th>
<th>Country&nbsp;</th>
<th>Action&nbsp;</th>
</thead>
<tbody>
<tr ng-repeat="data in customers">
<td>{{data.customerName}}</td>
<td>{{data.email}}</td>
<td>{{data.address}}</td>
<td>{{data.city}}</td>
<td>{{data.country}}</td>
<td><a href="#/edit-customer/{{data.customerNumber}}" class="btn">&nbsp;<i class="glyphicon glyphicon-edit"></i>&nbsp; Edit Customer</a></td>
</tr>
</tbody>
</table>
</div>
<div class="col-md-12" ng-show="customers.length == 0">
<div class="col-md-12">
<h4>No customers found</h4>
</div>
</div>
</div>

2. edit-customer.html – This file is used to add, update, delete a customer

<style type="text/css">
.form-horizontal input.ng-invalid.ng-dirty {
border-color: #FA787E;
}
.form-horizontal input.ng-valid.ng-dirty {
border-color: #78FA89;
}
</style>
<div class="view">
<div class="container">
<div class="row">
<nav class= "navbar navbar-default" role= "navigation" >
<div class= "navbar-header" >
<a class= "navbar-brand" href= "#/customers"><i class="glyphicon glyphicon-th-large"></i> Customers List </a>
<a ng-show="customer._id" class= "navbar-brand" href= "#/edit-customer/0"><i class="glyphicon glyphicon-plus"></i> Create Customer </a>
<a ng-show="customer._id" class= "navbar-brand pull-right"><i class="glyphicon glyphicon-edit"></i> Currently Editing Customer Number: {{customer._id}}</a>
</div>
</nav>
<header>
<h3>{{title}}</h3>
</header>
<div class="col-md-12">
<form role="form" name="myForm" class="form-horizontal">
<div class="row">
<div class= "form-group" ng-class="{error: myForm.name.$invalid}">
<label class= "col-md-2"> Name </label>
<div class="col-md-4">
<input name="name" ng-model="customer.customerName" type= "text" class= "form-control" placeholder="Your name" required/>
<span ng-show="myForm.name.$dirty && myForm.name.$invalid" class="help-inline">Name Required</span>
</div>
</div>
<div class= "form-group">
<label class= "col-md-2"> Email address </label>
<div class="col-md-4">
<input name="email" ng-model="customer.email" type= "email" class= "form-control" placeholder="Enter email" required/>
<span ng-show="myForm.email.$dirty && myForm.email.$invalid && !myForm.email.$error.email" class="help-inline">Email Required</span>
<span ng-show="myForm.email.$error.email" class="help-inline">Email is not valid</span>
</div>
</div>
<div class= "form-group">
<label class= "col-md-2">Address </label>
<div class="col-md-4">
<input ng-model="customer.address" type= "text" class= "form-control" placeholder= "Present Address"/>
</div>
</div>
<div class= "form-group">
<label class= "col-md-2">City </label>
<div class="col-md-4">
<input ng-model="customer.city" type= "text" class= "form-control" placeholder= "Current City"/>
</div>
</div>
<div class= "form-group">
<label class= "col-md-2">Country </label>
<div class="col-md-4">
<input ng-model="customer.country" type= "text" class= "form-control" placeholder= "Residing Country"/>
</div>
</div>
<div class= "form-group">
<label class= "col-md-2"></label>
<div class="col-md-4">
<a href="#/" class="btn">Cancel</a>
<button ng-click="saveCustomer(customer);"
ng-disabled="isClean() || myForm.$invalid"
class="btn btn-primary">{{buttonText}}</button>
<button ng-click="deleteCustomer(customer)"
ng-show="customer._id" class="btn btn-warning">Delete</button>
</div>
</div>
</div>
</form>
</div></div>
</div>
</div>

4. AngularJS Code

The app.js file which contains all the client side angularjs code

var app = angular.module('myApp', ['ngRoute']);
app.factory("services", ['$http', function($http) {
var serviceBase = 'services/'
var obj = {};
obj.getCustomers = function(){
return $http.get(serviceBase + 'customers');
}
obj.getCustomer = function(customerID){
return $http.get(serviceBase + 'customer?id=' + customerID);
}
obj.insertCustomer = function (customer) {
return $http.post(serviceBase + 'insertCustomer', customer).then(function (results) {
return results;
});
};
obj.updateCustomer = function (id,customer) {
return $http.post(serviceBase + 'updateCustomer', {id:id, customer:customer}).then(function (status) {
return status.data;
});
};
obj.deleteCustomer = function (id) {
return $http.delete(serviceBase + 'deleteCustomer?id=' + id).then(function (status) {
return status.data;
});
};
return obj;
}]);
app.controller('listCtrl', function ($scope, services) {
services.getCustomers().then(function(data){
$scope.customers = data.data;
});
});
app.controller('editCtrl', function ($scope, $rootScope, $location, $routeParams, services, customer) {
var customerID = ($routeParams.customerID) ? parseInt($routeParams.customerID) : 0;
$rootScope.title = (customerID > 0) ? 'Edit Customer' : 'Add Customer';
$scope.buttonText = (customerID > 0) ? 'Update Customer' : 'Add New Customer';
var original = customer.data;
original._id = customerID;
$scope.customer = angular.copy(original);
$scope.customer._id = customerID;
$scope.isClean = function() {
return angular.equals(original, $scope.customer);
}
$scope.deleteCustomer = function(customer) {
$location.path('/');
if(confirm("Are you sure to delete customer number: "+$scope.customer._id)==true)
services.deleteCustomer(customer.customerNumber);
};
$scope.saveCustomer = function(customer) {
$location.path('/');
if (customerID <= 0) {
services.insertCustomer(customer);
}
else {
services.updateCustomer(customerID, customer);
}
};
});
app.config(['$routeProvider',
function($routeProvider) {
$routeProvider.
when('/', {
title: 'Customers',
templateUrl: 'partials/customers.html',
controller: 'listCtrl'
})
.when('/edit-customer/:customerID', {
title: 'Edit Customers',
templateUrl: 'partials/edit-customer.html',
controller: 'editCtrl',
resolve: {
customer: function(services, $route){
var customerID = $route.current.params.customerID;
return services.getCustomer(customerID);
}
}
})
.otherwise({
redirectTo: '/'
});
}]);
app.run(['$location', '$rootScope', function($location, $rootScope) {
$rootScope.$on('$routeChangeSuccess', function (event, current, previous) {
$rootScope.title = current.$$route.title;
});
}]);
Subscribe my updates via Email