PHP REST API AJAX jQuery CRUD Example

Introduction

In this example we will see an example on PHP AJAX jQuery CRUD. CRUD is an acronym for four operations such as Create, Read, Update and Delete. We are going to perform CRUD operations on company details. We will use here MySQL database to store company detail information. We will perform each operation asynchronously through AJAX technique using jQuery on client side and PHP in server side.

We will create here REST APIs using PHP and call those REST APIs through jQuery AJAX from front-end. I am going to use Apache HTTP server to deploy our application.

We will display company information in tabular format or in HTML table on UI or front-end. We will perform inline edit on a row. We will ask users whether they want to really delete a record or not on clicking a delete button. We will add new company details on a popup window.

Prerequisites

PHP 7.3.5, Apache HTTP Server 2.4, MySQL 8.0.17

I assume that you already have the required configurations for PHP, MySQL and Apache server in your machine.

Create MySQL Table

We will create a table called company under roytuts database in MySQL server with the following structure.

CREATE TABLE IF NOT EXISTS `company` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Dump Data

As we need to test our application so we will dump some data into the company table.

INSERT INTO `company` (`id`, `name`) VALUES
	(1, 'Tom & Jerry'),
	(2, 'Order All'),
	(3, 'Roy Food'),
	(4, 'Chinese Food'),
	(5, 'Veg Food');

Creating Project Directory

We need to create a project root directory where we will put our server side code.

As we are using the same server for deploying the client application so we will put the client side code in the same folder.

The project root directory name is php-ajax-jquery-mysql-crud. Make sure you create this directory under htdocs folder in Apache server installation.

We may not speak about the project root directory name in subsequent sections but we will create file with respect to the project root directory.

PHP MySQL Configuration

We need to connect to MySQL database. Therefore we need PHP and MySQL configuration for connecting to MySQL database and performing MySQL queries from PHP code.

Create a file called db.php with the below source code.

<?php

/**
* Author : https://roytuts.com
*/
	
$dbConn = mysqli_connect('localhost', 'root', 'root', 'roytuts') or die('MySQL connect failed. ' . mysqli_connect_error());

function dbQuery($sql) {
	global $dbConn;
	$result = mysqli_query($dbConn, $sql) or die(mysqli_error($dbConn));
	return $result;
}

function dbFetchAssoc($result) {
	return mysqli_fetch_assoc($result);
}

function closeConn() {
	global $dbConn;
	mysqli_close($dbConn);
}
	
//End of file

PHP REST APIs

Now we will create REST APIs using PHP technology for performing CRUD operations.

REST API for Create

We will create REST API for creating resource. We will use HTTP method POST and JSON parameter will be used in the request body for creating new resource.

We are allowing anyone to access this REST API.

In PHP we retrieve the JSON body parameter using the following code.

file_get_contents("php://input", true)

Finally we decode the JSON parameters to access as an object using the function json_decode().

Create a file called create.php with the below source code.

<?php

/**
* Author : https://roytuts.com
*/

require_once 'db.php';

header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: POST");

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
	// get posted data
	$data = json_decode(file_get_contents("php://input", true));
	
	$sql = "INSERT INTO company(name) VALUES('" . mysqli_real_escape_string($dbConn, $data->name) . "')";
	dbQuery($sql);
}

//End of file

REST API for Read

Next we will create REST API for reading data from server. We may read single company information by passing a id value as a parameter or all company information.

We accept HTTP method GET and allow access to anyone.

<?php

/**
* Author : https://roytuts.com
*/

require_once 'db.php';

header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: GET");

if ($_SERVER['REQUEST_METHOD'] === 'GET' && isset($_GET['id'])) {
	$sql = "SELECT * FROM company WHERE id = " . mysqli_real_escape_string($dbConn, $_GET['id']) . " LIMIT 1";
	$result = dbQuery($sql);
	
	$row = dbFetchAssoc($result);
	
	echo json_encode($row);
} else {
	$sql = "SELECT * FROM company";
	$results = dbQuery($sql);
	
	$rows = array();
	
	while($row = dbFetchAssoc($results)) {
		$rows[] = $row;
	}
	
	echo json_encode($rows);
}

//End of file

REST API for Update

Next we create REST API for updating the existing resource. We accept HTTP method PUT and allow access to anyone to this API.

We read the JSON body parameter in the same way we read for POST API earlier.

<?php

/**
* Author : https://roytuts.com
*/

require_once 'db.php';

header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: PUT");

if ($_SERVER['REQUEST_METHOD'] === 'PUT') {
	// get posted data
	$data = json_decode(file_get_contents("php://input", true));
	
	$sql = "UPDATE company SET name = '" . mysqli_real_escape_string($dbConn, $data->name) . "' WHERE id = " . $data->id;
	dbQuery($sql);
}

//End of file

REST API for Delete

Finally we create REST API for deleting resource. We accept HTTP method DELETE and allow access to this API to anyone.

We are expecting the company id parameter to be passed as a query parameter in the URL string.

<?php

/**
* Author : https://roytuts.com
*/

require_once 'db.php';

header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: DELETE");

if ($_SERVER['REQUEST_METHOD'] === 'DELETE' && isset($_GET['id'])) {
	$sql = "DELETE FROM company WHERE id = " . mysqli_real_escape_string($dbConn, $_GET['id']);
	dbQuery($sql);
}

//End of file

We are done with the server application using PHP programming language.

Now we will create client application using simple HTML, CSS, jQuery with AJAX.

REST API CRUD Example in PHP PDO, MySQL

You can create as a separate application or you can put the application code in the same project root directory of the server application.

As I am using the same server so I am going to put into the same project directory.

Create below HTML file called ajax_crud.html with the below source code.

<!doctype html>
<html>
<head>
	<title>PHP REST API MySQL AJAX jQuery CRUD</title>
	<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
	<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
	<style>
		.input-group {
			margin: 10px 0px 10px 0px;
		}
		.input-group label {
			display: block;
			text-align: left;
			margin: 3px;
		}
		.input-group input {
			height: 30px;
			width: 300px;
			padding: 5px 10px;
			font-size: 16px;
			border-radius: 5px;
			border: 1px solid gray;
		}
		.btn {
			padding: 10px;
			font-size: 15px;
			color: white;
			background: #5F9EA0;
			border: none;
			border-radius: 5px;
		}
	</style>
	<script type="text/javascript">
		$(document).ready(function() {
			$.getJSON('http://localhost/php-ajax-jquery-mysql-crud/read.php', function(json) {
				var tr=[];
				for (var i = 0; i < json.length; i++) {
					tr.push('<tr>');
					tr.push('<td>' + json[i].id + '</td>');
					tr.push('<td>' + json[i].name + '</td>');
					tr.push('<td><button class=\'edit\'>Edit</button>&nbsp;&nbsp;<button class=\'delete\' id=' + json[i].id + '>Delete</button></td>');
					tr.push('</tr>');
				}
				$('table').append($(tr.join('')));
			});
			
			$(document).delegate('#addNew', 'click', function(event) {
				event.preventDefault();
				
				var name = $('#name').val();
				
				if(name == null || name == "") {
					alert("Company Name is required");
					return;
				}
				
				$.ajax({
					type: "POST",
					contentType: "application/json; charset=utf-8",
					url: "http://localhost/php-ajax-jquery-mysql-crud/create.php",
					data: JSON.stringify({'name': name}),
					cache: false,
					success: function(result) {
						alert('Company added successfully');
						location.reload(true);
					},
					error: function(err) {
						alert(err);
					}
				});
			});
			
			$(document).delegate('.delete', 'click', function() { 
				if (confirm('Do you really want to delete record?')) {
					var id = $(this).attr('id');
					var parent = $(this).parent().parent();
					$.ajax({
						type: "DELETE",
						url: "http://localhost/php-ajax-jquery-mysql-crud/delete.php?id=" + id,
						cache: false,
						success: function() {
							parent.fadeOut('slow', function() {
								$(this).remove();
							});
							location.reload(true)
						},
						error: function() {
							alert('Error deleting record');
						}
					});
				}
			});
			
			$(document).delegate('.edit', 'click', function() {
				var parent = $(this).parent().parent();
				
				var id = parent.children("td:nth-child(1)");
				var name = parent.children("td:nth-child(2)");
				var buttons = parent.children("td:nth-child(3)");
				
				name.html("<input type='text' id='txtName' value='" + name.html() + "'/>");
				buttons.html("<button id='save'>Save</button>&nbsp;&nbsp;<button class='delete' id='" + id.html() + "'>Delete</button>");
			});
			
			$(document).delegate('#save', 'click', function() {
				var parent = $(this).parent().parent();
				
				var id = parent.children("td:nth-child(1)");
				var name = parent.children("td:nth-child(2)");
				var buttons = parent.children("td:nth-child(3)");
				
				$.ajax({
					type: "PUT",
					contentType: "application/json; charset=utf-8",
					url: "http://localhost/php-ajax-jquery-mysql-crud/update.php",
					data: JSON.stringify({'id' : id.html(), 'name' : name.children("input[type=text]").val()}),
					cache: false,
					success: function() {
						name.html(name.children("input[type=text]").val());
						buttons.html("<button class='edit' id='" + id.html() + "'>Edit</button>&nbsp;&nbsp;<button class='delete' id='" + id.html() + "'>Delete</button>");
					},
					error: function() {
						alert('Error updating record');
					}
				});
			});

		});
	</script>
</head>
<body>

	<h2>PHP REST API MySQL AJAX jQuery CRUD</h2>
	
	<h3>Add a New Company</h3>
	<div class="input-group">
		<label>Company Name</label>
		<input type="text" id="name" name="name" value="">
	</div>
	<div class="input-group">
		<button class="btn" type="button" id="addNew">Save</button>
	</div>
	
	<p/>

	<table border="1" cellspacing="0" cellpadding="5">
		<tr>
			<th>Id</th>
			<th>Name</th>
			<th>Actions</th>
		</tr>
	</table>

</body>
</html>

I am not going to explain everything from the above file but what it does is:

In the above file we have included the required jQuery library from CDN.

We apply some basic style on the input text field and button through which we save new company information into MySQL database.

Next section contains the jQuery AJAX programming for performing CRUD operations. We reload window after deletion or addition of the record because AJAX technique is by default asynchronous and your changes on the page will not be reflected.

Testing the Application

Now run your Apache web server, your application will be deployed automatically as it is inside htdocs folder.

Now hitting the home page URL http://localhost/ajax-crud-client/ajax_crud.html will give you the following screen. As we had stored some data initially so it displays those data on home page.

Now you can perform Edit, Delete and Add new company information.

Source Code

Download

Thanks for reading.

Roy Tutorials