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.


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. 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.

Related Posts