We are going to build a basic AJAX CRUD app from scratch using only PHP, MySQL and jQuery AJAX. CRUD is an acronym for Create, Read, Update, Delete. Basic operations that are at the heart of all web apps.
We will use Bootstrap for the layout and utilize other jQuery plugins for the UI.
But first, the file structure.
/db – The directory that has all the server and database related files
- env.php – environment settings for database, server and miscellaneous display options
- generate-db.php – creates a database based on the details set in the env. file
- create-table.php -creates a table based on the details set in the env. file
- connect-db.php – global include for databases connection
/api – The directory that has all endpoint related files
- create.php – all the code and mySQL statements for inserting records
- delete.php – all the code and mySQL statements for deleting records
- index.php – all the code and mySQL statements for showing rows based on the current page
- update.php – all the code and mySQL statements for updating records
/js/index.js – The file that handles all page events
- displayBookList() – displays the initial list of all books with pagination
- displayPageEntries() – displays books for the current page
- refreshList(data) – universal function to refresh the content of the table, takes in data.
- View book – has all the code to display a record
- Create book – code to add a new record
- Edit book – all the code to fill in the edit form with current data
- Update book – code to update a record
/index.php – Is the main file that contains all elements, our app will use modal windows to display the forms.
Let’s start.
Step 1: Take care of the database. These files are for global use but create-table.php and generate-db.php are used only once.
.env.php
<?php // Database info $server = "localhost"; $user = "root"; $pass = ""; $db_name = "awesomedb"; $table_name = "books"; // Server URL $url = "http://localhost/"; $display_rows = 10; ?>
generate-db.php
<?php
include_once("env.php");
// Create connection
$connection = new mysqli($server, $user, $pass);
// Check connection
if ($connection->connect_error) {
die("ERROR: Could not connect " . $connection->connect_error);
}
// Create new database
$sql = "CREATE DATABASE `$db_name`";
if($connection->query($sql) === TRUE){
echo "Database created successfully";
} else {
echo "ERROR: Unable to execute $sql. " . $connection->error;
}
// Close connection
$connection->close();
?>
create-table.php
<?php
include_once("connect-db.php");
// Create new table
$sql = "CREATE TABLE `$table_name` (
id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(191) NOT NULL,
author VARCHAR(191) NOT NULL,
published_date DATE,
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
status ENUM('active', 'pending', 'deleted')
)";
if($connection->query($sql) === TRUE){
echo "Table '".$table_name."' created successfully";
} else {
echo "Error creating table: " . $connection->error;
}
// Close connection
$connection->close();
?>
connect-db.php
<?php
include_once("env.php");
// Create connection
$connection = new mysqli($server, $user, $pass, $db_name);
// Check connection
if ($connection->connect_error) {
die("ERROR: Could not connect " . $connection->connect_error);
}
?>
Step 2: Prepare the endpoints. These files run the mySQL queries for the operations. This is where the magic actually happens.
create.php
<?php
require_once('../db/connect-db.php');
// Add new record
$sql = "INSERT INTO `$table_name` ( `title`, `author`, `published_date`) VALUES ( '".$_POST['title']."', '".$_POST['author']."', '".$_POST['published_date']."')";
$result = $connection->query($sql);
// Return value
$sql = "SELECT * FROM $table_name ORDER BY id DESC LIMIT 1";
$result = $connection->query($sql);
$data = $result->fetch_assoc();
echo json_encode($data);
?>
index.php
<?php
require_once('../db/connect-db.php');
$page = (isset($_GET["page"])) ? $_GET["page"] : 1;
$start_from = ($page-1) * $display_rows;
// Get all rows for selected page
$sql = "SELECT * FROM $table_name ORDER BY id DESC LIMIT $start_from, $display_rows";
$result = $connection->query($sql);
while($row = $result->fetch_assoc()){
$json[] = $row;
}
$data['data'] = $json;
// Get total count of all books
$sql = "SELECT COUNT(*) AS total FROM $table_name";
$result = $connection->query($sql);
$values = mysqli_fetch_assoc($result);
$data['total'] = $values['total'];
// Return books
echo json_encode($data);
?>
update.php
<?php
require_once('../db/connect-db.php');
// Update record
$sql = "UPDATE `$table_name` SET `title` = '".$_POST['title']."', `author` = '".$_POST['author']."', `published_date` = '".$_POST['published_date']."' WHERE `id` = ".$_POST['id']."";
$result = $connection->query($sql);
// Return value
$sql = "SELECT * FROM $table_name WHERE `id` = '".$_POST['id']."'";
$result = $connection->query($sql);
$data = $result->fetch_assoc();
echo json_encode($data);
?>
delete.php
<?php
require_once('../db/connect-db.php');
$id = $_POST["id"];
// Delete record
$sql = "DELETE FROM $table_name WHERE id = '".$id."'";
if($connection->query($sql) === TRUE){
echo json_encode([$id]);
} else {
echo "Error deleting record " . $connection->error;
}
// Close connection
$connection->close();
?>
Step 3: Design the user interface. Page elements like lists, pagination, buttons and modal windows allows for user interaction. We used a few jQuery plugins to improve the UI like twbsPagination, bootstrap-validator, jQueryUI and toast to display notifications
index.php
<?php require_once("db/env.php"); ?>
<!doctype html>
<html lang="en">
<head>
<!-- Meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<!-- JS -->
<script src="https://code.jquery.com/jquery-3.5.1.min.js" integrity="sha256-9/aliU8dGd2tb6OSsuzixeV4y/faTqgFtohetphbbj0=" crossorigin="anonymous"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twbs-pagination/1.4.2/jquery.twbsPagination.min.js" integrity="sha512-frFP3ZxLshB4CErXkPVEXnd5ingvYYtYhE5qllGdZmcOlRKNEPbufyupfdSTNmoF5ICaQNO6SenXzOZvoGkiIA==" crossorigin="anonymous"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/1000hz-bootstrap-validator/0.11.5/validator.min.js"></script>
<script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/toastr.js/latest/js/toastr.min.js"></script>
<link href="//cdnjs.cloudflare.com/ajax/libs/toastr.js/latest/css/toastr.min.css" rel="stylesheet">
<!-- Custom JS -->
<script type="text/javascript">
var url = "<?php echo $url; ?>";
var display_rows = "<?php echo $display_rows; ?>";
$(function(){
$("#published_date").datepicker({ dateFormat: 'yy-mm-dd' });
$("#published_date_edit").datepicker({ dateFormat: 'yy-mm-dd' });
});
</script>
<!-- API JS files -->
<script src="js/index.js"></script>
<title>Trial Project for Worker Bee TV - Submitted by Jay Acab chiefofstack.com </title>
</head>
<body>
<div class="container">
<div class="row mt-4">
<div class="col-lg-12">
<div class="float-left">
<h2>Books</h2>
</div>
<div class="float-right">
<button type="button" class="btn btn-success" data-toggle="modal" data-target="#create-book">
Add New Book
</button>
</div>
</div>
</div>
<table class="table table-bordered table-striped text-muted text-center mt-3">
<thead>
<tr>
<th>Date Published</th>
<th>Author name</th>
<th>Book Title</th>
<th>Action</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<ul id="pagination" class="pagination-sm"></ul>
</div>
<!-- View Book Modal -->
<div id="view-book" class="modal" tabindex="-1" role="dialog" aria-labelledby="modalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<div>
<h4 class="modal-title float-left" id="modalLabel">Book Information</h4>
</div>
<div>
<button type="button" class="close float-right" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
</div>
</div>
<div class="modal-body">
<form id="view-book-form" data-toggle="validator" action="api/create.php" method="POST">
<div class="form-group">
<label class="control-label" for="title">Title:</label>
<p id="title_view" name="title" class="font-weight-bold" /></p>
</div>
<div class="form-group">
<label class="control-label" for="author">Author:</label>
<p id="author_view" name="author" class="font-weight-bold"/></p>
</div>
<div class="form-group">
<label class="control-label" for="published_date">Date Published:</label>
<p id="published_date_view" name="published_date" class="font-weight-bold" /></p>
</div>
<div class="form-group">
<label class="control-label" for="id">ID:</label>
<p id="id_view" name="id" class="font-weight-bold" /></p>
</div>
</form>
</div>
</div>
</div>
</div>
<!-- Create Book Modal -->
<div id="create-book" class="modal" tabindex="-1" role="dialog" aria-labelledby="modalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<div>
<h4 class="modal-title float-left" id="modalLabel">Add a Book</h4>
</div>
<div>
<button type="button" class="close float-right" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
</div>
</div>
<div class="modal-body">
<form id="create-book-form" data-toggle="validator" action="api/create.php" method="POST">
<div class="form-group">
<label class="control-label" for="title">Title:</label>
<input type="text" id="title" name="title" class="form-control" data-error="Please enter book title." required />
<div class="help-block with-errors text-danger"></div>
</div>
<div class="form-group">
<label class="control-label" for="author">Author:</label>
<input type="text" id="author" name="author" class="form-control" data-error="Please enter the name of the author." required>
<div class="help-block with-errors text-danger"></div>
</div>
<div class="form-group">
<label class="control-label" for="published_date">Date Published:</label>
<input type="text" id="published_date" name="published_date" class="form-control" data-error="Please enter the date when the book was published." required autocomplete="off">
<div class="help-block with-errors text-danger"></div>
</div>
<div class="form-group mt-4">
<button type="submit" class="btn submit-book btn-success">Submit</button>
</div>
</form>
</div>
</div>
</div>
</div>
<!-- Edit Book Modal -->
<div id="edit-book" class="modal" tabindex="-1" role="dialog" aria-labelledby="modalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<div>
<h4 class="modal-title float-left" id="modalLabel">Edit Book</h4>
</div>
<div>
<button type="button" class="close float-right" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
</div>
</div>
<div class="modal-body">
<form id="edit-book-form" data-toggle="validator" action="api/update.php" method="put">
<input type="hidden" id="id" name="id" class="edit-id">
<div class="form-group">
<label class="control-label" for="title_edit">Title:</label>
<input type="text" id="title_edit" name="title" class="form-control" data-error="Please enter book title." required />
<div class="help-block with-errors text-danger"></div>
</div>
<div class="form-group">
<label class="control-label" for="author_edit">Author:</label>
<input type="text" id="author_edit" name="author" class="form-control" data-error="Please enter the name of the author." required>
<div class="help-block with-errors text-danger"></div>
</div>
<div class="form-group">
<label class="control-label" for="published_date_edit">Date Published:</label>
<input type="text" id="published_date_edit" name="published_date" class="form-control" data-error="Please enter the date when the book was published." required autocomplete="off">
<div class="help-block with-errors text-danger"></div>
</div>
<div class="form-group mt-4">
<button type="submit" class="btn submit-book-edit btn-success">Submit</button>
</div>
</form>
</div>
</div>
</div>
</div>
</body>
</html>
Step 4: Handle the events. This file brings your page to life. It connects page events to the endpoints to complete the operation.
index.js
$(document).ready(function() {
/* Initialize the index */
var page = 1;
var current_page = 1;
var total_page = 0;
var display = "list";
/* Display Initial List of Books */
if(display=="list")
displayBookList();
/* View book */
$("body").on("click",".view-book",function(){
var id = $(this).parent("td").data('id');
var title = $(this).parent("td").prev("td").text();
var author = $(this).parent("td").prev("td").prev("td").text();
var published_date = $(this).parent("td").prev("td").prev("td").prev("td").text();
display = "details";
$("#id_view").text(id);
$("#title_view").text(title);
$("#author_view").text(author);
$("#published_date_view").text(published_date);
});
/* Create new book */
$('#create-book-form').on('submit', function(e){
e.preventDefault();
var form_action = $("#create-book-form").attr("action");
var title = $("#title").val();
var author = $("#author").val();
var published_date = $("#published_date").val();
display = "create";
if(title != '' && author != '' && published_date !=''){
$.ajax({
dataType: 'json',
type:'POST',
url: url + form_action,
data:{
title:title,
author:author,
published_date:published_date
}
}).done(function(data){
// Reset Form
$("#title").val('');
$("#author").val('');
$("#published_date").val('');
// Display new item on the list
displayPageEntries();
// Hide modal
$("#create-book").modal('hide');
toastr.success('Item Created Successfully.', 'Success Alert', {timeOut: 5000});
});
}else{
alert('All fields are required. Please make sure you fill out all fields correctly.')
}
});
/* Edit book */
$("body").on("click",".edit-book",function(){
var id = $(this).parent("td").data('id');
var title = $(this).parent("td").prev("td").text();
var author = $(this).parent("td").prev("td").prev("td").text();
var published_date = $(this).parent("td").prev("td").prev("td").prev("td").text();
display = "edit";
$("#title_edit").val(title);
$("#author_edit").val(author);
$("#published_date_edit").val(published_date);
$("#edit-book-form").find(".edit-id").val(id);
});
/* Update book */
$('#edit-book-form').on('submit', function(e){
e.preventDefault();
var form_action = $("#edit-book-form").attr("action");
var title = $("#title_edit").val();
var author = $("#author_edit").val();
var published_date = $("#published_date_edit").val();
var id = $("#edit-book-form").find(".edit-id").val();
if(title != '' && author != '' && published_date !=''){
$.ajax({
dataType: 'json',
type:'POST',
url: url + form_action,
data:{
id:id,
title:title,
author:author,
published_date:published_date
}
}).done(function(data){
// Reset Form
$("#title_edit").val('');
$("#author_edit").val('');
$("#published_date_edit").val('');
// Display new item on the list
displayPageEntries();
// Hide modal
$("#edit-book").modal('hide');
toastr.success('Item Updated Successfully.', 'Success Alert', {timeOut: 5000});
});
}else{
alert('All fields are required. Please make sure you fill out all fields correctly.')
}
});
/* Delete book */
$("body").on("click",".delete-book",function(){
var id = $(this).parent("td").data('id');
$.ajax({
dataType: 'json',
type:'POST',
url: url + 'api/delete.php',
data:{ id:id }
}).done(function(data){
displayPageEntries();
toastr.success('Item Deleted Successfully.', 'Success Alert', {timeOut: 5000});
});
});
/* Display initial list of all books with pagination */
function displayBookList() {
$.ajax({
dataType: 'json',
url: url+'api/index.php',
data: {page:page}
}).done(function(data){
total_page = Math.ceil(data.total/display_rows);
current_page = page;
refreshList(data.data);
$('#pagination').twbsPagination({
totalPages: total_page,
visiblePages: current_page,
onPageClick: function (event, pageL) {
page = pageL;
displayPageEntries();
}
});
});
}
/* Display books for current page*/
function displayPageEntries() {
$.ajax({
dataType: 'json',
url: url+'api/index.php',
data: {page:page}
}).done(function(data){
refreshList(data.data);
});
}
/* Refresh table list */
function refreshList(data) {
var rows = '';
$.each(data,function(key,value) {
rows = rows + '<tr>';
rows = rows + '<td>'+value.published_date+'</td>';
rows = rows + '<td>'+value.author+'</td>';
rows = rows + '<td>'+value.title+'</td>';
rows = rows + '<td data-id="'+value.id+'">';
rows = rows + '<button data-toggle="modal" data-target="#view-book" class="btn btn-primary btn-sm view-book">View Details</button> ';
rows = rows + '<button data-toggle="modal" data-target="#edit-book" class="btn btn-primary btn-sm edit-book">Edit Book</button> ';
rows = rows + '<button class="btn btn-danger btn-sm delete-book">Delete</button>';
rows = rows + '</td>';
rows = rows + '</tr>';
});
$("tbody").html(rows);
}
});
I grouped it into 4 basic steps to make the process of building the app more straight forward. On this test project without using a proper MVC framework (using only PHP, mySQL and jQuery) we have completed an AJAX CRUD app complete with notification and pagination.
This is important because there will be times that a programmer will be limited by the technology he can use and that’s when it pays to know the basics. I hope you enjoyed this tutorial, please share it to someone who might need it. Thanks!
(Visited 4,264 times, 4 visits today)
Hello i have test your basic ajax crud app but its not working, can you help me?
“The connection for this site is not secure” mhmhm why?
also the code is S tier helpful!
Thanks Sir
its working thanks