AngularJS Insert Update Delete Using PHP MySQL

Posted by & filed under Ajax, AngularJS, CSS, HTML5, MYSQL, PHP, Twitter Bootstrap.

This is my first tutorial in AngularJS. In this tutorial we are going to see basic Insert Update Delete using AngularJS, PHP and MySQL. This tutorial has following steps

AngularJS Insert Update Delete Using PHP MySQL Steps:

  1.    HTML form designing using Bootstrap
  2.    Creating AngularJS module and controller.
  3.    Finally making making Ajax call using AngularJS $http service to do Insert Update Delete operation.
AngularJS Insert Update Delete Using PHP MySQL

Design HTML Form Using Bootstrap :

TO create responsive layout for this tutorial I am using Twitter Bootstrap. For this first create post directory in your server root folder (htdocs/www). Now create index.html file and add the following html basic markup in it.

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="utf-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<meta name="viewport" content="width=device-width, initial-scale=1">
	<title>AngularJS Insert Update Delete Using PHP MySQL</title>
	<!-- Bootstrap -->
	<link href='http://fonts.googleapis.com/css?family=Source+Sans+Pro:400,300,200' rel='stylesheet' type='text/css'>	
	<link href="css/bootstrap.min.css" rel="stylesheet">
	<link href="css/font-awesome.min.css" rel="stylesheet">
	<link href="css/animate.min.css" rel="stylesheet">
	<link href="css/style.css" rel="stylesheet">

	<!-- Script -->
	<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
	<script src="js/jquery.min.js"></script>
	<!-- Include all compiled plugins (below), or include individual files as needed -->
	<script src="js/bootstrap.min.js"></script>
	<script src="js/angular.min.js"></script>
	<script src="js/angular-custom.js"></script>
</head>
<body>
</body>
</html>

Now using Bootstrap Grid system I have split into two column. One for display HMTL form and other for to display saved details. Here is the HTML form markup.

<div class="container">
	<h2 class="title text-center">AngularJS Insert Update Delete Using PHP MySQL</h2>

	<div class="row mt80">
		<div class="col-xs-12 col-sm-4 col-md-4 col-lg-4 animated fadeInDown">
			<div class="alert alert-danger text-center alert-failure-div" role="alert" style="display: none">
				<p></p>
			</div>
			<div class="alert alert-success text-center alert-success-div" role="alert" style="display: none">
				<p></p>
			</div>
			<form novalidate name="userForm">
				<div class="form-group">
					<label for="name">Name</label> 
					<input type="text" class="form-control" id="name" name="name" placeholder="Name">
				</div>
				<div class="form-group">
					<label for="email">Email</label> 
					<input type="email" class="form-control" id="email" name="email" placeholder="Email">
				</div>
				<div class="form-group">
					<label for="company_name">Company Name</label> 
					<input type="text" class="form-control" id="company_name" name="company_name" placeholder="Company Name">
				</div>
				<div class="form-group">
					<label for="designation">Designation</label> 
					<input class="form-control" id="designation" name="designation" placeholder="Designation">
				</div>
				<div class="text-center">
					<button data-loading-text="Saving User..." type="submit" class="btn btn-save">Save User</button>
				</div>
			</form>
		</div>
		<div class="col-xs-12 col-sm-8 col-md-8 col-lg-8 animated fadeInUp">
			<div class="table-responsive">
				<table class="table table-bordered table-hover table-striped">
					<thead>
						<tr>
							<th width="5%">#</th>
							<th width="20%">Name</th>
							<th width="20%">Email</th>
							<th width="20%">Company Name</th>
							<th width="15%">Designation</th>
							<th width="20%">Action</th>
						</tr>
					</thead>
					<tbody>
						<tr>
							<th scope="row">1</th>
							<td>Muni</td>
							<td>support[at]smarttutorials.net</td>
							<td>SmartTutorials.net</td>
							<td>Director</td>
							<td><span> Edit</span> | <span>Delete</span></td>
						</tr>
					</tbody>
				</table>
			</div>
		</div>
	</div>
</div>

We have completed our designing part. Let move AngularJS part.

Create AngularJS Module:

AngularJS is MVC structured JavaScript framework. It let us do client side work effortlessly, also it reduces server load.

When we go for any framework, it has following advantages.
1. We try to follow that framework design pattern and coding structure.
2. We will write manageable code.
3. It help us to finish task so quickly.

In AngularJS everything is in the module. The module is like container it will have all codes we write for this particular AngularJS application. Then if our module requires some more functionality then we will inject particular module in our AngularJS application using dependency Injection. This avoids unnecessary script loading and also improves performance of the application.

Here is the syntax to create AngularJS module. I am creating ‘postModule’ for this application.

$postModule = angular.module('postModule', []);

To create module this AngularJS module method will requires following parameters.

  1. Name of the module (i.e. postModule)
  2. Array of dependency modules. Here this application doesn’t need extra modules, so we provide empty array.

Now apply that crated AngularJS application to HTML DOM using ng-app directive. You may apply this wherever you like to add in your HTML document. I am adding that module in the body tag like this.

<body data-ng-app="postModule">

When AngularJS compiler encounters this directive then it initialize AngularJS application.

Now Create AngularJS controller. Here is the syntax for creating controller.

$postModule.controller('PostController',function($scope, $http){
	//you properies and methods goes here
});

It requires name of the controller (i.e. PostController ), followed by anonymous function. By default all the AngularJS Controller will inject $scope service. All the Controller will pass the data from model to view via this $scope service. Here the Controller is like a linker between Model and view.

Here our whole AngularJS application script.

$postModule = angular.module('postModule', []);
var base_path = document.getElementById('base_path').value;
$postModule.controller('PostController',function($scope, $http){
	$scope.post = {};
	$scope.post.users = [];
	$scope.tempUser = {};
	$scope.editMode = false;
	$scope.index = '';
	
	var url = base_path+'ajax.php';
	
	$scope.saveUser = function(){
	    $http({
	      method: 'post',
	      url: url,
	      data: $.param({'user' : $scope.tempUser, 'type' : 'save_user' }),
	      headers: {'Content-Type': 'application/x-www-form-urlencoded'}
	    }).
	    success(function(data, status, headers, config) {
	    	if(data.success){
	    		if( $scope.editMode ){
	    			$scope.post.users[$scope.index].id = data.id;
	    			$scope.post.users[$scope.index].name = $scope.tempUser.name;
	    			$scope.post.users[$scope.index].email = $scope.tempUser.email;
	    			$scope.post.users[$scope.index].companyName = $scope.tempUser.companyName;
	    			$scope.post.users[$scope.index].designation = $scope.tempUser.designation;
	    		}else{
	    			$scope.post.users.push({
		    			id : data.id,
		    			name : $scope.tempUser.name,
		    			email : $scope.tempUser.email,
		    			companyName : $scope.tempUser.companyName,
		    			designation : $scope.tempUser.designation
		    		});
	    		}
	    		$scope.messageSuccess(data.message);
	    		$scope.userForm.$setPristine();
	    		$scope.tempUser = {};
	    		
	    	}else{
	    		$scope.messageFailure(data.message);
	    	}
	    }).
	    error(function(data, status, headers, config) {
	        //$scope.codeStatus = response || "Request failed";
	    });
	    
	    jQuery('.btn-save').button('reset');
	}
	
	$scope.addUser = function(){
		
		jQuery('.btn-save').button('loading');
		$scope.saveUser();
		$scope.editMode = false;
		$scope.index = '';
	}
	
	$scope.updateUser = function(){
		$('.btn-save').button('loading');
		$scope.saveUser();
	}
	
	$scope.editUser = function(user){
		$scope.tempUser = {
			id: user.id,
			name : user.name,
			email : user.email,
			companyName : user.companyName,
			designation : user.designation
		};
		$scope.editMode = true;
		$scope.index = $scope.post.users.indexOf(user);
	}
	
	
	$scope.deleteUser = function(user){
		var r = confirm("Are you sure want to delete this user!");
		if (r == true) {
			$http({
		      method: 'post',
		      url: url,
		      data: $.param({ 'id' : user.id, 'type' : 'delete_user' }),
		      headers: {'Content-Type': 'application/x-www-form-urlencoded'}
		    }).
		    success(function(data, status, headers, config) {
		    	if(data.success){
		    		var index = $scope.post.users.indexOf(user);
		    		$scope.post.users.splice(index, 1);
		    	}else{
		    		$scope.messageFailure(data.message);
		    	}
		    }).
		    error(function(data, status, headers, config) {
		    	//$scope.messageFailure(data.message);
		    });
		}
	}
	
	$scope.init = function(){
	    $http({
	      method: 'post',
	      url: url,
	      data: $.param({ 'type' : 'getUsers' }),
	      headers: {'Content-Type': 'application/x-www-form-urlencoded'}
	    }).
	    success(function(data, status, headers, config) {
	    	if(data.success && !angular.isUndefined(data.data) ){
	    		$scope.post.users = data.data;
	    	}else{
	    		$scope.messageFailure(data.message);
	    	}
	    }).
	    error(function(data, status, headers, config) {
	    	//$scope.messageFailure(data.message);
	    });
	}
	
	$scope.messageFailure = function (msg){
		jQuery('.alert-failure-div > p').html(msg);
		jQuery('.alert-failure-div').show();
		jQuery('.alert-failure-div').delay(5000).slideUp(function(){
			jQuery('.alert-failure-div > p').html('');
		});
	}
	
	$scope.messageSuccess = function (msg){
		jQuery('.alert-success-div > p').html(msg);
		jQuery('.alert-success-div').show();
		jQuery('.alert-success-div').delay(5000).slideUp(function(){
			jQuery('.alert-success-div > p').html('');
		});
	}
	
	
	$scope.getError = function(error, name){
		if(angular.isDefined(error)){
			if(error.required && name == 'name'){
				return "Please enter name";
			}else if(error.email && name == 'email'){
				return "Please enter valid email";
			}else if(error.required && name == 'company_name'){
				return "Please enter company name";
			}else if(error.required && name == 'designation'){
				return "Please enter designation";
			}else if(error.required && name == 'email'){
				return "Please enter email";
			}else if(error.minlength && name == 'name'){
				return "Name must be 3 characters long";
			}else if(error.minlength && name == 'company_name'){
				return "Company name must be 3 characters long";
			}else if(error.minlength && name == 'designation'){
				return "Designation must be 3 characters long";
			}
		}
	}
	
});

I will cover AngularJS form validation in different tutorial.

Create sample MySQL Database & Table

Use the following SQL queries to create sample table for this application.

CREATE TABLE IF NOT EXISTS `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) NOT NULL,
  `companyName` varchar(150) NOT NULL,
  `designation` varchar(150) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

ALTER TABLE `employee`
  ADD PRIMARY KEY (`id`);

PHP web service for AngularJS CRUD operation:

First create config.php file and define site wide constants in it.

<body data-ng<?php
/*
Site : http:www.smarttutorials.net
Author :muni
*/
//site specific configuration declartion
define( 'BASE_PATH', 'http://localhost/post/');
define('DB_HOST', 'localhost');
define('DB_NAME', 'post');
define('DB_USERNAME','root');
define('DB_PASSWORD','');


$mysqli  = new mysqli(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
	echo("Failed to connect, the error message is : ". mysqli_connect_error());
	exit();
}-app="postModule">

Now create ajax.php file that handles all the Ajax request from AngularJS for Insert, Read, Update and Delete operation. Here is the full PHP script that handles the CRUD operation.

<?php
/*
Site : http:www.smarttutorials.net
Author :muni
*/
require_once 'config.php';

if( isset($_POST['type']) && !empty( isset($_POST['type']) ) ){
	$type = $_POST['type'];
	
	switch ($type) {
		case "save_user":
			save_user($mysqli);
			break;
		case "delete_user":
			delete_user($mysqli, $_POST['id']);
			break;
		case "getUsers":
			getUsers($mysqli);
			break;
		default:
			invalidRequest();
	}
}else{
	invalidRequest();
}

/**
 * This function will handle user add, update functionality
 * @throws Exception
 */
function save_user($mysqli){
	try{
		$data = array();
		$name = $mysqli->real_escape_string(isset( $_POST['user']['name'] ) ? $_POST['user']['name'] : '');
		$companyName = $mysqli->real_escape_string(isset( $_POST['user']['companyName'] ) ? $_POST['user']['companyName'] : '');
		$designation = $mysqli->real_escape_string( isset( $_POST['user']['designation'] ) ? $_POST['user']['designation'] : '');
		$email = $mysqli->real_escape_string( isset( $_POST['user']['email'] ) ? $_POST['user']['email'] : '');
		$id = $mysqli->real_escape_string( isset( $_POST['user']['id'] ) ? $_POST['user']['id'] : '');
	
		if($name == '' || $companyName == '' || $designation == ''|| $email == '' ){
			throw new Exception( "Required fields missing, Please enter and submit" );
		}
	
	
		if(empty($id)){
			$query = "INSERT INTO employee (`id`, `name`, email, `companyName`, `designation`) VALUES (NULL, '$name', '$email', '$companyName', '$designation')";
		}else{
			$query = "UPDATE employee SET `name` = '$name', email = '$email', `companyName` = '$companyName', `designation` = '$designation' WHERE `employee`.`id` = $id";
		}
	
		if( $mysqli->query( $query ) ){
			$data['success'] = true;
			if(!empty($id))$data['message'] = 'User updated successfully.';
			else $data['message'] = 'User inserted successfully.';
			if(empty($id))$data['id'] = (int) $mysqli->insert_id;
			else $data['id'] = (int) $id;
		}else{
			throw new Exception( $mysqli->sqlstate.' - '. $mysqli->error );
		}
		$mysqli->close();
		echo json_encode($data);
		exit;
	}catch (Exception $e){
		$data = array();
		$data['success'] = false;
		$data['message'] = $e->getMessage();
		echo json_encode($data);
		exit;
	}
}

/**
 * This function will handle user deletion
 * @param string $id
 * @throws Exception
 */
function delete_user($mysqli, $id = ''){
	try{
		if(empty($id)) throw new Exception( "Invalid User." );
		$query = "DELETE FROM `employee` WHERE `id` = $id";
		if($mysqli->query( $query )){
			$data['success'] = true;
			$data['message'] = 'User deleted successfully.';
			echo json_encode($data);
			exit;
		}else{
			throw new Exception( $mysqli->sqlstate.' - '. $mysqli->error );
		}
		
	
	}catch (Exception $e){
		$data = array();
		$data['success'] = false;
		$data['message'] = $e->getMessage();
		echo json_encode($data);
		exit;
	}
}
	
/**
 * This function gets list of users from database
 */
function getUsers($mysqli){
	try{
	
		$query = "SELECT * FROM `employee` order by id desc limit 8";
		$result = $mysqli->query( $query );
		$data = array();
		while ($row = $result->fetch_assoc()) {
			$row['id'] = (int) $row['id'];
			$data['data'][] = $row;
		}
		$data['success'] = true;
		echo json_encode($data);exit;
	
	}catch (Exception $e){
		$data = array();
		$data['success'] = false;
		$data['message'] = $e->getMessage();
		echo json_encode($data);
		exit;
	}
}
	
	


function invalidRequest()
{
	$data = array();
	$data['success'] = false;
	$data['message'] = "Invalid request.";
	echo json_encode($data);
	exit;
}

Download Premium Only Scripts & 80+ Demo scripts Instantly at just 1.95 USD per month + 10% discount to all Exclusive Scripts

If you want any of my script need to be customized according to your business requirement,

Please feel free to contact me [at] muni2explore[at]gmail.com

Note: But it will be charged based on your customization requirement

Get Updates, Scripts & Other Useful Resources to your Email

Join 10,000+ Happy Subscribers on feedburner. Click to Subscribe (We don't send spam)
Every Email Subsciber could have access to download 100+ demo scripts & all future scripts.

%d bloggers like this:

Get Instant Script Download Access!