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 return a response.

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

---

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 for the database connection.

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 (List) – Return all list of users in JSON format.
• request_type = 2 (Insert) – Check if the username already exists in the users table if not exists then insert a new record and return a JSON response.
• request_type = 3 (Delete)- Check if userid exists or not. If exists then delete a record from users 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){
  $stmt = $con->prepare("SELECT * FROM users");
  $stmt->execute();
  $result = $stmt->get_result();
  $data = array();
  if($result->num_rows > 0){
     while($row = $result->fetch_assoc()) {
            $data[] = array("id"=>$row['id'],"fname"=>$row['fname'],"lname"=>$row['lname'],"username"=>$row['username']); 
     }
  }
    
  $stmt->close();
  echo json_encode($data);
  exit;
}

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

  // Check username already exists
  $stmt = $con->prepare("SELECT * FROM users WHERE username=?");
  $stmt->bind_param('s',$username);
  $stmt->execute();
  $result = $stmt->get_result();
  $stmt->close();
  $return_arr = array();
  if($result->num_rows == 0){

    // Insert
    $insertSQL = "INSERT INTO users(fname,lname,username ) values(?,?,?)";
    $stmt = $con->prepare($insertSQL);
    $stmt->bind_param("sss",$fname,$lname,$username);
    $stmt->execute();

    $lastinsert_id = $stmt->insert_id;
    if($lastinsert_id > 0){
       $return_arr[] = array("id"=>$lastinsert_id,"fname"=>$fname,"lname"=>$lname,"username"=>$username);
    }
    $stmt->close();
  }
  echo json_encode($return_arr);
  exit;
}

// Delete record
if($request_type == 3){
  $userid = $data->userid;
  
  // Check userid exists
  $stmt = $con->prepare("SELECT * FROM users WHERE id=?");
  $stmt->bind_param('i',$userid);
  $stmt->execute();
  $result = $stmt->get_result();
  $stmt->close();

  if($result->num_rows > 0){

    // Delete
    $deleteSQL = "DELETE FROM users WHERE id=?";
    $stmt = $con->prepare($deleteSQL);
    $stmt->bind_param("i",$userid);
    $stmt->execute();
    $stmt->close();

    echo 1;
  }else{ 
    echo 0;
  }

  exit;
}

---

5. Script

• 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 if response.data is not empty.
• On delete button click, send userid from $http and set request_type: 3. Remove record from $scope.users using splice() if response.data == 1.

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) {
     if(response.data.length > 0)
        $scope.users.push(response.data[0]);
     else
        alert('Record not inserted.');
  });
 }

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

</script>

---

6. Demo

View Demo

---

7. Conclusion

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

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

If you found this tutorial helpful then don't forget to share.