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.
Created two table layouts –
Read POST data and executing operations according to the
I use a single PHP file to handle all
If you found this tutorial helpful then don't forget to share.
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.
1. Table structure
I am usingusers 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 newconfig.php file.
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
Defineng-app='myapp' and ng-controller='userCtrl' on <div>.Created two table layouts –
- First, contains 3 input element with
ng-modeldirective and a button withng-clickdirective. - On the second layout, define
ng-repeatdirective on<tr>to show the list ofusersusing AngularJS. Added Delete button following with user record and defineng-click="remove(index,userid.id)".
<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 aaddremove.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
userstable 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
userstable.
<?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
$httprequest from the controller to get all users records where passrequest_type: 1asdata. Initialize$scope.userswith response data. - On Add button click, pass entered input values as data in
$httppost request and setrequest_type: 2. Pushresponse.data[0]value in$scope.usersifresponse.datais not empty. - On delete button click, send
useridfrom$httpand setrequest_type: 3. Remove record from$scope.usersusingsplice()ifresponse.data == 1.
<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. Conclusion
In this tutorial, I showed how you can add or remove record from 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 request type.If you found this tutorial helpful then don't forget to share.
