Angularjs CRUD with PHP and mysql
Aug 30, 2016 by Pankaj Kumar
Hey guys, Today we are going to explain to work with angular using php & mysql at backend. So in this demo we will create CRUD with book data.
So lets proceed in this task step by step:
Database setup
CREATE TABLE `books` ( `id` int(11) NOT NULL AUTO_INCREMENT, `book_id` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `book_name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `author` varchar(15) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Now lets discuss to next step, On front part,index.html:
<div ng-app="AngularPHPcrudApp" class="container" ng-controller="bookController" ng-init="getBooks()">
<div class="row">
<h2>AngularJS CRUD Using PHP & MYSQL</h2>
<div class="panel panel-default">
<div class="panel-heading">Books List <a href="javascript:void(0);" class="glyphicon glyphicon-plus" onClick="$('.formData').slideToggle();">Add</a></div>
<div class="panel-body none formData">
<form class="form col-md-3" name="bookForm">
<div class="form-group">
<label>Book Id</label>
<input type="text" class="form-control" name="book_id" ng-model="bookData.book_id"/>
</div>
<div class="form-group">
<label>Book Name</label>
<input type="text" class="form-control" name="book_name" ng-model="bookData.book_name"/>
</div>
<div class="form-group">
<label>Author</label>
<input type="text" class="form-control" name="author" ng-model="bookData.author"/>
</div>
<a href="javascript:void(0);" class="btn btn-default" onClick="$('.formData').slideUp();">Reset</a>
<a href="javascript:void(0);" class="btn btn-default" ng-hide="bookData.id" ng-click="addBook()">Submit</a>
<a href="javascript:void(0);" class="btn btn-default" ng-hide="!bookData.id" ng-click="updateBook()">Update</a>
</form>
</div>
<table class="table">
<tr>
<th width="5%">S. No</th>
<th width="20%">Book Id</th>
<th width="20%">Book Name</th>
<th width="30%">Author</th>
<th width="10%">Settings</th>
</tr>
<tr ng-repeat="book in books | orderBy:'-created'">
<td>{{$index + 1}}</td>
<td>{{book.book_id}}</td>
<td>{{book.book_name}}</td>
<td>{{book.author}}</td>
<td>
<a href="javascript:void(0);" ng-click="editBook(book)">update</a>
<a href="javascript:void(0);" ng-click="deleteBook(book)">delete</a>
</td>
</tr>
</table>
</div>
</div>
</div>
In the above code, We are performing task of view part, like book listing/add book & edit book. And also set the angular app with name "AngularPHPcrudApp" and controller 'bookController'. Now from the above html content request goes to angular part..\
So in the next step lets understand the angularjs role in this demo.
<script>
angular.module("AngularPHPcrudApp", []).controller("bookController", function($scope,$http){
$scope.books = [];
$scope.bookData = {};
// function to get records from the database
$scope.getBooks = function(){
$http.get('action.php', {
params:{
'type':'view'
}
}).success(function(response){
if(response.status == 'OK'){
$scope.books = response.records;
}
});
};
// function to insert or update book data to the database
$scope.saveBook = function(type){
var data = $.param({
'data':$scope.bookData,
'type':type
});
var config = {
headers : {
'Content-Type': 'application/x-www-form-urlencoded;charset=utf-8;'
}
};
$http.post("action.php", data, config).success(function(response){
if(response.status == 'OK'){
if(type == 'edit'){
$scope.books[$scope.index].id = $scope.bookData.id;
$scope.books[$scope.index].book_name = $scope.bookData.book_name;
$scope.books[$scope.index].book_id = $scope.bookData.book_id;
$scope.books[$scope.index].author = $scope.bookData.author;
$scope.books[$scope.index].created = $scope.bookData.created;
}else{
$scope.books.push({
id:response.data.id,
book_name:response.data.book_name,
author:response.data.author,
book_id:response.data.book_id
});
}
$scope.bookForm.$setPristine();
$scope.tempBookData = {};
$('.formData').slideUp();
alert(response.msg);
}else{
alert(response.msg);
}
});
};
// function to add book data
$scope.addBook = function(){
$scope.saveBook('add');
};
// function to edit book data
$scope.editBook = function(books){
$scope.bookData = {
id:books.id,
book_name:books.book_name,
book_id:books.book_id,
author:books.author,
created:books.created
};
$scope.index = $scope.books.indexOf(books);
$('.formData').slideDown();
};
// function to update book data
$scope.updateBook = function(){
$scope.saveBook('edit');
};
// function to delete book data from the database
$scope.deleteBook = function(book){
var conf = confirm('Are you sure to delete the book?');
if(conf === true){
var data = $.param({
'id': book.id,
'type':'delete'
});
var config = {
headers : {
'Content-Type': 'application/x-www-form-urlencoded;charset=utf-8;'
}
};
$http.post("action.php",data,config).success(function(response){
if(response.status == 'OK'){
var index = $scope.books.indexOf(book);
$scope.books.splice(index,1);
alert(response.msg);
}else{
alert(response.msg);
}
});
}
};
});
</script>
So in the head section of our index.html, we have defined the angular related task for CRUD. So you can see in the above code we have defined separate method for add book, edit book & delete book. For detailed study in angular please read the angularjs tutorials.
Now let's move to the next part of our demo, the backend part or the task related to php/mysql(action.php).
include 'connect.php';
$db = new DATABASE();
$tblName = 'books';
if(isset($_REQUEST['type']) && !empty($_REQUEST['type'])){
$type = $_REQUEST['type'];
switch($type){
case "view":
$records = $db->getRows($tblName);
if($records){
$resp['records'] = $db->getRows($tblName);
$resp['status'] = 'OK';
}else{
$resp['records'] = array();
$resp['status'] = 'ERR';
}
echo json_encode($resp);
break;
case "add":
if(!empty($_POST['data'])){
$userData = array(
'book_id' => $_POST['data']['book_id'],
'book_name' => $_POST['data']['book_name'],
'author' => $_POST['data']['author']
);
$book_id = $_POST['data']['book_id'] ? $_POST['data']['book_id'] : '';
$book_name = $_POST['data']['book_name'] ? $_POST['data']['book_name'] : '';
$author = $_POST['data']['author'] ? $_POST['data']['author'] : '';
$res = $db->add_book($book_id,$book_name,$author);
if($res){
$d = array(
"id"=> $res,
"book_id" => $book_id,
"book_name" => $book_name,
"author" => $author
);
$resp['data'] = $d;
$resp['status'] = 'OK';
$resp['msg'] = 'Book has been added successfully.';
}else{
$resp['status'] = 'ERR';
$resp['msg'] = 'Some problem occurred, please try again.';
}
}else{
$resp['status'] = 'ERR';
$resp['msg'] = 'Some problem occurred, please try again.';
}
echo json_encode($resp);
break;
case "edit":
if(!empty($_POST['data'])){
$update = $db->update($_POST['data']['book_id'],$_POST['data']['book_name'],$_POST['data']['author'],$_POST['data']['id']);
if($update){
$resp['status'] = 'OK';
$resp['msg'] = 'Book data has been updated successfully.';
}else{
$resp['status'] = 'ERR';
$resp['msg'] = 'Some problem occurred, please try again.';
}
}else{
$resp['status'] = 'ERR';
$resp['msg'] = 'Some problem occurred, please try again.';
}
echo json_encode($resp);
break;
case "delete":
if(!empty($_POST['id'])){
$id = $_POST['id'];
$delete = $db->delete($id);
if($delete){
$resp['status'] = 'OK';
$resp['msg'] = 'Book data has been deleted successfully.';
}else{
$resp['status'] = 'ERR';
$resp['msg'] = 'Something went wrong.';
}
}else{
$resp['status'] = 'ERR';
$resp['msg'] = 'Something went wrong.';
}
echo json_encode($resp);
break;
default:
echo '{"status":"INVALID"}';
}
}
So in the above file, we have received the request with input values from the angular part. In the above code we have performed as per the difference case(add/edit/delete) with switch case loop. From this page request goes to the part where the request interect with the mysql part or where we actually deal with the mysql database. So let's lets move to the next part and see the file connect.php.
<?php
/*
* Class to create database connection...
* @Author: Suraj Roy
* @Date: 30 Aug 2016
* @Source url: https://jsonworld.com
* @Topic : CRUD operations using Angularjs, PHP & mysql.
*/
class DATABASE {
private $dbHost = 'localhost';
private $dbUsername = 'root';
private $dbPassword = 'root';
private $dbName = 'demo_angularjs_php';
public $db;
/*
* Db connection...
*/
public function __construct(){
$this->conn = mysqli_connect($this->dbHost,$this->dbUsername,$this->dbPassword,$this->dbName);
if (mysqli_connect_errno()){
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
}
/*
* @fetching data from db books table
*/
public function getRows($table,$conditions = array()){
$rows = [];
$sql = "SELECT * FROM books";
$result=mysqli_query($this->conn,$sql) or die(mysqli_error($this->conn));
while($row=mysqli_fetch_object($result)){
$rows[] = $row;
}
return !empty($rows)?$rows:false;
}
/*
* Insert data into the database
*/
public function add_book($book_id,$book_name,$author){
if(!empty($book_name)){
$sql = "INSERT INTO books SET book_id='$book_id',book_name='$book_name',author='$author'";
$res = mysqli_query($this->conn,$sql) or die(mysqli_error($this->conn));
if($res){
$book_id = mysqli_insert_id($this->conn);
return $book_id;
}else{
return false;
}
}else{
return false;
}
}
/*
* Update data into the database...
*/
public function update($book_id,$book_name,$author,$id){
if(!empty($id)){
$sql = "UPDATE books SET book_id='$book_id',book_name='$book_name',author='$author' WHERE id='$id'"; //,,email='$email',
$query = mysqli_query($this->conn,$sql);
$row = mysqli_affected_rows($this->conn);
return $query?$row:false;
}else{
return false;
}
}
}
You can download working demo zipped code from here.
That's all for now. Thank you for reading and I hope this post will be helpful for beginners who want to work with angular using php/mysql at backend.
Let me know your thoughts over the email demo.jsonworld@gmail.com. I would love to hear them and If you like this article, share with your friends.
