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
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 –
- First, contains 3 input element with
ng-model
directive and a button withng-click
directive. - On the second layout, define
ng-repeat
directive on<tr>
to show the list ofusers
using AngularJS. Added Delete button following with user record and defineng-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> </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> </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 passrequest_type: 1
asdata
. Initialize$scope.users
with response data. - On Add button click, pass entered input values as data in
$http
post request and setrequest_type: 2
. Pushresponse.data[0]
value in$scope.users
ifresponse.data
is not empty. - On delete button click, send
userid
from$http
and setrequest_type: 3
. Remove record from$scope.users
usingsplice()
ifresponse.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
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.