PHP CRUD Operations with Search and Pagination

The CRUD operation is the most used functionality in web applications. Almost all web applications use CRUD functionality to manage and manipulate data in the database. The CRUD (Create, Read, Update, and Delete) functionality can be easily implemented using PHP and MySQL. PHP CRUD with MySQL helps handle view, add, edit, and delete data from the database.

Search and Pagination are very useful features to make the data list user-friendly. When you add CRUD functionality to the website, search and pagination are must-have functionality for the CRUD data list. In this tutorial, we will show you how to implement PHP CRUD operations with search and pagination using MySQL.

In the example code, we will implement the following functionality to integrate CRUD with search filter and pagination in PHP.

• Fetch and list the members data from the database.
• Add pagination links to the data list.
• Search and filter records.
• Add member data to the database.
• Edit and update member data in the database.
• Delete records from the database.

Before getting started, take a look at the file structure of the PHP CRUD application with pagination and search script.

php_crud_with_search_pagination/
├── index.php
├── addEdit.php
├── config.php
├── DB.class.php
├── userAction.php
├── Pagination.class.php
├── css/
|   ├── bootstrap.min.css
|   └── style.css
└── images/

Create Database Table

First, create a table to store member info in the database. The following SQL creates a members table with some basic fields in the MySQL database.

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `email` varchar(50) NOT NULL,
  `country` varchar(50) DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `modified` datetime DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Basic Configuration (config.php)

In the config.php file, constant variables of the common and database settings are defined.

Common Settings:

• PER_PAGE_LIMIT – Number of records to be listed on each page.

Database Constants:

• DB_HOST – Specify the database host.
• DB_USERNAME – Specify the database username.
• DB_PASSWORD – Specify the database password.
• DB_NAME – Specify the database name.

<?php 
// Pagination configuration 
define('PER_PAGE_LIMIT', 10); 
 
// Database configuration   
define('DB_HOST', 'MySQL_Database_Host'); 
define('DB_USERNAME', 'MySQL_Database_Username');  
define('DB_PASSWORD', 'MySQL_Database_Password'); 
define('DB_NAME', 'MySQL_Database_Name');  
 
// Start session  
if(!session_id()){  
    session_start();  
}  
?>

Database Class (DB.class.php)

The DB class handles all the operations (connect, fetch, insert, update, and delete) related to the database.

• __construct() – Connect to the MySQL database server.
• getRows() – Fetch records from the database based on the specified conditions.
• insert() – Insert records in the database.
• update() – Update records in the database based on the specified conditions.
• delete() – Delete records from the database based on the specified conditions.

<?php 
/* 
 * DB Class 
 * This class is used for database related (connect, insert, update, and delete) operations 
 * @author    CodexWorld.com 
 * @url        http://www.codexworld.com 
 * @license    http://www.codexworld.com/license 
 */ 
include_once 'config.php'; 
class DB{ 
    private $dbHost     = DB_HOST; 
    private $dbUsername = DB_USERNAME; 
    private $dbPassword = DB_PASSWORD; 
    private $dbName     = DB_NAME; 
    private $tblName    = 'members'; 
     
    public function __construct(){ 
        if(!isset($this->db)){ 
            // Connect to the database 
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName); 
            if($conn->connect_error){ 
                die("Failed to connect with MySQL: " . $conn->connect_error); 
            }else{ 
                $this->db = $conn; 
            } 
        } 
    } 
     
    /* 
     * Returns rows from the database based on the conditions 
     * @param array select, where, order_by, limit and return_type conditions 
     */ 
    public function getRows($conditions = array()){ 
        $sql = 'SELECT '; 
        $sql .= array_key_exists("select", $conditions)?$conditions['select']:'*'; 
        $sql .= ' FROM '.$this->tblName; 
        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("like", $conditions) && !empty($conditions['like'])){ 
            $sql .= (strpos($sql, 'WHERE') !== false)?' AND ':' WHERE '; 
            $i = 0; 
            $likeSQL = ''; 
            foreach($conditions['like'] as $key => $value){ 
                $pre = ($i > 0)?' AND ':''; 
                $likeSQL .= $pre.$key." LIKE '%".$value."%'"; 
                $i++; 
            } 
            $sql .= '('.$likeSQL.')'; 
        } 
         
        if(array_key_exists("like_or", $conditions) && !empty($conditions['like_or'])){ 
            $sql .= (strpos($sql, 'WHERE') !== false)?' AND ':' WHERE '; 
            $i = 0; 
            $likeSQL = ''; 
            foreach($conditions['like_or'] as $key => $value){ 
                $pre = ($i > 0)?' OR ':''; 
                $likeSQL .= $pre.$key." LIKE '%".$value."%'"; 
                $i++; 
            } 
            $sql .= '('.$likeSQL.')'; 
        } 
         
        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'];  
        } 
        $result = $this->db->query($sql); 
         
        if(array_key_exists("return_type", $conditions) && $conditions['return_type'] != 'all'){ 
            switch($conditions['return_type']){ 
                case 'count': 
                    $data = $result->num_rows; 
                    break; 
                case 'single': 
                    $data = $result->fetch_assoc(); 
                    break; 
                default: 
                    $data = ''; 
            } 
        }else{ 
            if($result->num_rows > 0){ 
                while($row = $result->fetch_assoc()){ 
                    $data[] = $row; 
                } 
            } 
        } 
        return !empty($data)?$data:false; 
    } 
     
    /* 
     * Insert data into the database 
     * @param array the data for inserting into the table 
     */ 
    public function insert($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"); 
            } 
            foreach($data as $key=>$val){ 
                $pre = ($i > 0)?', ':''; 
                $columns .= $pre.$key; 
                $values  .= $pre."'".$val."'"; 
                $i++; 
            } 
            $query = "INSERT INTO ".$this->tblName." (".$columns.") VALUES (".$values.")"; 
            $insert = $this->db->query($query); 
            return $insert?$this->db->insert_id:false; 
        }else{ 
            return false; 
        } 
    } 
     
    /* 
     * Update data into the database 
     * @param array the data for updating into the table 
     * @param array where condition on updating data 
     */ 
    public function update($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++; 
                } 
            } 
            $query = "UPDATE ".$this->tblName." SET ".$colvalSet.$whereSql; 
            $update = $this->db->query($query); 
            return $update?$this->db->affected_rows:false; 
        }else{ 
            return false; 
        } 
    } 
     
    /* 
     * Delete data from the database 
     * @param array where condition on deleting data 
     */ 
    public function delete($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++; 
            } 
        } 
        $query = "DELETE FROM ".$this->tblName.$whereSql; 
        $delete = $this->db->query($query); 
        return $delete?true:false; 
    } 
} 
?>

Pagination Class (Pagination.class.php)

The Pagination class is used to generate links to control the paging of the data list. You can see all the configuration options and reference of the PHP Pagination class from here.

Data list with Search and Pagination (index.php)

Initially, all the members’ data is retrieved from the database and listed on the webpage with Add, Edit, and Delete links. Also, search and pagination options are added to the CRUD data list.

• The Add link redirects the user to the addEdit.php page to perform the Create operation.
• The Edit link redirects the user to the addEdit.php page with the respective ID to perform the Update operation.
• The Delete link redirects the user to the userAction.php file with action_type and id parameters to perform the Delete operation.
• The Search option allows sorting the record set by the user’s name/email/country.
  • Get search keywords from the query string of the URL.
  • Pass the search query in the like_or param of the getRows() function.
  • Retrieve filtered data based on the keywords.
• The Pagination helps to retrieve a limited number of records from the database and access data through links.
  • Define offset and limit.
  • Initialize the Pagination class and pass configuration options (Base URL, Total Rows Count, and Per Page Limit).
  • Call the createLinks() method of the Pagination class to render the pagination links.

<?php 
// Load and initialize database class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Load pagination class 
require_once 'Pagination.class.php'; 
 
// Get status message from session 
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:''; 
if(!empty($sessData['status']['msg'])){ 
    $statusMsg = $sessData['status']['msg']; 
    $statusMsgType = $sessData['status']['type']; 
    unset($_SESSION['sessData']['status']); 
} 
 
// Page offset and limit 
$perPageLimit = PER_PAGE_LIMIT; 
$offset = !empty($_GET['page'])?(($_GET['page']-1)*$perPageLimit):0; 
 
// Get search keyword 
$searchKeyword = !empty($_GET['sq'])?$_GET['sq']:''; 
$searchStr = !empty($searchKeyword)?'?sq='.$searchKeyword:''; 
 
// Search DB query 
$searchArr = ''; 
if(!empty($searchKeyword)){ 
    $searchArr = array( 
        'first_name' => $searchKeyword, 
        'last_name' => $searchKeyword, 
        'email' => $searchKeyword, 
        'country' => $searchKeyword 
    ); 
} 
 
// Get count of the members 
$cond = array( 
    'like_or' => $searchArr, 
    'return_type' => 'count' 
); 
$rowCount = $db->getRows($cond); 
 
// Initialize pagination class 
$pagConfig = array( 
    'baseURL' => 'index.php'.$searchStr, 
    'totalRows' => $rowCount, 
    'perPage' => $perPageLimit 
); 
$pagination = new Pagination($pagConfig); 
 
// Fetch all members from the database 
$cond = array( 
    'like_or' => $searchArr, 
    'start' => $offset, 
    'limit' => $perPageLimit, 
    'order_by' => 'id DESC', 
); 
$members = $db->getRows($cond); 
 
?>

<!-- Display status message -->
<?php if(!empty($statusMsg) && ($statusMsgType == 'success')){ ?>
<div class="col-xs-12">
    <div class="alert alert-success"><?php echo $statusMsg; ?></div>
</div>
<?php }elseif(!empty($statusMsg) && ($statusMsgType == 'error')){ ?>
<div class="col-xs-12">
    <div class="alert alert-danger"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>

<div class="row">
    <div class="col-md-12 head">
        <!-- Search form -->
        <form class="float-start">
        <div class="input-group mb-3">
            <input type="text" name="sq" class="form-control" placeholder="Search by keyword..." value="<?php echo $searchKeyword; ?>">
            <button class="btn btn-primary" type="submit">Search</button>
            <a href="index.php" class="btn btn-secondary">Reset</a>
        </div>
        </form>

        <!-- Add link -->
        <div class="float-end">
            <a href="addEdit.php" class="btn btn-success"><i class="plus"></i> New Member</a>
        </div>
    </div>
    
    <!-- Data list table --> 
    <table class="table table-striped table-bordered">
        <thead>
            <tr>
                <th>#</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Email</th>
                <th>Country</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
            <?php 
            if(!empty($members)){ $count = 0;  
                foreach($members as $row){ $count++; 
            ?>
            <tr>
                <td><?php echo $count; ?></td>
                <td><?php echo $row['first_name']; ?></td>
                <td><?php echo $row['last_name']; ?></td>
                <td><?php echo $row['email']; ?></td>
                <td><?php echo $row['country']; ?></td>
                <td>
                    <a href="addEdit.php?id=<?php echo $row['id']; ?>" class="btn btn-warning">edit</a>
                    <a href="userAction.php?action_type=delete&id=<?php echo $row['id']; ?>" class="btn btn-danger" onclick="return confirm('Are you sure to delete?');">delete</a>
                </td>
            </tr>
            <?php } }else{ ?>
            <tr><td colspan="6">No member(s) found......</td></tr>
            <?php } ?>
        </tbody>
    </table>
    
    <!-- Display pagination links -->
    <?php echo $pagination->createLinks(); ?>
</div>

Create & Update Records (addEdit.php)

The addEdit.php file holds the HTML form to collect users’ inputs and POST them to the server-side script for processing add and edit requests.

• Initially, the form data is submitted to the PHP script (userAction.php) to insert records in the users table.
• If the ID parameter exists on the URL, the existing user data will be retrieved from the database based on this ID, and the data will be pre-filled in the input fields. The data is submitted to the PHP script (userAction.php) to update the existing record in the users table.
• If any error occurred, the error message will be shown, and the pre-filled data will be rendered in the form fields.

<?php 
// Load and initialize database class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
$userData = array(); 
 
// Get session data 
$sessData = !empty($_SESSION['sessData'])?$_SESSION['sessData']:''; 
 
// Get status message from session 
if(!empty($sessData['status']['msg'])){ 
    $statusMsg = $sessData['status']['msg']; 
    $statusMsgType = $sessData['status']['type']; 
    unset($_SESSION['sessData']['status']); 
} 
 
// Get the user's input data from the session 
if(!empty($sessData['postData'])){ 
    $userData = $sessData['postData']; 
    unset($_SESSION['sessData']['postData']); 
} 
 
// Fetch member data from the database 
if(!empty($_GET['id'])){ 
    $conditions = array( 
        'where' => array( 
            'id' => $_GET['id'] 
        ) 
    ); 
    $conditions['return_type'] = 'single'; 
    $userData = $db->getRows($conditions); 
} 
 
// Define action 
$actionLabel = !empty($_GET['id'])?'Edit':'Add'; 
 
?>

<!-- Display status message -->
<?php if(!empty($statusMsg) && ($statusMsgType == 'success')){ ?>
<div class="col-xs-12">
    <div class="alert alert-success"><?php echo $statusMsg; ?></div>
</div>
<?php }elseif(!empty($statusMsg) && ($statusMsgType == 'error')){ ?>
<div class="col-xs-12">
    <div class="alert alert-danger"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>

<div class="row">
    <div class="col-md-12">
        <h2><?php echo $actionLabel; ?> Member</h2>
    </div>
    <div class="col-md-6">
        <!-- Add/Edit form -->
        <form method="post" action="userAction.php" class="form">
            <div class="mb-3">
                <label class="form-label">First Name</label>
                <input type="text" class="form-control" name="first_name" placeholder="Enter your first name" value="<?php echo !empty($userData['first_name'])?$userData['first_name']:''; ?>" required="">
            </div>
            <div class="mb-3">
                <label class="form-label">Last Name</label>
                <input type="text" class="form-control" name="last_name" placeholder="Enter your last name" value="<?php echo !empty($userData['last_name'])?$userData['last_name']:''; ?>" required="">
            </div>
            <div class="mb-3">
                <label class="form-label">Email address</label>
                <input type="email" class="form-control" name="email" placeholder="Enter your email" value="<?php echo !empty($userData['email'])?$userData['email']:''; ?>" required="">
            </div>
            <div class="mb-3">
                <label class="form-label">Country</label>
                <input type="text" class="form-control" name="country" placeholder="Country name" value="<?php echo !empty($userData['country'])?$userData['country']:''; ?>" required="">
            </div>

            <div class="mb-3">
                <input type="hidden" name="id" value="<?php echo !empty($userData['id'])?$userData['id']:''; ?>">
                <a href="index.php" class="btn btn-secondary">Back</a>
                <input type="submit" name="userSubmit" class="btn btn-primary" value="Submit">
            </div>
        </form>
    </div>
</div>

CRUD Operations (userAction.php)

The userAction.php file is used to perform the add, edit, and delete operations using PHP and MySQL (DB class).

• Add / Edit Form Submit – If the form is submitted, the data is inserted or updated in the database based on the id field.
• Query String in URL – If action_type parameter exists in the URL, the record is deleted from the database based on ID given in the query string with id parameter.

After the data manipulation, the status is stored in SESSION and the user redirects back to the respective page.

<?php 
// Load and initialize database class 
require_once 'DB.class.php'; 
$db = new DB(); 
 
// Set default redirect url 
$redirectURL = 'index.php'; 
 
if(isset($_POST['userSubmit'])){ 
    // Get form fields value 
    $id = $_POST['id']; 
    $first_name = trim(strip_tags($_POST['first_name'])); 
    $last_name = trim(strip_tags($_POST['last_name'])); 
    $email = trim(strip_tags($_POST['email'])); 
    $country = trim(strip_tags($_POST['country'])); 
 
    $id_str = ''; 
    if(!empty($id)){ 
        $id_str = '?id='.$id; 
    } 
 
    // Fields validation 
    $errorMsg = ''; 
    if(empty($first_name)){ 
        $errorMsg .= '<p>Please enter your first name.</p>'; 
    } 
    if(empty($last_name)){ 
        $errorMsg .= '<p>Please enter your last name.</p>'; 
    } 
    if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){ 
        $errorMsg .= '<p>Please enter a valid email.</p>'; 
    } 
    if(empty($country)){ 
        $errorMsg .= '<p>Please enter country name.</p>'; 
    } 
 
    // Submitted form data 
    $userData = array( 
        'first_name' => $first_name, 
        'last_name' => $last_name, 
        'email' => $email, 
        'country' => $country 
    ); 
     
    // Store the submitted field values in the session 
    $sessData['postData'] = $userData; 
     
    // Process the form data 
    if(empty($errorMsg)){ 
        if(!empty($id)){ 
            // Update data in database 
            $condition = array('id' => $id); 
            $update = $db->update($userData, $condition); 
 
            if($update){ 
                $sessData['status']['type'] = 'success'; 
                $sessData['status']['msg'] = 'Member data has been updated successfully.'; 
                 
                // Remove submitted field values from session 
                unset($sessData['postData']); 
            }else{ 
                $sessData['status']['type'] = 'error'; 
                $sessData['status']['msg'] = 'Something went wrong, please try again.'; 
                 
                // Set redirect url 
                $redirectURL = 'addEdit.php'.$id_str; 
            } 
        }else{ 
            // Insert data in database 
            $insert = $db->insert($userData); 
 
            if($insert){ 
                $sessData['status']['type'] = 'success'; 
                $sessData['status']['msg'] = 'Member data has been added successfully.'; 
                 
                // Remove submitted field values from session 
                unset($sessData['postData']); 
            }else{ 
                $sessData['status']['type'] = 'error'; 
                $sessData['status']['msg'] = 'Something went wrong, please try again.'; 
                 
                // Set redirect url 
                $redirectURL = 'addEdit.php'.$id_str; 
            } 
        } 
    }else{ 
        $sessData['status']['type'] = 'error'; 
        $sessData['status']['msg'] = '<p>Please fill all the mandatory fields:</p>'.$errorMsg; 
         
        // Set redirect url 
        $redirectURL = 'addEdit.php'.$id_str; 
    } 
     
    // Store status into the session 
    $_SESSION['sessData'] = $sessData; 
}elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){ 
    // Delete data from database 
    $condition = array('id' => $_GET['id']); 
    $delete = $db->delete($condition); 
 
    if($delete){ 
        $sessData['status']['type'] = 'success'; 
        $sessData['status']['msg'] = 'Member data has been deleted successfully.'; 
    }else{ 
        $sessData['status']['type'] = 'error'; 
        $sessData['status']['msg'] = 'Something went wrong, please try again.'; 
    } 
     
    // Store status into the session 
    $_SESSION['sessData'] = $sessData; 
} 
 
// Redirect to the respective page 
header("Location:".$redirectURL); 
exit(); 
 
?>

PHP CRUD Operations without Page Refresh using Ajax

Conclusion

Our simple CRUD operations with PHP and MySQLi help you to implement data management (select, insert, update, and delete) functionality in the web application. Also, this example script will add pagination with search to make the CRUD data list more user-friendly. You can easily enhance the PHP CRUD with search and pagination script functionality as per your needs.