PHP CRUD with Search and Pagination

by Vincy. Last modified on September 14th, 2022.

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.

php search pagination crud

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

↑ Back to Top

Share this page