PHP OOP CRUD Operations using PDO Extension and MySQL


In the earlier article, we’ve already published PHP CRUD functionality tutorial using Ajax. There we’ve using MySQLi to connect database and database-related operations. After publishing that article we received many requests from our readers for PHP CRUD tutorial with PDO extension and MySQL. They also mentioned that they are waiting for our tutorial because complete and clear PHP MySQL PDO tutorial was not found after huge searching. Waiting is over! Here we’ll show the view, add, edit, update, and delete operations using PDO in PHP.

The PHP Data Objects (PDO) extension defines a consistent, lightweight interface for accessing databases in PHP. PDO extension provides a data-access abstraction layer, which means that you can use different databases using the same functions to queries and fetch data from the database.

In this PHP PDO tutorial, through implementing the simple PHP CRUD operation you’ll learn PDO connection, PDO insert, PDO select, PDO update, PDO delete query management. Here we’ll provide the example script on simple view, add, edit, and delete functionality with PHP OOP using PDO MySQL.

In this example script, we’ll fetch the users data from the database and display the user data list with add link, edit link, and delete link. By these links user can add new data to the database, update previously inserted data and delete the data from the database. We’ll use Object oriented approach to building the example script and bootstrap table structure for styling the list, form fields, and links.

Before you begin, take on a look on the files structure of the example application which we’re going to build.

php-oop-crud-pdo-mysql-tutorial-files-structure-codexworld

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,
 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 with the database, insert, update and delete the record from the database. You 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, insert, update, and delete) operations
 * with PHP Data Objects (PDO)
 * @author    CodexWorld.com
 * @url       http://www.codexworld.com
 * @license   http://www.codexworld.com/license
 */
class DB{
    private $dbHost     "localhost";
    private 
$dbUsername "root";
    private 
$dbPassword "";
    private 
$dbName     "codexworld";
    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();
            }
        }
        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){
                 
$query->bindValue(':'.$key$val);
            }
            
$insert $query->execute();
            return 
$insert?$this->db->lastInsertId():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)?', ':'';
                
$colvalSet .= $pre.$key."='".$val."'";
                
$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 (insert, update, delete records)

This file handles the requests coming from the HTML page using DB class. Based on the request, user data would add, update, delete to the database. Here the code is executed based on the action_type. action_type would be three types, add, edit, and delete. The following operations can happen based on the action_type.
add insert the record in the database, status message store into the session and return to the list page.
edit updates the record in the database status message store into the session and return to the list page.
delete deletes the record from the database status message store into the session and return to the list page.

<?php
session_start
();
include 
'DB.php';
$db = new DB();
$tblName 'pdo_users';
if(isset(
$_REQUEST['action_type']) && !empty($_REQUEST['action_type'])){
    if(
$_REQUEST['action_type'] == 'add'){
        
$userData = array(
            
'name' => $_POST['name'],
            
'email' => $_POST['email'],
            
'phone' => $_POST['phone']
        );
        
$insert $db->insert($tblName,$userData);
        
$statusMsg $insert?'User data has been inserted successfully.':'Some problem occurred, please try again.';
        
$_SESSION['statusMsg'] = $statusMsg;
        
header("Location:index.php");
    }elseif(
$_REQUEST['action_type'] == 'edit'){
        if(!empty(
$_POST['id'])){
            
$userData = array(
                
'name' => $_POST['name'],
                
'email' => $_POST['email'],
                
'phone' => $_POST['phone']
            );
            
$condition = array('id' => $_POST['id']);
            
$update $db->update($tblName,$userData,$condition);
            
$statusMsg $update?'User data has been updated successfully.':'Some problem occurred, please try again.';
            
$_SESSION['statusMsg'] = $statusMsg;
            
header("Location:index.php");
        }
    }elseif(
$_REQUEST['action_type'] == 'delete'){
        if(!empty(
$_GET['id'])){
            
$condition = array('id' => $_GET['id']);
            
$delete $db->delete($tblName,$condition);
            
$statusMsg $delete?'User data has been deleted successfully.':'Some problem occurred, please try again.';
            
$_SESSION['statusMsg'] = $statusMsg;
            
header("Location:index.php");
        }
    }
}

index.php (View Data)

This is the main listing page where all the users are listed with add, edit, and delete links.

Bootstrap libraries:
Bootstrap CSS & JS library need to be included if you want to use Bootstrap table and form structure, otherwise, omit it.

<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet">
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>

PHP & HTML:
Include the DB.php file for using the DB class to fetch the users data from the database using PDO and MySQL.

<?php
session_start
();
if(!empty(
$_SESSION['statusMsg'])){
    echo 
'<p>'.$_SESSION['statusMsg'].'</p>';
    unset(
$_SESSION['statusMsg']);
}
?> <div class="row"> <div class="panel panel-default users-content"> <div class="panel-heading">Users <a href="add.php" class="glyphicon glyphicon-plus"></a></div> <table class="table"> <tr> <th width="5%">#</th> <th width="20%">Name</th> <th width="30%">Email</th> <th width="20%">Phone</th> <th width="12%">Created</th> <th width="13%"></th> </tr>             <?php
            
include 'DB.php';
            
$db = new DB();
            
$users $db->getRows('pdo_users',array('order_by'=>'id DESC'));
            if(!empty(
$users)){ $count 0; foreach($users as $user){ $count++;?> <tr> <td><?php echo $count?></td> <td><?php echo $user['name']; ?></td> <td><?php echo $user['email']; ?></td> <td><?php echo $user['phone']; ?></td> <td><?php echo $user['created']; ?></td> <td> <a href="edit.php?id=<?php echo $user['id']; ?>" class="glyphicon glyphicon-edit"></a> <a href="action.php?action_type=delete&id=<?php echo $user['id']; ?> class="glyphicon glyphicon-trash" onclick="return confirm('Are you sure?');"></a> </td> </tr>             <?php } }else{ ?> <tr><td colspan="4">No user(s) found......</td>             <?php ?> </table> </div> </div>

add.php (Add Data)

In this file, an HTML form would display to collecting the user data and submitted to the action.php file. Also, a hidden field would be submitted with the respective action_type.

<div class="row">
    <div class="panel panel-default user-add-edit">
        <div class="panel-heading">Add User <a href="index.php" class="glyphicon glyphicon-arrow-left"></a></div>
        <div class="panel-body">
            <form method="post" action="action.php" class="form" id="userForm">
                <div class="form-group">
                    <label>Name</label>
                    <input type="text" class="form-control" name="name"/>
                </div>
                <div class="form-group">
                    <label>Email</label>
                    <input type="text" class="form-control" name="email"/>
                </div>
                <div class="form-group">
                    <label>Phone</label>
                    <input type="text" class="form-control" name="phone"/>
                </div>
                <input type="hidden" name="action_type" value="add"/>
                <input type="submit" class="form-control btn-default" name="submit" value="Add User"/>
            </form>
        </div>
    </div>
</div>

edit.php (Edit Data)

In this file, an HTML form would display with existing user data and submitted to the action.php file. Also, two hidden fields would be submitted with the respective action_type and user id.

<?php
include 'DB.php';
$db = new DB();
$userData $db->getRows('pdo_users',array('where'=>array('id'=>$_GET['id']),'return_type'=>'single'));
if(!empty(
$userData)){
?> <div class="row"> <div class="panel panel-default user-add-edit"> <div class="panel-heading">Edit User <a href="index.php" class="glyphicon glyphicon-arrow-left"></a></div> <div class="panel-body"> <form method="post" action="action.php" class="form" id="userForm"> <div class="form-group"> <label>Name</label> <input type="text" class="form-control" name="name" value="<?php echo $userData['name']; ?>"/> </div> <div class="form-group"> <label>Email</label> <input type="text" class="form-control" name="email" value="<?php echo $userData['email']; ?>"/> </div> <div class="form-group"> <label>Phone</label> <input type="text" class="form-control" name="phone" value="<?php echo $userData['phone']; ?>"/> </div> <input type="hidden" name="id" value="<?php echo $userData['id']; ?>"/> <input type="hidden" name="action_type" value="edit"/> <input type="submit" class="form-control btn-default" name="submit" value="Update User"/> </form> </div> </div> </div> <?php ?>

1 Comment

  1. Jonecir Said...

Leave a reply

Connect With CodexWorld