AngularJS CRUD Operations with PHP and MySQL

Demo

AngularJS is a JavaScript Framework and it is a library written in JavaScript. AngularJS can be added to a web page using a <script> tag. AngularJS extends HTML attributes with Directives. AngularJS Directives are HTML attributes with a "ng" prefix (ng-init). If you are a beginner to AngularJS and looking for working example on AngularJs, this tutorial will help you a lot. This tutorial will focus on CRUD (Create, Read, Update, and Delete) operations with AngularJS. We?ll do the view, add, edit, and delete operations on a single page using AngularJS with PHP and MySQL.

In this example AngularJS CRUD application, we?ll implement the following functionalities.

  • Fetch the users data from the database using PHP & MySQL, and display the users data using AngularJS.
  • Add user data to the database using AngularJS, PHP, and MySQL.
  • Edit and update user data using AngularJS, PHP, and MySQL.
  • Delete user data from the database using AngularJS, PHP, and MySQL.

All the CRUD operations (view, add, edit, delete) will be done on a single page and without page reload or refresh. In front-end part mainly AngularJs will handle the whole process and little jQuery will be used for some cases. In the back-end, PHP will communicate with the database and provide the respective requested data to the front-end. PDO extension and MySQL will help to connect with the database and database-related operations (select, insert, update, and delete).

Before you begin to AngularJS CRUD example, take a look at the files structure of the application which are going to build.

angularjs-crud-operations-php-mysql-files-structure-semicolonworld

Database Table Creation

For this example application, we?ll create a simple table (users) with some basic columns where users data would be stored.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `created` datetime NOT NULL,
 `modified` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Class (DB.php)

DB class handles all the operations related to the database using PHP PDO extension and MySQL. For example, connect to the database, fetch, insert, update and delete the record from the database. You only need to change the $dbHost, $dbUsername, $dbPassword, and $dbName variables value as per the database credentials.

<?php
/*
 * DB Class
 * This class is used for database related (connect, get, insert, update, and delete) operations
 * with PHP Data Objects (PDO)
 * @author    semicolonworld.com
 * @url       http://www.semicolonworld.com
 * @license   http://www.semicolonworld.com/license
 */
class DB {
    
// Database credentials
    
private $dbHost     'localhost';
    private 
$dbUsername 'root';
    private 
$dbPassword '';
    private 
$dbName     'semicolonworld';
    public 
$db;
    
    
/*
     * Connect to the database and return db connecction
     */
    
public function __construct(){
        if(!isset(
$this->db)){
            
// Connect to the database
            
try{
                
$conn = new PDO("mysql:host=".$this->dbHost.";dbname=".$this->dbName$this->dbUsername$this->dbPassword);
                
$conn -> setAttribute(PDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION);
                
$this->db $conn;
            }catch(
PDOException $e){
                die(
"Failed to connect with MySQL: " $e->getMessage());
            }
        }
    }
    
    
/*
     * Returns rows from the database based on the conditions
     * @param string name of the table
     * @param array select, where, order_by, limit and return_type conditions
     */
    
public function getRows($table,$conditions = array()){
        
$sql 'SELECT ';
        
$sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
        
$sql .= ' FROM '.$table;
        if(
array_key_exists("where",$conditions)){
            
$sql .= ' WHERE ';
            
$i 0;
            foreach(
$conditions['where'] as $key => $value){
                
$pre = ($i 0)?' AND ':'';
                
$sql .= $pre.$key." = '".$value."'";
                
$i++;
            }
        }
        
        if(
array_key_exists("order_by",$conditions)){
            
$sql .= ' ORDER BY '.$conditions['order_by']; 
        }
        
        if(
array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            
$sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; 
        }elseif(!
array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            
$sql .= ' LIMIT '.$conditions['limit']; 
        }
        
        
$query $this->db->prepare($sql);
        
$query->execute();
        
        if(
array_key_exists("return_type",$conditions) && $conditions['return_type'] != 'all'){
            switch(
$conditions['return_type']){
                case 
'count':
                    
$data $query->rowCount();
                    break;
                case 
'single':
                    
$data $query->fetch(PDO::FETCH_ASSOC);
                    break;
                default:
                    
$data '';
            }
        }else{
            if(
$query->rowCount() > 0){
                
$data $query->fetchAll(PDO::FETCH_ASSOC);
            }
        }
        return !empty(
$data)?$data:false;
    }
    
    
/*
     * Insert data into the database
     * @param string name of the table
     * @param array the data for inserting into the table
     */
    
public function insert($table,$data){
        if(!empty(
$data) && is_array($data)){
            
$columns '';
            
$values  '';
            
$i 0;
            if(!
array_key_exists('created',$data)){
                
$data['created'] = date("Y-m-d H:i:s");
            }
            if(!
array_key_exists('modified',$data)){
                
$data['modified'] = date("Y-m-d H:i:s");
            }

            
$columnString implode(','array_keys($data));
            
$valueString ":".implode(',:'array_keys($data));
            
$sql "INSERT INTO ".$table." (".$columnString.") VALUES (".$valueString.")";
            
$query $this->db->prepare($sql);
            foreach(
$data as $key=>$val){
                
$val htmlspecialchars(strip_tags($val));
                
$query->bindValue(':'.$key$val);
            }
            
$insert $query->execute();
            if(
$insert){
                
$data['id'] = $this->db->lastInsertId();
                return 
$data;
            }else{
                return 
false;
            }
        }else{
            return 
false;
        }
    }
    
    
/*
     * Update data into the database
     * @param string name of the table
     * @param array the data for updating into the table
     * @param array where condition on updating data
     */
    
public function update($table,$data,$conditions){
        if(!empty(
$data) && is_array($data)){
            
$colvalSet '';
            
$whereSql '';
            
$i 0;
            if(!
array_key_exists('modified',$data)){
                
$data['modified'] = date("Y-m-d H:i:s");
            }
            foreach(
$data as $key=>$val){
                
$pre = ($i 0)?', ':'';
                
$val htmlspecialchars(strip_tags($val));
                
$colvalSet .= $pre.$key."='".$val."'";
      &nnbsp;         
$i++;
            }
            if(!empty(
$conditions)&& is_array($conditions)){
                
$whereSql .= ' WHERE ';
                
$i 0;
                foreach(
$conditions as $key => $value){
                    
$pre = ($i 0)?' AND ':'';
                    
$whereSql .= $pre.$key." = '".$value."'";
                    
$i++;
                }
            }
            
$sql "UPDATE ".$table." SET ".$colvalSet.$whereSql;
            
$query $this->db->prepare($sql);
            
$update $query->execute();
            return 
$update?$query->rowCount():false;
        }else{
            return 
false;
        }
    }
    
    
/*
     * Delete data from the database
     * @param string name of the table
     * @param array where condition on deleting data
     */
    
public function delete($table,$conditions){
        
$whereSql '';
        if(!empty(
$conditions)&& is_array($conditions)){
            
$whereSql .= ' WHERE ';
            
$i 0;
            foreach(
$conditions as $key => $value){
                
$pre = ($i 0)?' AND ':'';
                
$whereSql .= $pre.$key." = '".$value."'";
                
$i++;
            }
        }
        
$sql "DELETE FROM ".$table.$whereSql;
        
$delete $this->db->exec($sql);
        return 
$delete?$delete:false;
    }
}

action.php (fetch, insert, update, and delete records)

This file handles the requests coming from the HTML page by AngularJS and DB class helps to database related operation. Based on the request, user data would read, add, update, delete from the database. Here the code is executed based on the type. type would be four types, view, add, edit, and delete. The following operations can happen based on the type.
view: fetch the records from the database, records and status message returns as JSON format.
add: insert the record in the database, inserted data and status message returns as JSON format.
edit: update the record in the database, status message returns as JSON format.
delete: delete the record from the database, status message returns as JSON format.

<?php
include 'DB.php';
$db = new DB();
$tblName 'users';
if(isset(
$_REQUEST['type']) && !empty($_REQUEST['type'])){
    
$type $_REQUEST['type'];
    switch(
$type){
        case 
"view":
            
$records $db->getRows($tblName);
            if(
$records){
                
$data['records'] = $db->getRows($tblName);
                
$data['status'] = 'OK';
            }else{
                
$data['records'] = array();
                
$data['status'] = 'ERR';
            }
            echo 
json_encode($data);
            break;
        case 
"add":
            if(!empty(
$_POST['data'])){
                
$userData = array(
                    
'name' => $_POST['data']['name'],
                    
'email' => $_POST['data']['email'],
                    
'phone' => $_POST['data']['phone']
                );
                
$insert $db->insert($tblName,$userData);
                if(
$insert){
                    
$data['data'] = $insert;
                    
$data['status'] = 'OK';
                    
$data['msg'] = 'User data has been added successfully.';
                }else{
                    
$data['status'] = 'ERR';
                    
$data['msg'] = 'Some problem occurred, please try again.';
                }
            }else{
                
$data['status'] = 'ERR';
                
$data['msg'] = 'Some problem occurred, please try again.';
            }
            echo 
json_encode($data);
            break;
        case 
"edit":
            if(!empty(
$_POST['data'])){
                
$userData = array(
                    
'name' => $_POST['data']['name'],
                    
'email' => $_POST['data']['email'],
                    
'phone' => $_POST['data']['phone']
                );
                
$condition = array('id' => $_POST['data']['id']);
                
$update $db->update($tblName,$userData,$condition);
                if(
$update){
                    
$data['status'] = 'OK';
                    
$data['msg'] = 'User data has been updated successfully.';
                }else{
                    
$data['status'] = 'ERR';
                    
$data['msg'] = 'Some problem occurred, please try again.';
                }
            }else{
                
$data['status'] = 'ERR';
                
$data['msg'] = 'Some problem occurred, please try again.';
            }
            echo 
json_encode($data);
            break;
        case 
"delete":
            if(!empty(
$_POST['id'])){
                
$condition = array('id' => $_POST['id']);
                
$delete $db->delete

Related Articles

Comments 0