Home jQuery

CRUD with jQuery and PHP – Step By Step Guide

CRUD with jQuery and PHP – Step By Step Guide

After a PHP and MySQL CRUD tutorial for beginners, you may want to step up your level by adding some AJAX functionalities to it.

We will achieve it with the help of jQuery. This will enable a better user experience (UX) and fast-loading web interfaces. This is because your whole page won’t have to refresh every time.

This tutorial will focus on creating, reading, updating and deleting database records. We will do it using a MySQL database, jQuery AJAX and PHP.

This will lead to other useful features like search, pagination, CSV downloads and more.

Contents of this post will include:

1.0 CRUD with jQuery and PHP – Output

2.0 File Structure

3.0 Database table and connection
3.1 Create the database table
3.2 Dump sample data on the table
3.3 PHP script to connect to database

4.0 Put assets inside certain directories
4.1 Put jQuery and Bootstrap libraries in “libs/js/” folder
4.2 Put images in “images” folder

5.0 Make use of your HTML coding skills
5.1 Create basic HTML code structure for index.php
5.2 Add custom CSS
5.3 Add basic navigation button and loader image
5.4 Put the most important DIV tag

6.0 Create or insert data using AJAX in PHP
6.1 JavaScript to show the “create product HTML form”
6.2 HTML form used to create a record
6.3 JavaScript to create a record
6.4 PHP script to create a record

7.0 Read data using AJAX in PHP
7.1 JavaScript to read records
7.2 PHP script to read records

8.0 Update data using AJAX in PHP
8.1 JavaScript to show the “update record HTML form”
8.2 HTML form used to update a record
8.3 JavaScript to update a record
8.4 PHP script to update a record

9.0 Delete data using AJAX in PHP
9.1 JavaScript to delete a record
9.2 PHP script to delete a record

10.0 Source Code Download
10.1 Getting The Source Code

1.0 CRUD with jQuery and PHP – Output

Video demo of the LEVEL 1 source code:

Video demo of the LEVEL 2 source code: Coming soon!

2.0 File Structure

Knowing the file structure will give us an overview of what PHP files do we need to create and where the assets should be placed.

php-ajax-crud-tutorial-file-structure

3.0 Database structure and connection

We have to create a database, then a table with the following table structure, and a PHP file that we will use for database connection.

3.1 Create the database table

Run the following SQL code on your PhpMyAdmin. This is to create our database table. By the way, the database name we used in this tutorial was named “phpajaxcrudlevel1”.

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(512) NOT NULL,
  `description` text NOT NULL,
  `price` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=64 ;

3.2 Dump sample data on the table

Run the following SQL code on your PhpMyAdmin again, this will pre-insert sample data or record on our “products” database table.

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `description`, `price`, `created`, `modified`) VALUES
(1, 'LG Optimus 4X HD P880 Black', 'Display - True HD-IPS LCD - 720 x 1280 pixels, 4.7 inches. Internal Memory - 16 GB storage (12 GB user available), 1 GB RAM. Camera - 8 MP, 3264x2448 pixels, autofocus, LED flash', 309, '2014-06-01 01:12:26', '2014-05-31 17:12:26'),
(2, 'Motorola Google Nexus 6, Midnight Blue 32GB', 'The stunning 6 inch Quad HD display is great for movies, videos, gaming, e-books, and surfing the Web, and the Nexus 6 provides exceptional battery life.', 400, '2014-06-01 01:12:26', '2014-05-31 17:12:26'),
(3, 'Samsung Galaxy S4 i9500 16GB', 'Make your life richer, simpler, and more fun. As a real life companion, the new Samsung GALAXY S4 helps bring us closer and captures those fun moments when we are together. Each feature was designed to simplify our daily lives. Furthermore, it cares enough to monitor our health and well being.', 600, '2014-06-01 01:12:26', '2014-05-31 17:12:26'),
(6, 'Bench Men''s Bench Spokes Slim T-Shirt', 'Make their heads spin by rollin'' through with swag to spare. Cotton-poly heather blend provides for a soft, comfortable wear. Screen printed Bench graphics on front. Slim fitting for modern appeal. Contrast topstitching along shoulders. Ribbed crew neck. Short sleeves', 14, '2014-06-01 01:12:26', '2014-05-31 02:12:21'),
(7, 'HP ZBook 17 Mobile Business Workstation', 'Feel the power! Take performance to a new level with the HP ZBook 17 with Intel''s quad core CPU and 4GB GDDR5 Nvidia Quadro graphics.  Project a professional image at the office, client meetings, and on the road without sacrificing durability in a stylish chassis.', 5149, '2014-06-01 01:13:45', '2014-05-31 02:13:39'),
(8, 'Samsung Galaxy Tab 4', 'Ideal for watching HD movies, playing games, browsing the web, or reading, the Samsung Galaxy Tab 4 features a 10.1-inch, 1280x800 resolution screen, so you experience rich graphics, bright colors, and crisp text.', 210, '2014-06-01 01:14:13', '2014-05-31 02:14:08'),
(9, 'Spalding Men', 'Right from the beginning, it was all about being first, being the best…being what others could only dream of becoming. Founded by Boston Red Stockings pitcher A.G. Spalding in 1876, Spalding has become a leader of innovation and quality in the sporting goods industry.', 49, '2014-06-01 01:18:36', '2014-05-31 02:18:31'),
(10, 'Sony Smart Watch 3', 'Contextually aware and smart, Android Wear gives you useful information at a glance and responds to your voice, feeding you relevant and specific information as you move.', 194, '2014-06-06 17:10:01', '2014-06-05 18:09:51'),
(11, 'Huawei SnapTo', 'Support all GSM 4G LTE Networks ( T-Mobile, AT&T, Straight Talk, NET10, etc.). 75% screen-body ratio and a stylish, leather-texture finish battery cover with a slim design make the phone compac', 179, '2014-06-06 17:11:04', '2014-06-05 18:10:54'),
(12, 'Abercrombie Men''s Lake Arnold Blazer', '100% Gabardine wool imported from Italy. Classic collegiate blazer with heritage A&F crest at left chest pocket. Front pockets with fold-over flaps.', 25, '2014-06-06 17:12:21', '2014-06-05 18:12:11');

As you may have noticed, steps 1 and 2 are both SQL queries. Yes, they can be run at the same time. But I wanted it to be on separate steps to emphasize the SQL queries’ purpose.

3.3 PHP script to connect to database

Create a folder named “config” and inside it, create a database.php file with the following code. It answers how to connect to MySQL database with PDO?

<?php
// used to connect to the database
$host = "localhost";
$db_name = "phpajaxcrudlevel1";
$username = "root";
$password = "";

try {
	$con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}

// to handle connection error
catch(PDOException $exception){
	echo "Connection error: " . $exception->getMessage();
}
?>

4.0 Put assets inside certain directories

4.1 Put jQuery and Bootstrap libraries in “libs/js/” folder

This will enable us to use jQuery and Bootstrap library even if we are offline.

4.2 Put images in “images” folder

Put your choice of ajax-loader.gif inside “images” directory. You can use this AJAX loading GIF generator. This image will be used in almost every interaction a user will made in the program.

5.0 Make use of your HTML coding skills

5.1 Create basic HTML code structure for index.php

Create the index.php file. This is the only page the user has to interact with. Make it Bootstrap-ready. For those not familiar with Bootstrap, it is a popular user interface framework. I made a tutorial for you in this link: Step by Step Bootstrap Tutorial for Beginners

<!DOCTYPE html>
<html lang="en">
<head>
 
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
     
    <title>Read Products</title>
	
    <!-- bootstrap CSS -->
    <link href="libs/js/bootstrap/dist/css/bootstrap.css" rel="stylesheet" media="screen" />
	
</head>
<body>

    <!-- container -->
    <div class="container">
	
		<div class='page-header'>
			<h1 id='page-title'>Read Products</h1>
		</div>
		
		<!-- content will be here -->
		
    </div>
    <!-- /container -->
     
<!-- jQuery library -->
<script src="libs/js/jquery.js"></script>

<!-- bootstrap JavaScript -->
<script src="libs/js/bootstrap/dist/js/bootstrap.min.js"></script>
<script src="libs/js/bootstrap/docs-assets/js/holder.js"></script>
 
<!-- HTML5 Shiv and Respond.js IE8 support of HTML5 elements and media queries -->
<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
<!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->

<script type='text/javascript'>
// jquery / javascript codes will be here
</script>

</body>
</html>

5.2 Add custom CSS

Put this code before the end “head” tag. We use some custom CSS to make our user interface look better.

<!-- custom CSS -->
<style>
.display-none{
	display:none;
}

.padding-bottom-2em{
	padding-bottom:2em;
}

.width-30-pct{
	width:30%;
}

.width-40-pct{
	width:40%;
}

.overflow-hidden{
	overflow:hidden;
}

.margin-right-1em{
	margin-right:1em;
}

.right-margin{
	margin:0 .5em 0 0;
}

.margin-bottom-1em {
	margin-bottom:1em;
}

.margin-zero{
	margin:0;
}

.text-align-center{
	text-align:center;
}
</style>

5.3 Add basic navigation button and loader image

Our basic navigation buttons here are the “Read Products” and “Create Product” buttons. The “loader-image” is hidden but is shown everytime a user click on any button. It lets the user know something is loading or happening.

Add the following code after the “page-header” div tag.

<div class='margin-bottom-1em overflow-hidden'>
	<!-- when clicked, it will show the product's list -->
	<div id='read-products' class='btn btn-primary pull-right display-none'>
		<span class='glyphicon glyphicon-list'></span> Read Products
	</div>

	<!-- when clicked, it will load the create product form -->
	<div id='create-product' class='btn btn-primary pull-right'>
		<span class='glyphicon glyphicon-plus'></span> Create Product
	</div>
	
	<!-- this is the loader image, hidden at first -->
	<div id='loader-image'><img src='images/ajax-loader.gif' /></div>
</div>

5.4 Put the most important DIV tag

Inside this “div” tag is where every main content of the page will be loaded. We put “page-content” as ID reference because its content will be dynamic. Add the following code after the code on step 6.

<!-- this is where the contents will be shown. -->
<div id='page-content'></div>

6.0 Create or insert data using AJAX in PHP

6.1 JavaScript to show the “create product HTML form”

The “Create Product” form will be shown if the user clicks on the “Create Product” button. We’ll work with the JavaScript code now. Put the following code inside the “script type=’text/javascript'” tag from step 4, it can be found before the end “body” tag.

<script type='text/javascript'>
$(document).ready(function(){
	
	// will show the create product form
	$('#create-product').click(function(){
		// change page title
		changePageTitle('Create Product');
		
		// show create product form
		// show a loader image
		$('#loader-image').show();
		
		// hide create product button
		$('#create-product').hide();
		
		// show read products button
		$('#read-products').show();
		
		// fade out effect first
		$('#page-content').fadeOut('slow', function(){
			$('#page-content').load('create_form.php', function(){ 
			
				// hide loader image
				$('#loader-image').hide(); 
				
				// fade in effect
				$('#page-content').fadeIn('slow');
			});
		});
	});
});
</script>

6.2 HTML form used to create a record

The create_form.php file is the page to be loaded and contains the actual HTML form. Step 7 won’t work without it. Create the create_form.php file with the following code inside it.

<!--
	-we have our html form here where product information will be entered
	-we used the 'required' html5 property to prevent empty fields
-->
<form id='create-product-form' action='#' method='post' border='0'>
    <table class='table table-hover table-responsive table-bordered'>
        <tr>
            <td>Name</td>
            <td><input type='text' name='name' class='form-control' required /></td>
        </tr>
        <tr>
            <td>Description</td>
            <td><textarea name='description' class='form-control' required></textarea></td>
        </tr>
        <tr>
            <td>Price</td>
            <td><input type='number' min='1' name='price' class='form-control' required /></td>
        </tr>
        <tr>
            <td></td>
            <td>                
                <button type='submit' class='btn btn-primary'>
			<span class='glyphicon glyphicon-plus'></span> Create Product
		</button>
            </td>
        </tr>
    </table>
</form>

6.3 JavaScript to create a record

We have to catch the data when the create product form was submitted, and then store that data to the database. Add the following code after the “$(document).ready(function(){” code on section 6.1.

// will run if create product form was submitted
$(document).on('submit', '#create-product-form', function() {

	// show a loader img
	$('#loader-image').show();
	
	// post the data from the form
	$.post("create.php", $(this).serialize())
		.done(function(data) {
			
			// show create product button
			$('#create-product').show();
			
			// hide read products button
			$('#read-products').hide();
			
			// 'data' is the text returned, you can do any conditions based on that
			showProducts();
		});
			
	return false;
});

6.4 PHP script to create a record

This script is executed when the form was filled out and the “Create Product” button was clicked. Step 9 will not work without this script. Create a file named “create.php” with the following code inside it.

<?php
// include to get database connection
include_once 'config/database.php';

try{
	
	// set your default time-zone
	date_default_timezone_set('Asia/Manila');
	$created=date('Y-m-d H:i:s');
	
	// write query
	$query = "INSERT INTO products SET name=:name, description=:description, price=:price, created=:created";

	// prepare query for execution
	$stmt = $con->prepare($query);

	// bind the parameters
	// this is the first question mark
	$stmt->bindParam(":name", $_POST['name']);
	$stmt->bindParam(":description", $_POST['description']);
	$stmt->bindParam(":price", $_POST['price']);
	$stmt->bindParam(":created", $created);

	// execute the query
	if($stmt->execute()){
		echo "Product was created.";
	}else{
		echo "Unable to create product.";
	}
}

// handle error
catch(PDOException $exception){
	echo "Error: " . $exception->getMessage();
}

?>

7.0 Read data using AJAX in PHP

7.1 JavaScript to read records

JavaScript needed to read records from the database. Add the following code after the “$(document).ready(function(){” code on section 6.1.

// view products on load of the page
$('#loader-image').show();
showProducts();

// clicking the 'read products' button
$('#read-products').click(function(){
	
	// show a loader img
	$('#loader-image').show();
	
	// show create product button
	$('#create-product').show();
	
	// hide read products button
	$('#read-products').hide();
	
	// show products
	showProducts();
});

// read products
function showProducts(){
		
	// change page title
	changePageTitle('Read Products');
	
	// fade out effect first
	$('#page-content').fadeOut('slow', function(){
		$('#page-content').load('read.php', function(){
			// hide loader image
			$('#loader-image').hide(); 
			
			// fade in effect
			$('#page-content').fadeIn('slow');
		});
	});
}

7.2 PHP script to read records

Create a read.php file with the following code inside it.

<?php
// include to get database connection
include 'config/database.php';

// PDO select all query
$query = "SELECT id, name, description, price, created FROM products ORDER BY id desc";
$stmt = $con->prepare($query);
$stmt->execute();

//this is how to get number of rows returned
$num = $stmt->rowCount();

// check if more than 0 record found
if($num>0){

	// start table
    echo "<table class='table table-bordered table-hover'>";
    
        // creating our table heading
        echo "<tr>";
            echo "<th class='width-30-pct'>Name</th>";
            echo "<th class='width-30-pct'>Description</th>";
            echo "<th>Price</th>";
			echo "<th>Created</th>";
            echo "<th style='text-align:center;'>Action</th>";
        echo "</tr>";
        
        // retrieve our table contents
        // fetch() is faster than fetchAll()
        // http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
            // extract row
            // this will make $row['name'] to
            // just $name only
            extract($row);
            
            // creating new table row per record
            echo "<tr>";
                echo "<td>{$name}</td>";
                echo "<td>{$description}</td>";
                echo "<td>{$price}</td>";
				echo "<td>{$created}</td>";
                echo "<td style='text-align:center;'>";
					// add the record id here, it is used for editing and deleting products
					echo "<div class='product-id display-none'>{$id}</div>";
					
                    // edit button
                    echo "<div class='btn btn-info edit-btn margin-right-1em'>";
						echo "<span class='glyphicon glyphicon-edit'></span> Edit";
					echo "</div>";
					
                    // delete button
                    echo "<div class='btn btn-danger delete-btn'>";
						echo "<span class='glyphicon glyphicon-remove'></span> Delete";
					echo "</div>";
                echo "</td>";
            echo "</tr>";
        }
        
	//end table
    echo "</table>";
    
}

// tell the user if no records found
else{
    echo "<div class='noneFound'>No records found.</div>";
}

?>

8.0 Update data using AJAX in PHP

8.1 JavaScript to show the “update record HTML form”

The following code is executed when the “edit” button was clicked. This code is also after the “$(document).ready(function(){” code on section 6.1.

// clicking the edit button
$(document).on('click', '.edit-btn', function(){ 

	// change page title
	changePageTitle('Update Product');

	var product_id = $(this).closest('td').find('.product-id').text();
	
	// show a loader image
	$('#loader-image').show();
	
	// hide create product button
	$('#create-product').hide();
	
	// show read products button
	$('#read-products').show();

	// fade out effect first
	$('#page-content').fadeOut('slow', function(){
		$('#page-content').load('update_form.php?product_id=' + product_id, function(){
			// hide loader image
			$('#loader-image').hide(); 
			
			// fade in effect
			$('#page-content').fadeIn('slow');
		});
	});
});	

8.2 HTML form used to update a record

Create update_form.php file with the following code inside it. This file contains a PHP script that reads a single database record based on given ID parameter. It also contains the HTML form with the data we retrieved previously.

<?php
//include to get database connection
include 'config/database.php';

// get product id
$product_id=isset($_GET['product_id']) ? $_GET['product_id'] : die('ERROR: Product ID not found.');

// PDO query to select single record based on ID
$query = "SELECT 
			id, name, description, price 
		FROM 
			products  
		WHERE 
			id = ? 
		LIMIT 0,1";
		
// prepare query
$stmt = $con->prepare($query);

// this is the first question mark on the query
$stmt->bindParam(1, $product_id);

// execute our query
if($stmt->execute()){

	// store retrieved row to a variable
	$row = $stmt->fetch(PDO::FETCH_ASSOC);

	// values to fill up our form
	$id = $row['id'];
	$name = $row['name'];
	$description = $row['description'];
	$price = $row['price'];
	
}else{
	echo "Unable to read record.";
}
?>

<!--we have our html form here where new product information will be entered-->
<form id='update-product-form' action='#' method='post' border='0'>
    <table class='table table-bordered table-hover'>
        <tr>
            <td>Name</td>
            <td><input type='text' name='name' class='form-control' value='<?php echo htmlspecialchars($name, ENT_QUOTES); ?>' required /></td>
        </tr>
        <tr>
            <td>Description</td>
            <td>
			<textarea name='description' class='form-control' required><?php echo htmlspecialchars($description, ENT_QUOTES); ?></textarea>
			</td>
        </tr>
        <tr>
            <td>Price</td>
            <td><input type='number' min='1' name='price' class='form-control' value='<?php echo htmlspecialchars($price, ENT_QUOTES);  ?>' required /></td>
        </tr>
		<tr>
			<td>
				<!-- hidden ID field so that we could identify what record is to be updated -->
                		<input type='hidden' name='id' value='<?php echo $id ?>' />	
			</td>
            <td>
		<button type='submit' class='btn btn-primary'>
			<span class='glyphicon glyphicon-edit'></span> Save Changes
		</button>
            </td>
        </tr>
    </table>
</form>

8.3 JavaScript to update a record

// will run if update product form was submitted
$(document).on('submit', '#update-product-form', function() {

	// show a loader img
	$('#loader-image').show();
	
	// post the data from the form
	$.post("update.php", $(this).serialize())
		.done(function(data) {
			
			// show create product button
			$('#create-product').show();
			
			// hide read products button
			$('#read-products').hide();
		
			// 'data' is the text returned, you can do any conditions based on that
			showProducts();
		});
			
	return false;
});

8.4 PHP script to update a record

<?php
// include to get database connection
include_once 'config/database.php';

try{
	// PDO update query
	$query = "UPDATE  
				products 
			set 
				name = :name, 
				description = :description, 
				price = :price
			where
				id = :id";

	// prepare query for execution
	$stmt = $con->prepare($query);

	// bind the parameters
	$stmt->bindParam(':name', $_POST['name']);
	$stmt->bindParam(':description', $_POST['description']);
	$stmt->bindParam(':price', $_POST['price']);
	$stmt->bindParam(':id', $_POST['id']);

	// execute the query
	if($stmt->execute()){
		echo "Product was updated.";
	}else{
		echo "Unable to update product.";
	}
}

// handle error
catch(PDOException $exception){
	echo "Error: " . $exception->getMessage();
}
?>

9.0 Delete data using AJAX in PHP

9.1 JavaScript to delete a record

// will run if the delete button was clicked
$(document).on('click', '.delete-btn', function(){ 
	if(confirm('Are you sure?')){
	
		// get the id
		var product_id = $(this).closest('td').find('.product-id').text();
		
		// trigger the delete file
		$.post("delete.php", { id: product_id })
			.done(function(data){
				console.log(data);
				
				// show loader image
				$('#loader-image').show();
				
				// reload the product list
				showProducts();
				
			});
	}
});

9.2 PHP script to delete a record

Create a delete.php file with the following code inside it.

<?php
// include to get database connection
include_once 'config/database.php';

try {
	
	// PDO delete query
	$query = "DELETE FROM products WHERE id = ?";
	$stmt = $con->prepare($query);
	
	// substitute for the question mark on the query
	$stmt->bindParam(1, $_POST['id']);
	
	// execute the query
	if($stmt->execute()){
		echo "Product was deleted.";
	}else{
		echo "Unable to delete product.";
	}
}

// handle error
catch(PDOException $exception){
	echo "Error: " . $exception->getMessage();
}
?>

10.0 Source Code Download

10.1 Getting The Source Code

You can get the source code by following the whole, well detailed tutorial above. But isn’t it more convenient if you can just download the complete source code we used, and play around it?

There’s a small fee in getting the complete source code, it is small compared to the:

✔ Value or skill upgrade it can bring you, or YES
✔ Income you can get from your website project or business. YES
✔ Precious time you save. YES

For a limited time, I will give you the source code for a low price. DOWNLOAD THE SOURCE CODE LEVEL you desire by clicking its the BUY button below.

FEATURES LEVEL 1 LEVEL 2
Create product YES YES
Read product YES YES
Update product YES YES
Delete product YES YES
HTML5 validation YES YES
SQL file and changelog.txt in “dev” folder YES YES
Bootstrap UI YES YES
Category selection for create and update product NO YES
Pagination NO YES
Search product by name NO YES
Multiple delete NO YES
Export CSV NO YES
Navigation bar with category filter NO YES
Price displayed with comma and decimal point NO YES
MORE REASONS TO DOWNLOAD IT
Use new skills for your multiple projects YES YES
Save huge amount of time learning jQuery and PHP YES YES
Code examples are direct to the point YES YES
Well explained and commented source code YES YES
Fast and friendly email support YES YES
Free source code updates YES YES
LEVEL 1: BUY AND DOWNLOAD NOW USING
OR
COMING SOON!
* You can use your debit or credit card with PayPal.

You can read more about jQuery post and jQuery load functions that we used extensively in this tutorial. Understand more about jQuery and setTimeOut.

Important note:

If you found a problem with this code, we can solve it faster via Email or FB message, please send me a message via email ninjazhai30@gmail.com, or via our official Facebook page!

Please be more detailed about your issue. Best if you can provide your test URL. Thanks!

Thanks for reading this CRUD with jQuery and PHP for a Better User Experience!