PHP CRUD with Search and Pagination

Making a CRUD system is one of the prime and regular requirements in most of the software. It is nothing but the collection functionalities that are used to create and manage data.

In this tutorial, we are going to learn how to create a PHP CRUD system with search and pagination feature. This will be explained with a simple example of creating and managing toy database data with the create, read, update and delete (CRUD) actions.

Earlier, we saw about CRUD with AJAX and CRUD without AJAX (both did not have search and pagination). As we have learned enough about PHP CRUD, let’s see the code for implementing the search and pagination in PHP CRUD.

This screenshot shows the output of the PHP CRUD with search and pagination.

Search by Column using PHP and MySQL

In this section, we are going to see the code for displaying the search option in the UI and also the PHP code to handle the search request.

Below HTML form contains two fields name and code to get the keyword from the users. Once the user enters the keyword in these fields and submits, then the search request is received in the PHP endpoint.

This form also contains the Reset button to clear the searched keyword by resetting the search form.

<form name="frmSearch" method="post" action="">
    <div>
        <p>
            <input type="text" placeholder="Name" name="search[name]"
                value="<?php echo $name; ?>" /> <input type="text"
                placeholder="Code" name="search[code]"
                value="<?php echo $code; ?>" /> <input type="submit"
                name="go" class="btnSearch" value="Search"> <input
                type="reset" class="btnReset" value="Reset"
                onclick="window.location='index.php'">
        </p>
    </div>
    <div>
        <a class="font-bold float-right" href="add.php">Add New</a>
    </div>
    <table class="stripped">
        <thead>
            <tr>
                <th>Name</th>
                <th>Code</th>
                <th>Category</th>
                <th>Price</th>
                <th>Stock Count</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
                    <?php
                    if (! empty($result)) {
                        foreach ($result as $key => $value) {
                            if (is_numeric($key)) {
                                ?>
                     <tr>
                <td><?php echo $result[$key]['name']; ?></td>
                <td><?php echo $result[$key]['code']; ?></td>
                <td><?php echo $result[$key]['category']; ?></td>
                <td><?php echo $result[$key]['price']; ?></td>
                <td><?php echo$result[$key]['stock_count']; ?></td>
                <td><a class="mr-20"
                    href="edit.php?id=<?php echo $result[$key]["id"]; ?>">Edit</a>
                    <a
                    href="delete.php?action=delete&id=<?php echo $result[$key]["id"]; ?>">Delete</a>
                </td>
            </tr>
                    <?php
                            }
                        }
                    }
                    if (isset($result["perpage"])) {
                        ?>
                        <tr>
                <td colspan="6" align=right> <?php echo $result["perpage"]; ?></td>
            </tr>
                    <?php } ?>
                    </tbody>
    </table>
</form>

In PHP, the Name and Code field data are received and used in a MySQL select query. In this query, the LIKE clause is used to filter the toy database table result based on the search request.

Below PHP code shows the search request handling which will happen after form submit. In this code, the switch case constructs the query condition based on which column the user wants to filter the database result.

<?php
require_once __DIR__ . '/lib/perpage.php';
require_once __DIR__ . '/lib/DataSource.php';
$database = new DataSource();

$name = "";
$code = "";

$queryCondition = "";
if (! empty($_POST["search"])) {
    foreach ($_POST["search"] as $k => $v) {
        if (! empty($v)) {

            $queryCases = array(
                "name",
                "code"
            );
            if (in_array($k, $queryCases)) {
                if (! empty($queryCondition)) {
                    $queryCondition .= " AND ";
                } else {
                    $queryCondition .= " WHERE ";
                }
            }
            switch ($k) {
                case "name":
                    $name = $v;
                    $queryCondition .= "name LIKE '" . $v . "%'";
                    break;
                case "code":
                    $code = $v;
                    $queryCondition .= "code LIKE '" . $v . "%'";
                    break;
            }
        }
    }
}
$orderby = " ORDER BY id desc";
$sql = "SELECT * FROM toy " . $queryCondition;
$href = 'index.php';

$perPage = 3;
$page = 1;
if (isset($_POST['page'])) {
    $page = $_POST['page'];
}
$start = ($page - 1) * $perPage;
if ($start < 0)
    $start = 0;

$query = $sql . $orderby . " limit " . $start . "," . $perPage;
$result = $database->select($query);

if (! empty($result)) {
    $result["perpage"] = showperpage($sql, $perPage, $href);
}
?>

PHP Pagination by Preserving Search Results

This section contains the PHP code to display the pagination markup below the record set of the CRUD system. This perpage function receives the total record count, per page count parameters.

Using these parameters computes the total page count. For each page, it creates the HTML markup to navigate among the pages.

The page navigations are displayed as submit buttons to preserve the searched column data. If we display the pagination as links then it will redirect the user and the user will lose the searched keyword after the redirect.

<?php

function perpage($count, $per_page = '10', $href)
{
    $output = '';
    $paging_id = "link_perpage_box";
    if (! isset($_POST["page"])) {
        $_POST["page"] = 1;
    }
    if ($per_page != 0)
        $pages = ceil($count / $per_page);

    if ($pages >= 1) {

        if (($_POST["page"] - 3) > 0) {
            if ($_POST["page"] == 1)
                $output = $output . '<span id=1 class="current-page">1</span>';
            else
                $output = $output . '<input type="submit" name="page" class="perpage-link" value="1" />';
        }
        if (($_POST["page"] - 3) > 1) {
            $output = $output . '...';
        }

        for ($i = ($_POST["page"] - 2); $i <= ($_POST["page"] + 2); $i ++) {
            if ($i < 1)
                continue;
            if ($i > $pages)
                break;
            if ($_POST["page"] == $i)
                $output = $output . '<span id=' . $i . ' class="current-page" >' . $i . '</span>';
            else
                $output = $output . '<input type="submit" name="page" class="perpage-link" value="' . $i . '" />';
        }

        if (($pages - ($_POST["page"] + 2)) > 1) {
            $output = $output . '...';
        }
        if (($pages - ($_POST["page"] + 2)) > 0) {
            if ($_POST["page"] == $pages)
                $output = $output . '<span id=' . ($pages) . ' class="current-page">' . ($pages) . '</span>';
            else
                $output = $output . '<input type="submit" name="page" class="perpage-link" value="' . $pages . '" />';
        }
    }
    return $output;
}

function showperpage($sql, $per_page = 10, $href)
{
    require_once __DIR__ . '/DataSource.php';
    $db_handle = new DataSource();
    $count = $db_handle->getRecordCount($sql);
    $perpage = perpage($count, $per_page, $href);
    return $perpage;
}
?>

CRUD operations

This PHP class is a DAO that handles database connection establishment and query execution. The run query function receives the SELECT query statement and executes it to get the database resource ident.

With the reference of the resource id, it retrieves the record set and iterates it to construct the resultant array to be returned to the CRUD system.

add.php

<?php
require_once __DIR__ . '/lib/DataSource.php';
$database = new DataSource();
if (isset($_POST["submit"])) {
    $sql = "INSERT INTO toy(name, code, category, price, stock_count) VALUES(?, ?, ?, ?, ?)";
    $paramType = 'sssdi';
    $paramValue = array(
        $_POST["name"],
        $_POST["code"],
        $_POST["category"],
        $_POST["price"],
        $_POST["stock_count"]
    );
    $result = $database->insert($sql, $paramType, $paramValue);
    if (! $result) {
        $message = "problem in Adding to database. Please Retry.";
    } else {
        header("Location:index.php");
    }
}
?>
<html>
<head>

<link href="css/style.css" type="text/css" rel="stylesheet" />
<link href="css/form.css" type="text/css" rel="stylesheet" />
<script src="https://code.jquery.com/jquery-2.1.1.min.js"
    type="text/javascript"></script>
<script src="./js/validation.js" type="text/javascript"></script>
</head>
<body>
    <div class="phppot-container tile-container text-center">
        <form name="frmToy" method="post" action="" id="frmToy"
            onClick="return validate();">
            <?php if(! empty($message)){?>
            <div class="error">
                <?php echo $message;?>
                </div><?php }?>
            <h1>Add Record</h1>
            <div class="row">
                <label class="text-left">Name: <span id="name-info"
                    class="validation-message"></span></label><input
                    type="text" name="name" id="name"
                    class="full-width ">
            </div>
            <div class="row">
                <label class="text-left">Code: <span id="code-info"
                    class="validation-message"></span></label> <input
                    type="text" name="code" id="code"
                    class="full-width ">
            </div>
            <div class="row">
                <label class="text-left">Category: <span
                    id="category-info" class="validation-message"></span></label><input
                    type="text" name="category" id="category"
                    class="full-width ">
            </div>
            <div class="row">
                <label class="text-left">Price: <span id="price-info"
                    class="validation-message"></span></label><input
                    type="text" name="price" id="price"
                    class="full-width">
            </div>
            <div class="row">
                <label class="text-left">Stock Count: <span
                    id="stock-count-info" class="validation-message"></span></label><input
                    type="text" name="stock_count" id="stock_count"
                    class="full-width ">
            </div>
            <div class="row">
                <input type="submit" name="submit" id="btnAddAction"
                    class="full-width " value="Add" />
            </div>
        </form>
    </div>
</body>
</html>

The edit form and its PHP action will be same. You can find the edit code in the source code download at the end of this article.

Next, the delete operation is a simple one. It forms the DELETE query with condition to delete the selected record.

This PHP script specifies a particular record id with a WHERE condition and append it to the DELETE query.

The DataSource custom library method is called to execute the delete action.

delete.php

<?php
require_once __DIR__ . '/lib/DataSource.php';
$database = new DataSource();
$sql = "DELETE FROM toy WHERE id=?";
$paramType = 'i';
$paramValue = array(
    $_GET["id"]
);
$database->delete($sql, $paramType, $paramValue);
header("Location:index.php");
exit();
?>

MySQL Database Script

The following SQL script contains the toy database table’s create statement. By using this you can create the required database structure in your local environment to run this example.

CREATE TABLE IF NOT EXISTS `toy` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `code` varchar(20) NOT NULL,
  `category` varchar(255) NOT NULL,
  `price` double NOT NULL,
  `stock_count` bigint(16) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `toy` (`id`, `name`, `code`, `category`, `price`, `stock_count`) VALUES
(9, 'Music Aeroplane', 'TOY#MA01', 'Music Toys', 250, 500),
(10, 'Power Ranger', 'TOY#BT01', 'Battery Toys', 1000, 100),
(11, 'Remote Car', 'TOY#RMT01', 'Remote Control Toys', 280, 800),
(12, 'Bubbles', 'TOY#WT01', 'Water Games', 100, 1000),
(13, 'Cricket Cards', 'TOY#CD01', 'Card Games', 200, 80),
(14, 'Basket Ball', 'TOY#BB01', 'Outdoor Toys', 2000, 500),
(15, 'Word Puzzles', 'TOY#PZ01', 'Puzzles', 200, 200),
(16, 'Water Gun', 'TOY#WG01', 'Water Games', 100, 1000);

Download

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
    • URI Shortener PHP Script
    • Create Shortener to Convert Long URL to Short in JavaScript
    • How to Add CSRF Token to AJAX Request
    • PHP MySQL Date Range Search with jQuery DatePicker
    • Advanced Search using PHP
    • PHP CRUD with Search and Pagination
    • Highlighting Keywords in Search Results with PHP
    • PHP Pagination MySQL Database Example Script with Previous Next like Google
    • Command Line PHP
    • How to Create Custom Image File Explorer in PHP
    • Web Scraping with PHP - Tutorial to Scrape Web Pages
    • User Login Session Timeout Logout in PHP
    • PHP Change Password Script
    • Object-Oriented Poll System using PHP
    • PHP cURL
    • Extract Content using PHP and Preview like Facebook
    • Show Image Thumbnail by YouTube Video URL using PHP
    • Star Rating with PHP and jQuery AJAX
    • Simple HTML 5 star rating code
    • How to Crop Image using jQuery and PHP
    • How to Generate Initial Avatar Image from Username using PHP Dynamically
    • Live search in PHP and MySQL with AJAX
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js