PHP OOP CRUD Operations using PDO Extension with MySQL

By: CodexWorld | In: PHP | Last Updated: Nov 7, 2021

The PHP Data Objects (PDO) extension defines a lightweight interface to access database servers in PHP. You can use the same functions to execute SQL queries for different databases (MySQL, MS SQL, Oracle, PostgreSQL, etc.). Since it provides a data-access abstraction layer, the database server can be switched easily without code-level changes. When it comes to database operations in PHP, PDO is the powerful option for connecting and working with the database server.

In most cases, the MySQLi extension is used for database operations in PHP. If you want to use another database server except for MySQL, PDO is the best choice for database-specific operations. Mostly, connect, fetch, insert, update, and delete operations are executed in the database. In this tutorial, we will implement the simple PHP CRUD operation using PDO extension with MySQL, which help you to learn PDO connection, PDO insert, PDO select, PDO update, PDO delete query management. Here we’ll provide the example script on view, add, edit, and delete functionality with PHP OOP using PDO MySQL. We’ll use an Object-oriented approach to building the example script and bootstrap table structure for styling the list, form fields, and links.

In the example PHP CRUD with PDO script, the following functionalities will be implemented.

Fetch user data from the database and display the list on the web page.
Add user data in the MySQL database.
Edit and update data in the MySQL database.
Delete data from the MySQL database.

Before getting started to create a CRUD application with PDO extension and MySQL, take a look at the files structure.

php_crud_with_pdo_mysql/
├── index.php
├── add.php
├── edit.php
├── action.php
├── DB.class.php
├── bootstrap/
│   └── bootstrap.min.css
├── css/
│   └── style.css
└── images/

Create Database Table

To store the data a table is required in the database. The following SQL creates a users table with some basic fields in the MySQL database.

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) 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.class.php)

The DB class handles all the database related operations (connect, insert, update, and delete). Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.

__construct() – Connect to the database using PHP PDO extension with MySQL.
getRows() – Fetch records from the database.
insert() – Insert data into the database.
update() – Update data into the database.
delete() – Delete data from the database.

<?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 = "root"; 
    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_ERRMODE, PDO::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; 
    } 
}

CRUD Operations (action.php)

The action.php file is used to perform the CRUD operations using PHP PDO extension and MySQL (DB class). The code is executed based on the action_type specified in the URL query string:

add – Add or insert records in the database.
edit – Update records in the database using the id given in the query string of the link.
delete – Remove data from the database using the id given in the query string of the link.

After the data manipulation, the status is stored using PHP SESSION and redirects back to the respective web page.

<?php 
// Start session 
session_start(); 
 
// Include and initialize DB class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Database table name 
$tblName = 'users'; 
 
$postData = $statusMsg = $valErr = ''; 
$status = 'danger'; 
$redirectURL = 'index.php'; 
 
// If Add request is submitted 
if(!empty($_REQUEST['action_type']) && $_REQUEST['action_type'] == 'add'){ 
    $redirectURL = 'add.php'; 
     
    // Get user's input 
    $postData = $_POST; 
    $name = !empty($_POST['name'])?trim($_POST['name']):''; 
    $email = !empty($_POST['email'])?trim($_POST['email']):''; 
    $phone = !empty($_POST['phone'])?trim($_POST['phone']):''; 
     
    // Validate form fields 
    if(empty($name)){ 
        $valErr .= 'Please enter your name.<br/>'; 
    } 
    if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){ 
        $valErr .= 'Please enter a valid email.<br/>'; 
    } 
    if(empty($phone)){ 
        $valErr .= 'Please enter your phone no.<br/>'; 
    } 
     
    // Check whether user inputs are empty 
    if(empty($valErr)){ 
        // Insert data into the database 
        $userData = array( 
            'name' => $name, 
            'email' => $email, 
            'phone' => $phone 
        ); 
        $insert = $db->insert($tblName, $userData); 
         
        if($insert){ 
            $status = 'success'; 
            $statusMsg = 'User data has been added successfully!'; 
            $postData = ''; 
             
            $redirectURL = 'index.php'; 
        }else{ 
            $statusMsg = 'Something went wrong, please try again after some time.'; 
        } 
    }else{ 
        $statusMsg = '<p>Please fill all the mandatory fields:</p>'.trim($valErr, '<br/>'); 
    } 
     
    // Store status into the SESSION 
    $sessData['postData'] = $postData; 
    $sessData['status']['type'] = $status; 
    $sessData['status']['msg'] = $statusMsg; 
    $_SESSION['sessData'] = $sessData; 
}elseif(!empty($_REQUEST['action_type']) && $_REQUEST['action_type'] == 'edit' && !empty($_POST['id'])){ // If Edit request is submitted 
    $redirectURL = 'edit.php?id='.$_POST['id']; 
     
    // Get user's input 
    $postData = $_POST; 
    $name = !empty($_POST['name'])?trim($_POST['name']):''; 
    $email = !empty($_POST['email'])?trim($_POST['email']):''; 
    $phone = !empty($_POST['phone'])?trim($_POST['phone']):''; 
     
    // Validate form fields 
    if(empty($name)){ 
        $valErr .= 'Please enter your name.<br/>'; 
    } 
    if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){ 
        $valErr .= 'Please enter a valid email.<br/>'; 
    } 
    if(empty($phone)){ 
        $valErr .= 'Please enter your phone no.<br/>'; 
    } 
     
    // Check whether user inputs are empty 
    if(empty($valErr)){ 
        // Update data in the database 
        $userData = array( 
            'name' => $name, 
            'email' => $email, 
            'phone' => $phone 
        ); 
        $conditions = array('id' => $_POST['id']); 
        $update = $db->update($tblName, $userData, $conditions); 
         
        if($update){ 
            $status = 'success'; 
            $statusMsg = 'User data has been updated successfully!'; 
            $postData = ''; 
             
            $redirectURL = 'index.php'; 
        }else{ 
            $statusMsg = 'Something went wrong, please try again after some time.'; 
        } 
    }else{ 
        $statusMsg = '<p>Please fill all the mandatory fields:</p>'.trim($valErr, '<br/>'); 
    } 
     
    // Store status into the SESSION 
    $sessData['postData'] = $postData; 
    $sessData['status']['type'] = $status; 
    $sessData['status']['msg'] = $statusMsg; 
    $_SESSION['sessData'] = $sessData; 
}elseif(!empty($_REQUEST['action_type']) && $_REQUEST['action_type'] == 'delete' && !empty($_GET['id'])){ // If Delete request is submitted 
    // Delete data from the database 
    $conditions = array('id' => $_GET['id']); 
    $delete = $db->delete($tblName, $conditions); 
     
    if($delete){ 
        $status = 'success'; 
        $statusMsg = 'User data has been deleted successfully!'; 
    }else{ 
        $statusMsg = 'Something went wrong, please try again after some time.'; 
    } 
     
    // Store status into the SESSION 
    $sessData['status']['type'] = $status; 
    $sessData['status']['msg'] = $statusMsg; 
    $_SESSION['sessData'] = $sessData; 
} 
 
// Redirect to the home/add/edit page 
header("Location: $redirectURL"); 
exit;

Bootstrap Library

The Bootstrap library is used to provide a better UI for styling the table, list, form fields, and links. Include the CSS file of the Bootstrap 4 library. If you want to use custom stylesheet, you can omit it.

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.3/css/bootstrap.min.css">

Read & Delete Records (index.php)

In the index.php file, all the records are retrieved from the database and list them in tabular format with Add, Edit, and Delete links.

The Add link redirects to the add.php page to perform the Create operation.
The Edit link redirects to the edit.php page to perform the Update operation.
The Delete link redirects to the action.php file with action_type and id params. In the action.php file, the record is deleted from the database based on the row id.

<?php 
// Start session 
session_start(); 
 
// Get data from session 
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:''; 
 
// Get status from session 
if(!empty($sessData['status']['msg'])){ 
    $statusMsg = $sessData['status']['msg']; 
    $status = $sessData['status']['type']; 
    unset($_SESSION['sessData']['status']); 
} 
 
// Include and initialize DB class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Fetch the users data 
$users = $db->getRows('users', array('order_by'=>'id DESC')); 
 
// Retrieve status message from session 
if(!empty($_SESSION['statusMsg'])){ 
    echo '<p>'.$_SESSION['statusMsg'].'</p>'; 
    unset($_SESSION['statusMsg']); 
} 
?>

<div class="row">
    <div class="col-md-12 head">
        <h5>Users</h5>
        <!-- Add link -->
        <div class="float-right">
            <a href="add.php" class="btn btn-success"><i class="plus"></i> New User</a>
        </div>
    </div>
    
    <!-- Status message -->
    <?php if(!empty($statusMsg)){ ?>
        <div class="alert alert-<?php echo $status; ?>"><?php echo $statusMsg; ?></div>
    <?php } ?>

    <!-- List the users -->
    <table class="table table-striped table-bordered">
        <thead class="thead-dark">
            <tr>
                <th width="5%">#</th>
                <th width="20%">Name</th>
                <th width="25%">Email</th>
                <th width="18%">Phone</th>
                <th width="18%">Created</th>
                <th width="14%">Action</th>
            </tr>
        </thead>
        <tbody>
            <?php if(!empty($users)){ $i=0; foreach($users as $row){ $i++; ?>
            <tr>
                <td><?php echo $i; ?></td>
                <td><?php echo $row['name']; ?></td>
                <td><?php echo $row['email']; ?></td>
                <td><?php echo $row['phone']; ?></td>
                <td><?php echo $row['created']; ?></td>
                <td>
                    <a href="edit.php?id=<?php echo $row['id']; ?>" class="btn btn-warning">edit</a>
                    <a href="action.php?action_type=delete&id=<?php echo $row['id']; ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete data?');">delete</a>
                </td>
            </tr>
            <?php } }else{ ?>
            <tr><td colspan="5">No user(s) found...</td></tr>
            <?php } } ?>
        </tbody>
    </table>
</div>

Create Records (add.php)

In the add.php file, an HTML form is provided to input data for the Create operation.

After the form submission, the data is posted to the action.php file to insert the record in the database.

<?php 
// Start session 
session_start(); 
 
// Get data from session 
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:''; 
 
// Get status from session 
if(!empty($sessData['status']['msg'])){ 
    $statusMsg = $sessData['status']['msg']; 
    $status = $sessData['status']['type']; 
    unset($_SESSION['sessData']['status']); 
} 
 
// Get submitted form data  
$postData = array(); 
if(!empty($sessData['postData'])){ 
    $postData = $sessData['postData']; 
    unset($_SESSION['postData']); 
} 
?>

<div class="row">
    <div class="col-md-12 head">
        <h5>Add User</h5>
        
        <!-- Back link -->
        <div class="float-right">
            <a href="index.php" class="btn btn-success"><i class="back"></i> Back</a>
        </div>
    </div>
    
    <!-- Status message -->
    <?php if(!empty($statusMsg)){ ?>
        <div class="alert alert-<?php echo $status; ?>"><?php echo $statusMsg; ?></div>
    <?php } ?>
    
    <div class="col-md-12">
        <form method="post" action="action.php" class="form">
            <div class="form-group">
                <label>Name</label>
                <input type="text" class="form-control" name="name" value="<?php echo !empty($postData['name'])?$postData['name']:''; ?>" required="">
            </div>
            <div class="form-group">
                <label>Email</label>
                <input type="email" class="form-control" name="email" value="<?php echo !empty($postData['email'])?$postData['email']:''; ?>" required="">
            </div>
            <div class="form-group">
                <label>Phone</label>
                <input type="text" class="form-control" name="phone" value="<?php echo !empty($postData['phone'])?$postData['phone']:''; ?>" required="">
            </div>
            <input type="hidden" name="action_type" value="add"/>
            <input type="submit" class="form-control btn-primary" name="submit" value="Add User"/>
        </form>
    </div>
</div>

Update Records (edit.php)

In the edit.php file, an HTML form is provided to input data for the Update operation.

The existing data will be retrieved from the database based on the ID and the form fields will be pre-filled.
After the form submission, the data is posted to the action.php file to update the record in the database.

<?php 
// Start session 
session_start(); 
 
// Get data from session 
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:''; 
 
// Get status from session 
if(!empty($sessData['status']['msg'])){ 
    $statusMsg = $sessData['status']['msg']; 
    $status = $sessData['status']['type']; 
    unset($_SESSION['sessData']['status']); 
} 
 
// Include and initialize DB class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Fetch the user data by ID 
if(!empty($_GET['id'])){ 
    $conditons = array( 
        'where' => array( 
            'id' => $_GET['id'] 
        ), 
        'return_type' => 'single' 
    ); 
    $userData = $db->getRows('users', $conditons); 
} 
 
// Redirect to list page if invalid request submitted 
if(empty($userData)){ 
    header("Location: index.php"); 
    exit; 
} 
 
// Get submitted form data  
$postData = array(); 
if(!empty($sessData['postData'])){ 
    $postData = $sessData['postData']; 
    unset($_SESSION['postData']); 
} 
?>

<div class="row">
    <div class="col-md-12 head">
        <h5>Edit User</h5>
        
        <!-- Back link -->
        <div class="float-right">
            <a href="index.php" class="btn btn-success"><i class="back"></i> Back</a>
        </div>
    </div>
    
    <!-- Status message -->
    <?php if(!empty($statusMsg)){ ?>
        <div class="alert alert-<?php echo $status; ?>"><?php echo $statusMsg; ?></div>
    <?php } ?>
    
    <div class="col-md-12">
        <form method="post" action="action.php" class="form">
            <div class="form-group">
                <label>Name</label>
                <input type="text" class="form-control" name="name" value="<?php echo !empty($postData['name'])?$postData['name']:$userData['name']; ?>" required="">
            </div>
            <div class="form-group">
                <label>Email</label>
                <input type="email" class="form-control" name="email" value="<?php echo !empty($postData['email'])?$postData['email']:$userData['email']; ?>" required="">
            </div>
            <div class="form-group">
                <label>Phone</label>
                <input type="text" class="form-control" name="phone" value="<?php echo !empty($postData['phone'])?$postData['phone']:$userData['phone']; ?>" required="">
            </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-primary" name="submit" value="Update User"/>
        </form>
    </div>
</div>

PHP CRUD Operations without Page Refresh using jQuery, Ajax, and MySQL

Conclusion

In the earlier article, we’ve already published a PHP CRUD functionality tutorial using Ajax. There we’ve used MySQLi to connect database and database-related operations. After publishing that article we received many requests from our readers for a PHP CRUD tutorial with PDO extension and MySQL. They also mentioned that they are waiting for our tutorial because a complete and clear PHP MySQL PDO tutorial was not found after a huge search. Hope! this tutorial with an example script will help you to integrate view, add, edit, update, and delete operations using PDO in PHP.

Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request

Download Source Code

Add Featured Image to WordPress RSS Feed

How to Create and Save XML File using PHP

11 Comments

Parmod Said...

are you planning to upgrade with code for php 8+

May 6, 2023 at 5:58 AM
Nilton Oliveira Said...

What is the possibility of an example crud with select option of table states and cities related to the user table and also including the photo in the record.

November 17, 2022 at 1:24 PM
Mahesh Kumar Said...

how to use where condition on getRows() for multiple tables with JOINS or table1 as t1 , table2 as t2

January 5, 2022 at 8:52 PM
Shahzad Choudhary Said...

How we get here last insert id?
$insert = $db->insert($tblName,$userData);

April 4, 2021 at 12:54 PM

CodexWorld Said...

The insert() function will return the last insert ID if an insert is successful.

April 7, 2021 at 5:48 PM

Tom Said...

awesome tutorial man.
it will be better if it comes with sql query error handling
thanks

June 12, 2018 at 9:01 AM
Alen Said...

Hi I want to ask you about form data. which is secure way to post form data???

December 15, 2017 at 8:49 PM
Raj Dhanki Said...

Please upload php with mysqli with all fields like textbox checkbox select radio file upload and do insert update delete display in tabular format

November 21, 2017 at 1:56 AM
Riad Hossain Said...

Hello,
This is awesome… Now i want to add some columns. how to do this???

March 9, 2017 at 7:21 AM
Rene Said...

Very Nice.

I added a “IN” clause. Add this right after the if(array_key_exists(“in”,$conditions)){ section:

if(array_key_exists(“in”,$conditions)){
$i = 0;
foreach($conditions[‘in’] as $key => $value){
$pre = ($i > 0)?’ , ‘:’ AND ‘.$key.’ IN (‘;
$sql .=$pre;
$j=0;
foreach($value as $a => $b){
$pre1 = ($j > 0)?’ , ‘:”;
$sql.=$pre1.”‘”.$b.”‘”;
$j++;
}
$sql.=”)”;
$i++;
}
}

February 7, 2017 at 2:30 PM
Jonecir Said...

Hello, I just downloaded your “php_crud_with_pdo_mysql” source code, which I found quite nice.

However, I have a question, if I may:

How can I use or adapt this code in order to use multiple tables with an INNER JOIN or something like that?

Thanks in advance.

August 25, 2016 at 8:09 PM

PHP OOP CRUD Operations using PDO Extension with MySQL

Create Database Table

Database Class (DB.class.php)

CRUD Operations (action.php)

Bootstrap Library

Read & Delete Records (index.php)

Create Records (add.php)

Update Records (edit.php)

Conclusion

RELATED TUTORIALS

11 Comments

Leave a reply

Cancel

TRENDING TUTORIALS

TOPICSall topics

LATEST HOW TO GUIDES

ABOUT US

Contact

Terms

Extra Links