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.

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 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.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

4 thoughts on “Add and remove record from MySQL Database with AngularJS”

Leave a Comment