PHP CRUD with Search and Pagination using jQuery AJAX

by Vincy. Last modified on June 19th, 2023.

This tutorial is for doing database operations with search and pagination. It uses jQuery AJAX to perform all the operations. In the previous tutorial, we have seen the CRUD, search, and pagination without AJAX.

This example has AJAX functions sending requests to the PHP endpoint. The request parameters vary about the add/edit or search/pagination request raised via AJAX.

jQuery AJAX functions to create, read, update, delete, search and pagination

This JavaScript asset has the functions to perform database operations. It also has the handlers to achieve the AJAX search and pagination using jQuery and PHP.

This JS file is essential in this example because the database operations with PHP will be the same for any crud implementation.

assets/js/crud.js

function getresult(url) {
	$('#loader').show();
	$.ajax({
		url: url,
		type: "POST",
		data: { rowcount: $("#rowcount").val(), name: $("#name").val(), code: $("#code").val() },
		success: function(data) {
			$("#toys-grid").html(data); $('#add-form').hide(); $('#loader').hide();
		}
	});
}
getresult("getresult.php");

function add() {
	var valid = addValidate();
	if (valid) {
		$('#add-loader').show();
		$("input[type=button]#btnAddAction").hide();
		$.ajax({
			url: "add.php",
			type: "POST",
			data: { name: $("#add-name").val(), code: $("#add-code").val(), category: $("#category").val(), price: $("#price").val(), stock_count: $("#stock_count").val() },
			success: function(data) {
				getresult("getresult.php");
				$('#add-loader').hide();
				$("#frmToy")[0].reset();
				$("input[type=button]#btnAddAction").show();
			}
		});
	}
}

function showEdit(obj, id) {
	$(obj).closest('td').find('#action-loader').show();
	$.ajax({
		url: "show_edit.php?id=" + id,
		type: "POST",
		success: function(data) { $("#toy-" + id).html(data); $(obj).closest('td').find('#action-loader').hide(); }
	});
}

function edit(obj, id) {
	var valid = editValidate();
	if (valid) {
		$(obj).closest('div').find('#edit-loader').show();
		$(obj).closest('div').find("input[type=button]#btnEditAction").hide();
		$.ajax({
			url: "edit.php?id=" + id,
			type: "POST",
			data: { name: $("#edit-name").val(), code: $("#edit-code").val(), category: $("#edit-category").val(), price: $("#edit-price").val(), stock_count: $("#edit-stock").val() },
			success: function(data) { $("#toy-" + id).html(data); $(obj).closest('div').find('#edit-loader').hide(); }
		});
	}
}

function del(obj, id) {
	if (confirm("Are you sure want to delete?")) {
		$(obj).closest('td').find('#action-loader').show();
		$.ajax({
			url: "delete.php?id=" + id,
			type: "POST",
			success: function(data) { $("#toy-" + id).html(''); $(obj).closest('td').find('#action-loader').hide(); }
		});
	}
}

function addValidate() {
	var valid = true;
	$(".req-field").html('');

	if (!$("#add-name").val()) {
		$("#name-info").html("required.");
		valid = false;
	}
	if (!$("#add-code").val()) {
		$("#code-info").html("required.");
		valid = false;
	}
	if (!$("#category").val()) {
		$("#category-info").html("required.");
		valid = false;
	}
	if (!$("#price").val()) {
		$("#price-info").html("required.");
		valid = false;
	}
	if (!$("#stock_count").val()) {
		$("#stock_count-info").html("required.");
		valid = false;
	}
	return valid;
}

function editValidate(obj) {
	var valid = true;
	$(".required").html('');

	if (!$("#edit-name").val()) {
		$("#name-error-info").html("required.");
		valid = false;
	}
	if (!$("#edit-code").val()) {
		$("#code-error-info").html("required.");
		valid = false;
	}
	if (!$("#edit-category").val()) {
		$("#category-error-info").html("required.");
		valid = false;
	}
	if (!$("#edit-price").val()) {
		$("#price-error-info").html("required.");
		valid = false;
	}
	if (!$("#edit-stock").val()) {
		$("#stock-error-info").html("required.");
		valid = false;
	}
	return valid;
}

In this function, we are sending total rows for creating pagination links. We are also sending the search keywords corresponding to the name and code column of the database.

In PHP page, these jQuery AJAX parameters are used to create HTML for listing database results.

We are getting form inputs for creating data string to be passed to the PHP file. In add() and edit() functions we are calling jQuery validation before sending form inputs to a PHP file.

PHP script to perform database reading with search and pagination

This section prepares the MySQL query in PHP with the search and pagination limits.

It sends the param for the WHERE and the ORDER BY clauses of the SELECT statement.

getResult.php

<?php
require_once __DIR__ . "/lib/DataSource.php";
require_once __DIR__ . "/lib/pagination.class.php";
$database = new DataSource();

$perPage = new PerPage();

$queryCondition = "";
if (! empty($_POST["name"])) {
    $queryCondition .= " WHERE name LIKE '%" . $_POST["name"] . "%'";
}

if (! empty($_POST["code"])) {
    if (! empty($queryCondition)) {
        $queryCondition .= " AND ";
    } else {
        $queryCondition .= " WHERE ";
    }
    $queryCondition .= " code LIKE '%" . $_POST["code"] . "%'";
}

$orderby = " ORDER BY id desc";
$sql = "SELECT * FROM toy " . $queryCondition;
$paginationlink = "getresult.php?page=";
$page = 1;
if (! empty($_GET["page"])) {
    $page = $_GET["page"];
}

$start = ($page - 1) * $perPage->perpage;
if ($start < 0)
    $start = 0;

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

if (empty($_GET["rowcount"])) {
    $_GET["rowcount"] = $database->getRecordCount($sql);
}
$perpageresult = $perPage->perpage($_GET["rowcount"], $paginationlink);
?>

<form name="frmSearch" method="post" action="index.php">
	<div>
		<p>
			<input type="hidden" id="rowcount" name="rowcount"
				value="<?php echo $_GET["rowcount"]; ?>" /><input type="text"
				placeholder="Name" name="name" id="name" class="search-input"
				value="<?php if(! empty($_POST["name"])){echo $_POST["name"];} ?>" /><input
				type="text" placeholder="Code" name="code" id="code"
				class="search-input"
				value="<?php if(! empty($_POST["code"])){ echo $_POST["code"]; }?>" /><input
				type="button" name="go" class="btnSearch" value="Search"
				onclick="getresult('<?php echo $paginationlink . $page; ?>')"> <input
				type="button" class="btnReset" value="Reset"
				onclick="window.location='index.php'"> <span class="display-none"><img
				alt='Loader' src='spinner.svg' id="loader"></span>
		</p>
	</div>
	<div>
		<a id="btnAddAction" class="font-bold float-right cursor-pointer"
			onClick="$('#add-form').show();">Add New</a>
	</div>
	<table class="stripped">
		<thead>
			<tr>
				<th><strong>Name</strong></th>
				<th><strong>Code</strong></th>
				<th><strong>Category</strong></th>
				<th><strong>Price</strong></th>
				<th><strong>Stock Count</strong></th>
				<th><strong>Action</strong></th>

			</tr>
		</thead>
		<tbody>
<?php
if (! empty($result)) {
    foreach ($result as $k => $v) {
        ?>
<tr id="toy-<?php echo $result[$k]["id"]; ?>">
				<td><?php echo $result[$k]["name"]; ?></td>
				<td><?php echo $result[$k]["code"]; ?></td>
				<td><?php echo $result[$k]["category"]; ?></td>
				<td><?php echo $result[$k]["price"]; ?></td>
				<td><?php echo $result[$k]["stock_count"]; ?></td>
				<td><a class="mr-20 cursor-pointer"
					onClick="showEdit(this,<?php echo $result[$k]["id"]; ?>)">Edit</a>
					<a class="cursor-pointer"
					onClick="del(this,<?php echo $result[$k]["id"]; ?>)">Delete</a> <span
					class="display-none" id="action-loader"> <img alt='Loader'
						src='spinner.svg' class='align-middle'>
				</span></td>
			</tr>
<?php
    }
}
if (isset($perpageresult)) {
    ?>
<tr>
				<td colspan="6" align=right> <?php echo $perpageresult; ?></td>
			</tr>
<?php } ?>		
		
		
		
		<tbody>
	
	</table>
</form>

Other PHP MySQL endpoints to do the CRUD operations

add.php

<?php
require_once __DIR__ . "/lib/DataSource.php";
$database = new DataSource();
$query = "INSERT INTO toy (name, code, category, price, stock_count) VALUES (?, ?, ?, ?, ?)";
$paramType = 'sssii';
$paramValue = array(
    $_POST["name"],
    $_POST["code"],
    $_POST["category"],
    $_POST["price"],
    $_POST["stock_count"]
);
$insertId = $database->insert($query, $paramType, $paramValue);
?>

edit.php

<?php
require_once __DIR__ . "/lib/DataSource.php";
$database = new DataSource();
$sql = "UPDATE toy SET name=?, code=?, category=?, price=?, stock_count=? WHERE id=?";
$paramType = 'sssdii';
$paramValue = array(
    $_POST["name"],
    $_POST["code"],
    $_POST["category"],
    $_POST["price"],
    $_POST["stock_count"],
    $_GET["id"]
);
$updateId = $database->execute($sql, $paramType, $paramValue);
$sql = "SELECT * FROM toy WHERE id=?";
$paramType = 'i';
$paramValue = array(
    $_GET["id"]
);
$result = $database->select($sql, $paramType, $paramValue);
?>
<td><?php echo $result[0]["name"]; ?></td>
<td><?php echo $result[0]["code"]; ?></td>
<td><?php echo $result[0]["category"]; ?></td>
<td><?php echo $result[0]["price"]; ?></td>
<td><?php echo $result[0]["stock_count"]; ?></td>
<td class="action"><a class="mr-20 cursor-pointer"
	onClick="showEdit(this,<?php echo $_GET["id"]; ?>)">Edit</a> <a
	class="btnDeleteAction cursor-pointer"
	onClick="del(this,<?php echo $_GET["id"]; ?>)">Delete</a></td>

delete.php

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

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

Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.

I'm currently available for freelance work.

vincy@phppot.com

PHP CRUD with Search and Pagination using jQuery AJAX

jQuery AJAX functions to create, read, update, delete, search and pagination

PHP script to perform database reading with search and pagination

Other PHP MySQL endpoints to do the CRUD operations

Related Tutorials

Looking for an expert PHP freelance web developer?