Add and remove record from MySQL Database with AngularJS

If you know how to add or remove record to MySQL database table with jQuery AJAX then it’s been a lot easier for you to do with AngularJS.

Using PHP for handling requests and according to it execute the action and return response.

In the demonstration, I created a layout for entering new records and show the list of records in table layout with a delete button.

Add and remove record from MySQL Database with AngularJS


Contents

  1. Table structure
  2. Configuration
  3. HTML
  4. PHP
  5. Script
  6. Demo
  7. Conclusion

 


 

1. Table structure

I am using users table in the tutorial example.

CREATE TABLE `users` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `username` varchar(80) NOT NULL,
  `fname` varchar(80) NOT NULL,
  `lname` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

2. Configuration

Create a new config.php file.

Completed Code

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
 die("Connection failed: " . mysqli_connect_error());
}

 

3. HTML

Define ng-app='myapp' and ng-controller='userCtrl' on <div>.

Created two table layouts –

  1. First, contains 3 input element with ng-model directive and a button with ng-click directive.
  2. On the second layout, define ng-repeat directive on <tr> to show the list of users using AngularJS. Added Delete button following with user record and define ng-click="remove(index,userid.id)".

Completed Code

<div ng-app='myapp' ng-controller="userCtrl">
 <!-- New entry -->
 <table>
  <tr>
   <td>First Name</td>
   <td><input type='text' id='txt_fname' ng-model='fname'></td>
  </tr>
  <tr>
   <td>Last Name</td>
   <td><input type='text' id='txt_lname' ng-model='lname'></td>
  </tr>
  <tr>
   <td>Username</td>
   <td><input type='text' id='txt_uname' ng-model='uname'></td>
  </tr>
  <tr>
   <td>&nbsp;</td>
   <td><input type='button' id='but_save' value='Save' ng-click="add()" ></td>
  </tr>
 </table>

 <!-- User list -->
 <table border="1">
  <tr>
   <th>First name</th>
   <th>Last name</th>
   <th>Username</th>
   <th>&nbsp;</th>
  </tr>
 
  <tr ng-repeat="user in users">
   <td>{{user.fname}}</td>
   <td>{{user.lname}}</td>
   <td>{{user.username}}</td>
   <td><input type='button' ng-click='remove($index,user.id);' value='Delete'></td>
  </tr>
 
 </table>
</div>

 

4. PHP

Create a addremove.php file.

Read POST data and executing operations according to the $request_type value  –

  • request_type = 1 – Return all list of users in JSON format.
  • request_type = 2 – Add new record and return a JSON response.
  • request_type = 3 – Delete a user record from MySQL table.

Completed code

<?php

include 'config.php';

$data = json_decode(file_get_contents("php://input"));

$request_type = $data->request_type;

// Get all records
if($request_type == 1){
 $sel = mysqli_query($con,"select * from users");
 $data = array();

 while ($row = mysqli_fetch_array($sel)) {
  $data[] = array("id"=>$row['id'],"fname"=>$row['fname'],"lname"=>$row['lname'],"username"=>$row['username']); 
 }
 echo json_encode($data);
}

// Insert record
if($request_type == 2){
 $fname = $data->fname;
 $lname = $data->lname;
 $uname = $data->uname;

 mysqli_query($con,"insert into users(fname,lname,username) values('".$fname."','".$lname."','".$uname."')");
 $lastinsert_id = mysqli_insert_id($con);

 $return_arr[] = array("id"=>$lastinsert_id,"fname"=>$fname,"lname"=>$lname,"username"=>$uname);
 echo json_encode($return_arr);
}

// Delete record
if($request_type == 3){
 $userid = $data->userid;

 mysqli_query($con,"delete from users where id=".$userid);
 echo 1;
}

 

5. Script

  • Declare module variable
  • Send $http request from the controller to get all users records where pass request_type: 1 as data. Initialize $scope.users with response data.
  • On Add button click, pass entered input values as data in $http post request and set request_type: 2.  Push response.data[0] value in $scope.users.
  • On delete button click, send userid from $http and set request_type: 3. Remove record from $scope.users using splice().

Completed code

<script>
var fetch = angular.module('myapp', []);

fetch.controller('userCtrl', ['$scope', '$http', function ($scope, $http) {

 // Get all records
 $http({
  method: 'post',
  url: 'addremove.php',
  data: {request_type:1},

 }).then(function successCallback(response) {
  $scope.users = response.data;
 });

 // Add new record
 $scope.add = function(){
  $http({
   method: 'post',
   url: 'addremove.php',
   data: {fname:$scope.fname,lname:$scope.lname,uname:$scope.uname,request_type:2},
  }).then(function successCallback(response) {
   $scope.users.push(response.data[0]);
  });
 }

 // Remove record
 $scope.remove = function(index,userid){
 
  $http({
   method: 'post',
   url: 'addremove.php',
   data: {userid:userid,request_type:3},
  }).then(function successCallback(response) {
   $scope.users.splice(index, 1);
  }); 
 }
 
}]);

</script>

 

6. Demo

Enter values and click save button to insert the record. To remove a user click Delete button. Open in a new tab.


 

7. Conclusion

In this tutorial, I showed how you can add or remove record from MySQL database table with AngularJS and PHP.

I use single PHP file to handle all $http request for this define multiple if statement which executes according to request type.

Related Post

Spread the love

2 Comments

  1. Dhananjay said:

    please add getData.php page… unable to find this page

    October 22, 2017
    Reply
    • Yogesh Singh said:

      Hi Dhananjay,
      I have not used getData.php file in the tutorial.

      October 23, 2017
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *