Inline Table Data Edit with AngularJS and PHP

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

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

Inline Table Data Edit with AngularJS and PHP


Contents

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

 


 

1. Table structure

Create users table.

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 user2");
  $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 = $data->field;
  $value = $data->value;
  $userid = $data->userid;

  $sql = "UPDATE user2 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 like this post then don’t forget to share.

Related Post

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  

4 Comments

  1. Brian said:

    It’s not updating the mysql table. I change values, but when i hit refresh on browser, the values revert back to the original.

    October 15, 2018
    Reply
  2. Brian Bradley said:

    This works great! Can you post a way to add/delete a record? So at the top of the page, there would be a blank record that you could type in the information, and then click a button to add record. And then a button to delete records.

    October 17, 2018
    Reply
    • Yogesh Singh said:

      Hi Brian,
      I already published the tutorial about this. You can view it here.

      October 18, 2018
      Reply

Leave a Reply

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