Inline Table Data Edit with AngularJS and PHP

Live data edit on the table makes it easier to update the records while viewing.

For editing values on the table, I am using a textbox element. When data is modified in the element then update records in the MySQL database table using Angular and PHP.

Download

Table structure
Configuration
HTML
PHP
Script
Demo
Conclusion

1. Table structure

I am using users table in the example and added some records.

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

2. Configuration

Create a config.php for 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

Loop on users object using ng-repeat directive and display records.

For data edit creates input elements and define ng-model directive which use to select the value in the controller.

To update the record define ng-keyup directive which calls updateDetail() method. In updateDetail() method passes user object and name of the field.

Completed Code

<!doctype html>
<html>
 <head>
   <script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.7/angular.min.js"></script>
 </head>
 <body ng-app='myapp'>

   <div ng-controller="myCtrl">
 
     <table border='1' style='border-collapse: collapse;'>
       <tr>
         <th>Username</th>
         <th>First Name</th>
         <th>Last Name</th>
         <th>Age</th>
         <th>Email</th>
      </tr>
      <tr ng-repeat='user in users'>
        <td>{{ user.username }}<input type='hidden' ng-model='user.id'></td>
        <td>
          <input type='text' ng-keyup='updateDetail(user,"fname")' ng-model='user.fname' >
        </td>
        <td>
          <input type='text' ng-model='user.lname' ng-keyup='updateDetail(user,"lname")' >
        </td>
        <td>
          <input type='text' ng-model='user.age' ng-keyup='updateDetail(user,"age")' >
        </td>
        <td>
          <input type='text' ng-model='user.email' ng-keyup='updateDetail(user,"email")' >
        </td>
      </tr>
    </table>
 
   </div>

 </body>
</html>

4. PHP

Create a ajaxfile.php file.

From this file handle two requests –

If $request == 1 – Select all records from users table and initialize an Array. Encode Array in JSON format for return.
If $request == 2 – Store passed userid, field, and value. Set update query and execute it.

Completed Code

<?php

include 'config.php';

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

$request = $data->request;

// Fetch users
if($request == 1){
  $sel = mysqli_query($con,"select * from users");
  $data = array();

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

// Update record
if($request == 2){
  $field = mysqli_real_escape_string($con,$data->field);
  $value = mysqli_real_escape_string($con,$data->value);
  $userid = mysqli_real_escape_string($con,$data->userid);

  $sql = "UPDATE users set ".$field."='".$value."' WHERE id=".$userid;
  mysqli_query($con,$sql);
 
  echo "Update successfully";
}
exit;

5. Script

In the controller define two methods –

fetchUsers() – Send $http service request where pass request: 1 as data. On successfully callback assign response.data in $scope.users object.

updateDetail() – This method takes two parameters – an object and string value. Get userid using user object and using field variable to get pressed element value.

Execute $http service request where pass field, value, userid, and request: 2 as data.

Completed Code

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

fetch.controller('myCtrl', ['$scope', '$http', function ($scope, $http) {
 
  // Get all users list
  $scope.fetchUsers = function(){
 
    $http({
      method: 'post',
      url: 'ajaxfile.php',
      data: {request: 1}
    }).then(function successCallback(response) {
        $scope.users = response.data;
    });
  }

  // Call fetchUsers() method
  $scope.fetchUsers();

  // Set value to search box
  $scope.updateDetail = function(user,field){
    var userid = user.id;
 
    var value = "";
    switch(field){
       case 'fname': value = user.fname;
       break;
       case 'lname': value = user.lname;
       break;
       case 'age': value = user.age;
       break;
       case 'email': value = user.email;
       break;
    }
 
    $http({
       method: 'post',
       url: 'ajaxfile.php',
       data: {field: field,value: value,userid: userid,request: 2}
    }).then(function successCallback(response) {
       console.log('Update successfully');
    }); 
  }
 
}]);

6. Demo

7. Conclusion

I update the record in the MySQL table when data edit in the textbox element. You can also use a button to save more than one records on the single click.

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

Contents

1. Table structure

2. Configuration

3. HTML

4. PHP

5. Script

6. Demo

7. Conclusion