Description
AngularJS SQL is one of the best technology used to display data from the Database. But confirm the data is in JSON format.
Below is an example that shows how to perform CRUD operations in MySQL Database using AngularJS SQL , Bootstrap and PHP.
Example
Step-1 : Create responsive HTML form
index.html with the help of Bootstrap. Now with the help of Bootstrap Grid system, divide the body into two columns. One to show HMTL form and other to save details. Below is the code.
[html]
<!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>
<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>John</td>
<td>support[at]splessons.com</td>
<td>SPLessons.com</td>
<td>Director</td>
<td><span> Edit</span> | <span>Delete</span></td>
</tr>
</tbody>
</table>
</div>
</div>
</div>
</div>
</body>
</html>
[/html]
Step-2 : Then create a controller "PostController" with module called "postModule" for the application.
[html]
$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";
}
}
}
});
[/html]
Step-3 : Next create MySQL Database with the Table named "employee".
[html]
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`);
[/html]
Step-4 : The PHP file config.php is created and following code with wide constants are entered into it.
[php]
<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">
[/php]
Step-4 : Then create the file ajax.php which performs the operations Insert, Read, Update and Delete in AngularJS by tackling all the Ajax requests occuring.
[php]
<?php
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;
}
[/php]
Key Points
- AngularJS SQL Database data will be in JSON format.
- CRUD operations Insert, Update, Read and Delete operations in Database are performed with AngularJS SQL.