1. PHP
  2. Creating an AngularJS CRUD App with Search, Sort and Pagination with PHP/MySQLi

Creating an AngularJS CRUD App with Search, Sort and Pagination with PHP/MySQLi

Submitted by nurhodelta_17 on Saturday, March 27, 2021 - 16:57.
Language
PHP

This is a CRUD(Create, Read, Update and Delete) using Angular JS with the help of PHP and MySQLi to handle our database. Also includes how to filter/search data, sort data and paginate data from our MySQL Database. Angular JS is a javascript framework maintained by Google and is capable of creating Single-Paged Applications.

Getting Started

I've used CDN for Bootstrap, Font-awesome, and Angular JS so you need an internet connection for them to work.

Creating our Database

First, let's create our database and if you want, you can insert sample given data as well.

  1. Open PHPMyAdmin.
  2. Click databases, create a database and name it as angular.
  3. After creating a database, click the SQL and paste the below codes. See the image below for detailed instructions.
  1. CREATE TABLE `members` (
  2.   `memid` int(11) NOT NULL AUTO_INCREMENT,
  3.   `firstname` varchar(30) NOT NULL,
  4.   `lastname` varchar(30) NOT NULL,
  5.   `address` text NOT NULL,
  6. PRIMARY KEY(`memid`)

This is our sample data:

  1. INSERT INTO `members` (`memid`, `firstname`, `lastname`, `address`) VALUES
  2. (1, 'Neovic', 'Devierte', 'Silay City'),
  3. (2, 'Julyn', 'Divinagracia', 'E.B. Magalona'),
  4. (3, 'Gemalyn', 'Cepe', 'Bohol'),
  5. (4, 'Matet', 'Devierte', 'Silay City'),
  6. (5, 'Tintin', 'Devierte', 'Silay City'),
  7. (6, 'Bien', 'Devierte', 'Cebu City'),
  8. (7, 'Cherry', 'Ambayec', 'Cebu City'),
  9. (8, 'Jubilee', 'Limsiaco', 'Silay City'),
  10. (9, 'Janna ', 'Atienza', 'Talisay City'),
  11. (10, 'Desire', 'Osorio', 'Bacolod City'),
  12. (11, 'Debbie', 'Osorio', 'Talisay City'),
  13. (12, 'Nipoy ', 'Polondaya', 'Victorias City'),
  14. (13, 'Johnedel', 'Balino', 'Cauyan, Negros'),
  15. (14, 'Nereca', 'Tajonera', 'Cauayan, Negros'),
  16. (15, 'Jerome', 'Robles', 'Cebu City');
database sql

Creating our Connection

Next, we're gonna create our connection to our database which we are going to call every time we have business with the database. We name this as conn.php.

  1. <?php
  2.         $conn = new mysqli('localhost', 'root', '', 'angular');
  3.  
  4.         if ($conn->connect_error) {
  5.             die("Connection failed: " . $conn->connect_error);
  6.         }
  7.        
  8. ?>

index.php

Next, we're gonna create our index which contains our table.

  1. <!DOCTYPE html>
  2. <html lang="en" ng-app="app">
  3. <head>
  4.     <meta charset="utf-8">
  5.     <title>AngularJS CRUD with Search, Sort and Pagination</title>
  6.     <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
  7.     <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  8.     <script src="http://ajax.googleapis.com/ajax/libs/angularjs/1.4.8/angular.min.js"></script>
  9.     <link rel="stylesheet" type="text/css" href="style.css">
  10. </head>
  11. <body ng-controller="memberdata" ng-init="fetch()">
  12. <div class="container">
  13.     <h1 class="page-header text-center">AngularJS CRUD with Search, Sort and Pagination</h1>
  14.     <div class="row">
  15.                 <div class="col-md-8 col-md-offset-2">
  16.                         <div class="alert alert-success text-center" ng-show="success">
  17.                                 <button type="button" class="close" ng-click="clearMessage()"><span aria-hidden="true">&times;</span></button>
  18.                                 <i class="fa fa-check"></i> {{ successMessage }}
  19.                         </div>
  20.                         <div class="alert alert-danger text-center" ng-show="error">
  21.                                 <button type="button" class="close" ng-lick="clearMessage()"><span aria-hidden="true">&times;</span></button>
  22.                                 <i class="fa fa-warning"></i> {{ errorMessage }}
  23.                         </div>
  24.                         <div class="row">
  25.                                 <div class="col-md-12">
  26.                                         <button href="" class="btn btn-primary" ng-click="showAdd()"><i class="fa fa-plus"></i> New Member</button>
  27.                                         <span class="pull-right">
  28.                                                 <input type="text" ng-model="search" class="form-control" placeholder="Search">
  29.                                         </span>
  30.                                 </div>
  31.                         </div>
  32.                         <table class="table table-bordered table-striped" style="margin-top:10px;">
  33.                                 <thead>
  34.                     <tr>
  35.                         <th ng-click="sort('firstname')" class="text-center">First Name
  36.                                 <span class="pull-right">
  37.                                         <i class="fa fa-sort gray" ng-show="sortKey!='firstname'"></i>
  38.                                         <i class="fa fa-sort" ng-show="sortKey=='firstname'" ng-class="{'fa fa-sort-asc':reverse,'fa fa-sort-desc':!reverse}"></i>
  39.                                 </span>
  40.                         </th>
  41.                         <th ng-click="sort('lastname')" class="text-center">Last Name
  42.                                 <span class="pull-right">
  43.                                         <i class="fa fa-sort gray" ng-show="sortKey!='lastname'"></i>
  44.                                         <i class="fa fa-sort" ng-show="sortKey=='lastname'" ng-class="{'fa fa-sort-asc':reverse,'fa fa-sort-desc':!reverse}"></i>
  45.                                 </span>
  46.                         </th>
  47.                         <th ng-click="sort('address')" class="text-center">Address
  48.                                 <span class="pull-right">
  49.                                         <i class="fa fa-sort gray" ng-show="sortKey!='address'"></i>
  50.                                         <i class="fa fa-sort" ng-show="sortKey=='address'" ng-class="{'fa fa-sort-asc':reverse,'fa fa-sort-desc':!reverse}"></i>
  51.                                 </span>
  52.                         </th>
  53.                         <th class="text-center">Action</th>
  54.                     </tr>
  55.                 </thead>
  56.                                 <tbody>
  57.                                         <tr dir-paginate="member in members|orderBy:sortKey:reverse|filter:search|itemsPerPage:5">
  58.                                                 <td>{{ member.firstname }}</td>
  59.                                                 <td>{{ member.lastname }}</td>
  60.                                                 <td>{{ member.address }}</td>
  61.                                                 <td>
  62.                                                         <button type="button" class="btn btn-success" ng-click="showEdit(); selectMember(member);"><i class="fa fa-edit"></i> Edit</button>
  63.                                                         <button type="button" class="btn btn-danger" ng-click="showDelete(); selectMember(member);"> <i class="fa fa-trash"></i> Delete</button>
  64.                                                 </td>
  65.  
  66.                                         </tr>
  67.                                 </tbody>
  68.                         </table>
  69.                         <div class="pull-right" style="margin-top:-30px;">
  70.                                 <dir-pagination-controls
  71.                                    max-size="5"
  72.                                    direction-links="true"
  73.                                    boundary-links="true" >
  74.                                 </dir-pagination-controls>
  75.                         </div>
  76.                 </div>
  77.         </div>
  78.         <?php include('modal.php'); ?>
  79. </div>
  80. <script src="https://rawgit.com/michaelbromley/angularUtils-pagination/master/dirPagination.js"></script>
  81. <script src="angular.js"></script>
  82. </body>
  83. </html>

angular.js

This contains our angular JS codes.

  1. var app = angular.module('app', ['angularUtils.directives.dirPagination']);
  2. app.controller('memberdata',function($scope, $http, $window){
  3.         $scope.AddModal = false;
  4.     $scope.EditModal = false;
  5.     $scope.DeleteModal = false;
  6.  
  7.     $scope.errorFirstname = false;
  8.  
  9.     $scope.showAdd = function(){
  10.         $scope.firstname = null;
  11.         $scope.lastname = null;
  12.         $scope.address = null;
  13.         $scope.errorFirstname = false;
  14.         $scope.errorLastname = false;
  15.         $scope.errorAddress = false;
  16.         $scope.AddModal = true;
  17.     }
  18.  
  19.     $scope.fetch = function(){
  20.         $http.get("fetch.php").success(function(data){
  21.                 $scope.members = data;
  22.             });
  23.     }
  24.  
  25.     $scope.sort = function(keyname){
  26.         $scope.sortKey = keyname;  
  27.         $scope.reverse = !$scope.reverse;
  28.     }
  29.  
  30.     $scope.clearMessage = function(){
  31.         $scope.success = false;
  32.         $scope.error = false;
  33.     }
  34.  
  35.     $scope.addnew = function(){
  36.         $http.post(
  37.             "add.php", {
  38.                 'firstname': $scope.firstname,
  39.                 'lastname': $scope.lastname,
  40.                 'address':$scope.address,
  41.             }
  42.         ).success(function(data) {
  43.                 if(data.firstname){
  44.                         $scope.errorFirstname = true;
  45.                         $scope.errorLastname = false;
  46.                         $scope.errorAddress = false;
  47.                         $scope.errorMessage = data.message;
  48.                         $window.document.getElementById('firstname').focus();
  49.                 }
  50.                 else if(data.lastname){
  51.                         $scope.errorFirstname = false;
  52.                         $scope.errorLastname = true;
  53.                         $scope.errorAddress = false;
  54.                         $scope.errorMessage = data.message;
  55.                         $window.document.getElementById('lastname').focus();
  56.                 }
  57.                 else if(data.address){
  58.                         $scope.errorFirstname = false;
  59.                         $scope.errorLastname = false;
  60.                         $scope.errorAddress = true;
  61.                         $scope.errorMessage = data.message;
  62.                         $window.document.getElementById('address').focus();
  63.                 }
  64.                 else if(data.error){
  65.                         $scope.errorFirstname = false;
  66.                         $scope.errorLastname = false;
  67.                         $scope.errorAddress = false;
  68.                         $scope.error = true;
  69.                         $scope.errorMessage = data.message;
  70.                 }
  71.                 else{
  72.                         $scope.AddModal = false;
  73.                         $scope.success = true;
  74.                         $scope.successMessage = data.message;
  75.                         $scope.fetch();
  76.                 }
  77.         });
  78.     }
  79.  
  80.     $scope.selectMember = function(member){
  81.         $scope.clickMember = member;
  82.     }
  83.  
  84.     $scope.showEdit = function(){
  85.         $scope.EditModal = true;
  86.     }
  87.  
  88.     $scope.updateMember = function(){
  89.         $http.post("edit.php", $scope.clickMember)
  90.         .success(function(data) {
  91.                 if(data.error){
  92.                         $scope.error = true;
  93.                         $scope.errorMessage = data.message;
  94.                         $scope.fetch();
  95.                 }
  96.                 else{
  97.                         $scope.success = true;
  98.                         $scope.successMessage = data.message;
  99.                 }
  100.         });
  101.     }
  102.  
  103.     $scope.showDelete = function(){
  104.         $scope.DeleteModal = true;
  105.     }
  106.  
  107.     $scope.deleteMember = function(){
  108.         $http.post("delete.php", $scope.clickMember)
  109.         .success(function(data) {
  110.                 if(data.error){
  111.                         $scope.error = true;
  112.                         $scope.errorMessage = data.message;
  113.                 }
  114.                 else{
  115.                         $scope.success = true;
  116.                         $scope.successMessage = data.message;
  117.                         $scope.fetch();
  118.                 }
  119.         });
  120.     }
  121.  
  122. });

fetch.php

This is our PHP code/api that fetches data from our MySQL Table.

  1. <?php
  2.         include('conn.php');
  3.        
  4.         $output = array();
  5.         $sql = "SELECT * FROM members";
  6.         $query=$conn->query($sql);
  7.         while($row=$query->fetch_array()){
  8.                 $output[] = $row;
  9.         }
  10.  
  11.         echo json_encode($output);
  12. ?>

modal.php

This contains our customize modal for inserting, editing, and deleting data in our MySQL Table.

  1. <!-- Add Modal -->
  2. <div class="myModal" ng-show="AddModal">
  3.         <div class="modalContainer">
  4.                 <div class="modalHeader">
  5.                         <span class="headerTitle">Add New Member</span>
  6.                         <button class="closeBtn pull-right" ng-click="AddModal = false">&times;</button>
  7.                 </div>
  8.                 <div class="modalBody">
  9.                         <div class="form-group">
  10.                                 <label>Firstname:</label>
  11.                                 <input type="text" class="form-control" ng-model="firstname" id="firstname">
  12.                                 <span class="pull-right input-error" ng-show="errorFirstname">{{ errorMessage }}</span>
  13.                         </div>
  14.                         <div class="form-group">
  15.                                 <label>Lastname:</label>
  16.                                 <input type="text" class="form-control" ng-model="lastname" id="lastname">
  17.                                 <span class="pull-right input-error" ng-show="errorLastname">{{ errorMessage }}</span>
  18.                         </div>
  19.                         <div class="form-group">
  20.                                 <label>Address:</label>
  21.                                 <input type="text" class="form-control" ng-model="address" id="address">
  22.                                 <span class="pull-right input-error" ng-show="errorAddress">{{ errorMessage }}</span>
  23.                         </div>
  24.                 </div>
  25.                 <hr>
  26.                 <div class="modalFooter">
  27.                         <div class="footerBtn pull-right">
  28.                                 <button class="btn btn-default" ng-click="AddModal = false"><span class="glyphicon glyphicon-remove"></span> Cancel</button> <button class="btn btn-primary" ng-click="addnew()"><span class="glyphicon glyphicon-floppy-disk"></span> Save</button>
  29.                         </div>
  30.                 </div>
  31.         </div>
  32. </div>
  33.  
  34. <!-- Edit Modal -->
  35. <div class="myModal" ng-show="EditModal">
  36.         <div class="modalContainer">
  37.                 <div class="editHeader">
  38.                         <span class="headerTitle">Edit Member</span>
  39.                         <button class="closeEditBtn pull-right" ng-click="EditModal = false">&times;</button>
  40.                 </div>
  41.                 <div class="modalBody">
  42.                         <div class="form-group">
  43.                                 <label>Firstname:</label>
  44.                                 <input type="text" class="form-control" ng-model="clickMember.firstname">
  45.                         </div>
  46.                         <div class="form-group">
  47.                                 <label>Lastname:</label>
  48.                                 <input type="text" class="form-control" ng-model="clickMember.lastname">
  49.                         </div>
  50.                         <div class="form-group">
  51.                                 <label>Address:</label>
  52.                                 <input type="text" class="form-control" ng-model="clickMember.address">
  53.                         </div>
  54.                 </div>
  55.                 <hr>
  56.                 <div class="modalFooter">
  57.                         <div class="footerBtn pull-right">
  58.                                 <button class="btn btn-default" ng-click="EditModal = false"><span class="glyphicon glyphicon-remove"></span> Cancel</button> <button class="btn btn-success" ng-click="EditModal = false; updateMember();"><span class="glyphicon glyphicon-check"></span> Save</button>
  59.                         </div>
  60.                 </div>
  61.         </div>
  62. </div>
  63.  
  64. <!-- Delete Modal -->
  65. <div class="myModal" ng-show="DeleteModal">
  66.         <div class="modalContainer">
  67.                 <div class="deleteHeader">
  68.                         <span class="headerTitle">Delete Member</span>
  69.                         <button class="closeDelBtn pull-right" ng-click="DeleteModal = false">&times;</button>
  70.                 </div>
  71.                 <div class="modalBody">
  72.                         <h5 class="text-center">Are you sure you want to delete Member</h5>
  73.                         <h2 class="text-center">{{clickMember.firstname}} {{clickMember.lastname}}</h2>
  74.                 </div>
  75.                 <hr>
  76.                 <div class="modalFooter">
  77.                         <div class="footerBtn pull-right">
  78.                                 <button class="btn btn-default" ng-click="DeleteModal = false"><span class="glyphicon glyphicon-remove"></span> Cancel</button> <button class="btn btn-danger" ng-click="DeleteModal = false; deleteMember(); "><span class="glyphicon glyphicon-trash"></span> Yes</button>
  79.                         </div>
  80.                 </div>
  81.         </div>
  82. </div>

style.css

This contains our custom CSS, especially for our custom modal.

  1. .gray{
  2.         color:gray;
  3. }
  4.  
  5. .input-error{
  6.         font-size:12px;
  7.         color:#f44336;
  8. }
  9.  
  10. .myModal{
  11.         position:fixed;
  12.         top:0;
  13.         left:0;
  14.         right:0;
  15.         bottom:0;
  16.         background: rgba(0, 0, 0, 0.4);
  17.         z-index:100;
  18. }
  19.  
  20. .modalContainer{
  21.         width: 555px;
  22.         background: #FFFFFF;
  23.         margin:auto;
  24.         margin-top:10px;
  25. }
  26.  
  27. .modalHeader{
  28.         padding:10px;
  29.         background: #008CBA;
  30.         color: #FFFFFF;
  31.         height:50px;
  32.         font-size:20px;
  33.         padding-left:15px;
  34. }
  35.  
  36. .editHeader{
  37.         padding:10px;
  38.         background: #4CAF50;
  39.         color: #FFFFFF;
  40.         height:50px;
  41.         font-size:20px;
  42.         padding-left:15px;
  43. }
  44.  
  45. .deleteHeader{
  46.         padding:10px;
  47.         background: #f44336;
  48.         color: #FFFFFF;
  49.         height:50px;
  50.         font-size:20px;
  51.         padding-left:15px;
  52. }
  53.  
  54. .modalBody{
  55.         padding:30px;
  56. }
  57.  
  58. .modalFooter{
  59.         height:36px;
  60. }
  61.  
  62. .footerBtn{
  63.         margin-right:10px;
  64.         margin-top:-9px;
  65. }
  66.  
  67. .closeBtn{
  68.         background: #008CBA;
  69.         color: #FFFFFF;
  70.         border:none;
  71. }
  72.  
  73. .closeEditBtn{
  74.         background: #4CAF50;
  75.         color: #FFFFFF;
  76.         border:none;
  77. }
  78.  
  79. .closeDelBtn{
  80.         background: #f44336;
  81.         color: #FFFFFF;
  82.         border:none;
  83. }

add.php

This is our PHP code/api in inserting data into our database.

  1. <?php
  2.     include('conn.php');
  3.     $data = json_decode(file_get_contents("php://input"));
  4.  
  5.     $out = array('error' => false, 'firstname' => false, 'lastname' => false, 'address' => false);
  6.  
  7.     $firstname = $data->firstname;
  8.     $lastname = $data->lastname;
  9.     $address = $data->address;
  10.  
  11.     if(empty($firstname)){
  12.         $out['firstname'] = true;
  13.         $out['message'] = 'Firstname is required';
  14.     }
  15.     elseif(empty($lastname)){
  16.         $out['lastname'] = true;
  17.         $out['message'] = 'Lastname is required';
  18.     }
  19.     elseif(empty($address)){
  20.         $out['address'] = true;
  21.         $out['message'] = 'Address is required';
  22.     }
  23.     else{
  24.         $sql = "INSERT INTO members (firstname, lastname, address) VALUES ('$firstname', '$lastname', '$address')";
  25.         $query = $conn->query($sql);
  26.  
  27.         if($query){
  28.             $out['message'] = 'Member Added Successfully';
  29.         }
  30.         else{
  31.             $out['error'] = true;
  32.             $out['message'] = 'Cannot Add Member';
  33.         }
  34.     }
  35.        
  36.     echo json_encode($out);
  37. ?>

edit.php

This is our PHP code/api is updating our MySQL Table row.

  1. <?php
  2.         include('conn.php');
  3.     $data = json_decode(file_get_contents("php://input"));
  4.  
  5.     $out = array('error' => false);
  6.  
  7.     $firstname = $data->firstname;
  8.     $lastname = $data->lastname;
  9.     $address = $data->address;
  10.     $memid = $data->memid;
  11.  
  12.         $sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE memid = '$memid'";
  13.         $query = $conn->query($sql);
  14.  
  15.         if($query){
  16.                 $out['message'] = 'Member updated Successfully';
  17.         }
  18.         else{
  19.                 $out['error'] = true;
  20.                 $out['message'] = 'Cannot update Member';
  21.         }
  22.  
  23.     echo json_encode($out);
  24. ?>

delete.php

Lastly, this is our PHP code/API in deleting the MySQL Table row.

  1. <?php
  2.         include('conn.php');
  3.     $data = json_decode(file_get_contents("php://input"));
  4.  
  5.     $out = array('error' => false);
  6.  
  7.     $memid = $data->memid;
  8.  
  9.         $sql = "DELETE FROM members WHERE memid = '$memid'";
  10.         $query = $conn->query($sql);
  11.  
  12.         if($query){
  13.                 $out['message'] = 'Member deleted Successfully';
  14.         }
  15.         else{
  16.                 $out['error'] = true;
  17.                 $out['message'] = 'Cannot delete Member';
  18.         }
  19.  
  20.     echo json_encode($out);
  21. ?>
DEMO

That's the end of our tutorial. I hope this Angular JS CRUD APP with Search, Sort, and Pagination With PHP/MySQLi tutorial will help you with what you are looking for, and hopefully, this will add up to your knowledge of developing an Angular JS App.

Explore more on this website for more tutorials and free source code.

Enjoy :)

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Tags
php
MySQLi
angular js
CRUD
Search
sort
PAGINATION
MYSQL

Comments

Submitted by Anonymous (not verified) on Wed, 11/14/2018 - 16:37

When i edit a data and after editing i press cancel button the edited data showed on the table

Add new comment

About text formats