loading

How to PHP CRUD Operations with Search and Pagination

How to PHP CRUD Operations with Search and Pagination

How to PHP CRUD Operations with Search and Pagination

0 Sales

Free

The most often utilised capability in a web application is the CRUD activity. CRUD capability is used by almost every online application to maintain and manipulate data in a database. PHP with MySQL may be used to quickly construct CRUD (Create, Read, Update, and Delete) capabilities. PHP CRUD with MySQL facilitates the viewing, adding, editing, and deleting of data from a database.

Search and Pagination are incredibly important features for making the data list more user-friendly. When adding CRUD capability to a website, search and pagination are essential features for the CRUD data list. In this article, we will teach you how to use MySQL to build PHP CRUD operations with search and pagination.


The following functionality will be implemented in the sample code to connect CRUD with a search filter and pagination in PHP.

 - Retrieve and list the users' information from the database.
 - Pagination should be added to the data list.
 - Filter and search through records.
 - Insert user information into the database.
 - Edit and modify user data in the database.
 - Delete the database record.

Create Database Table

A table in the database must be built to contain the user's information. In the MySQL database, the following SQL generates a customers table with some basic columns.

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,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1' COMMENT '1=Active | 0=Inactive',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Database Class (DB.class.php)

The DB class is in charge of all database operations.

 - __construct() – Establish a connection to the MySQL database server. As per your database credentials, enter the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName).
 - getRows() – Fetch entries from the database depending on the conditions you provide.
 - insert() – Fill in the blanks in the database.
 - update() – Update database records based on the provided circumstances.
 - delete() – Delete entries from the database depending on the conditions you provide.

<?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
 */
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
            $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 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("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 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");
            }
            foreach($data as $key=>$val){
                $pre = ($i 0)?', ':'';
                $columns .= $pre.$key;
                $values  .= $pre."'".$val."'";
                $i++;
            }
            $query "INSERT INTO ".$table." (".$columns.") VALUES (".$values.")";
            $insert $this->db->query($query);
            return $insert?$this->db->insert_id: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++;
                }
            }
            $query "UPDATE ".$table." SET ".$colvalSet.$whereSql;
            $update $this->db->query($query);
            return $update?$this->db->affected_rows: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++;
            }
        }
        $query "DELETE FROM ".$table.$whereSql;
        $delete $this->db->query($query);
        return $delete?true:false;
    }
}

Pagination Class (Pagination.class.php)

The Pagination class is used to construct links that govern data list paging. You may examine all of the PHP Pagination class's setup options and references.

Data list with Search and Pagination (index.php)

Initially, all of the users' data is fetched from the database and shown on the homepage with links to Add, Edit, and Delete. Additionally, search and pagination capabilities have been added to the CRUD data list.

 - The Add link takes the user to the addEdit.php page, where they may conduct the Create activity.
 - The Edit link takes the user to the addEdit.php page with the appropriate ID in order to complete the Update action.
 - The Delete link takes the user to the userAction.php file, where the Delete operation is performed using the action type and id parameters.

 - The Search option allows you to sort the record set based on the user's name/email/phone number.

      - Get search terms from the URL.
      - Enter the search phrase in the like or parameter of the getRows() method.
      - Return data that has been filtered based on the keywords.

 - Pagination aids in retrieving a restricted amount of entries from a database and accessing info via links.

      - Set the offset and the limit.
      - Initialize the Pagination class and give configuration parameters (Base URL, Total Rows Count, and Per Page Limit).
      - To render the pagination links, use the Pagination class's createLinks() function.

<?php

// Start session
session_start();

// 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']);
}

// Load pagination class
require_once 'Pagination.class.php';

// Load and initialize database class
require_once 'DB.class.php';
$db = new DB();

// Page offset and limit
$perPageLimit 2;
$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(
        'name' => $searchKeyword,
        'email' => $searchKeyword,
        'phone' => $searchKeyword
    );
}

// Get count of the users
$con = array(
    'like_or' => $searchArr,
    'return_type' => 'count'
);
$rowCount $db->getRows('users'$con);

// Initialize pagination class
$pagConfig = array(
    'baseURL' => 'index.php'.$searchStr,
    'totalRows' => $rowCount,
    'perPage' => $perPageLimit
);
$pagination = new Pagination($pagConfig);

// Get users from database
$con = array(
    'like_or' => $searchArr,
    'start' => $offset,
    'limit' => $perPageLimit,
    'order_by' => 'id DESC',
);
$users $db->getRows('users'$con);

?>

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

<div class="row">
    <div class="col-md-12 search-panel">
        <!-- Search form -->
        <form>
        <div class="input-group">
            <input type="text" name="sq" class="form-control" placeholder="Search by keyword..." value="<?php echo $searchKeyword?>">
            <div class="input-group-btn">
                <button class="btn btn-default" type="submit">
                    <i class="glyphicon glyphicon-search"></i>
                </button>
            </div>
        </div>
        </form>
        
        <!-- Add link -->
        <span class="pull-right">
            <a href="addEdit.php" class="btn btn-primary"><i class="glyphicon glyphicon-plus"></i> New User</a>
        </span>
    </div>
    
    <!-- Data list table --> 
    <table class="table table-striped table-bordered">
        <thead>
            <tr>
                <th></th>
                <th>Name</th>
                <th>Email</th>
                <th>Phone</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
            <?php
            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>
                    <a href="addEdit.php?id=<?php echo $user['id']; ?>" class="glyphicon glyphicon-edit"></a>
                    <a href="userAction.php?action_type=delete&id=<?php echo $user['id']; ?>" class="glyphicon glyphicon-trash" onclick="return confirm('Are you sure to delete?')"></a>
                </td>
            </tr>
            <?php } }else{ ?>
            <tr><td colspan="5">No user(s) found......</td></tr>
            <?php ?>
        </tbody>
    </table>
    
    <!-- Display pagination links -->
    <?php echo $pagination->createLinks(); ?>
</div>

Create & Update Records (addEdit.php)

The addEdit.php file contains the HTML form that is used to collect user input for adding and editing data.

 - The form input is initially provided to the PHP script (userAction.php) in order to insert records into the users table.
 - If the ID parameter is present on the URL, the existing user data is obtained from the database using this ID and pre-filled in the input forms. The data is passed to the PHP script (userAction.php) in order to change an existing entry in the users database.
 - If an error occurs, an error notice is displayed, and the pre-filled data is presented in the form fields.

<?php

// Start session
session_start();

$postData $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 posted data from session
if(!empty($sessData['postData'])){
    $postData $sessData['postData'];
    unset($_SESSION['sessData']['postData']);
}

// Get user data
if(!empty($_GET['id'])){
    include 'DB.class.php';
    $db = new DB();
    $conditions['where'] = array(
        'id' => $_GET['id'],
    );
    $conditions['return_type'] = 'single';
    $userData $db->getRows('users'$conditions);
}

// Pre-filled data
$userData = !empty($postData)?$postData:$userData;

// Define action
$actionLabel = !empty($_GET['id'])?'Edit':'Add';

?>

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

<!-- Add/Edit form -->
<div class="panel panel-default">
    <div class="panel-heading"><?php echo $actionLabel?> User <a href="index.php" class="glyphicon glyphicon-arrow-left"></a></div>
    <div class="panel-body">
        <form method="post" action="userAction.php" class="form">
            <div class="form-group">
                <label>Name</label>
                <input type="text" class="form-control" name="name" value="<?php echo !empty($userData['name'])?$userData['name']:''?>">
            </div>
            <div class="form-group">
                <label>Email</label>
                <input type="text" class="form-control" name="email" value="<?php echo !empty($userData['email'])?$userData['email']:''?>">
            </div>
            <div class="form-group">
                <label>Phone</label>
                <input type="text" class="form-control" name="phone" value="<?php echo !empty($userData['phone'])?$userData['phone']:''?>">
            </div>
            <input type="hidden" name="id" value="<?php echo !empty($userData['id'])?$userData['id']:''?>">
            <input type="submit" name="userSubmit" class="btn btn-success" value="SUBMIT"/>
        </form>
    </div>
</div>

 

CRUD Operations (userAction.php)

The userAction.php file is used to conduct PHP and MySQL add, modify, and delete operations (DB class).

 - Add / Edit Form Submit — When the form is submitted, the data in the database is inserted or changed based on the id field.
 - Query String in URL — If the action type parameter is present in the URL, the record is removed from the database based on the ID specified in the query string with the id parameter.

Following data modification, the status is saved in SESSION and the user is sent to the appropriate page.

<?php

// Start session
session_start();

// Load and initialize database class
require_once 'DB.class.php';

$db = new DB();

$tblName 'users';

// Set default redirect url
$redirectURL 'index.php';

if(isset($_POST['userSubmit'])){

    // Get submitted data
    $name   $_POST['name'];
    $email  $_POST['email'];
    $phone  $_POST['phone'];
    $id     $_POST['id'];
    
    // Submitted user data
    $userData = array(
        'name'  => $name,
        'email' => $email,
        'phone' => $phone
    );
    
    // Store submitted data into session
    $sessData['postData'] = $userData;
    $sessData['postData']['id'] = $id;
    
    // ID query string
    $idStr = !empty($id)?'?id='.$id:'';
    
    // If the data is not empty
    if(!empty($name) && !empty($email) && !empty($phone)){
        if(filter_var($emailFILTER_VALIDATE_EMAIL)){
            if(!empty($id)){
                // Update data
                $condition = array('id' => $id);
                $update $db->update($tblName$userData$condition);
                
                if($update){
                    $sessData['postData'] = '';
                    $sessData['status']['type'] = 'success';
                    $sessData['status']['msg']  = 'User data has been updated successfully.';
                }else{
                    $sessData['status']['type'] = 'error';
                    $sessData['status']['msg']  = 'Some problem occurred, please try again.';
                    
                    // Set redirect url
                    $redirectURL 'addEdit.php'.$idStr;
                }
            }else{
                // Insert data
                $insert $db->insert($tblName$userData);
                
                if($insert){
                    $sessData['postData'] = '';
                    $sessData['status']['type'] = 'success';
                    $sessData['status']['msg']  = 'User data has been added successfully.';
                }else{

                    $sessData['status']['type'] = 'error';
                    $sessData['status']['msg']  = 'Some problem occurred, please try again.';
                    
                    // Set redirect url
                    $redirectURL 'addEdit.php';
                }
            }
        }else{
            $sessData['status']['type'] = 'error';
            $sessData['status']['msg']  = 'Please enter a valid email address.';
            
            // Set redirect url
            $redirectURL 'addEdit.php'.$idStr;
        }
    }else{
        $sessData['status']['type'] = 'error';
        $sessData['status']['msg']  = 'All fields are mandatory, please fill all the fields.';
        
        // Set redirect url
        $redirectURL 'addEdit.php'.$idStr;
    }
    
    // Store status into the session
    $_SESSION['sessData'] = $sessData;
}elseif(($_REQUEST['action_type'] == 'delete') && !empty($_GET['id'])){
    // Delete data
    $condition = array('id' => $_GET['id']);
    $delete $db->delete($tblName$condition);
    if($delete){
        $sessData['status']['type'] = 'success';
        $sessData['status']['msg']  = 'User data has been deleted successfully.';
    }else{
        $sessData['status']['type'] = 'error';
        $sessData['status']['msg']  = 'Some problem occurred, please try again.';
    }
    
    // Store status into the session
    $_SESSION['sessData'] = $sessData;
}

// Redirect the user
header("Location: ".$redirectURL);
exit();

Conclusion

Our easy CRUD operations with PHP and MySQLi assist you in implementing data management capabilities (select, insert, update, and delete) in the online application. In addition, to make the CRUD data list more user-friendly, this sample script will provide pagination with search. You may simply add search and pagination script features to the PHP CRUD to meet your specific demands.

LICENSE OF USE

You can use it for personal or commercial projects. You can't resell it partially or in this form.

PRODUCT INFO

Create Date : Feb 16, 2022

Updated Date : Feb 16, 2022

Ratings

Comments : 0

Downloads : 0