Home / jQuery /

CRUD with jQuery and PHP to Improve UX – Step By Step Guide

CRUD with jQuery and PHP to Improve UX – Step By Step Guide

We already have a PHP CRUD tutorial in this blog and now we are going to add some AJAX functions to it with the help of jQuery. This will enable smoother user experience (or UX) and faster loading of page data because your whole page won’t have to refresh every time.

Please note that this is not a production-ready code. This tutorial will only focus on creating, reading, updating and deleting records from a MySQL database with the help of PDO and jQuery.

Contents of this post will include:

1.0 Program Output
2.0 File Structure

3.0 CRUD with jQuery and PHP: Step by Step
Step 1: Create the Database Table
Step 2: Dump Sample Data On The Table
Step 3: Create Database Connection PHP File
Step 4: Create Basic HTML Code Structure For index.php
Step 5: Add Basic Navigation Button and Loader Image
Step 6: Put The Most Important DIV Tag
Step 7: Show The Create New Record Form
Step 8: The File That Contains The Create Record HTML Form
Step 9: Script To Create New Record In The Database
Step 10: Prepare To Read Records From The Database
Step 11: Script That Read Database Records
Step 12: JavaScript For Updating A Record
Step 13: Edit HTML For That Contains Data
Step 14: Save Changes To The Database
Step 15: JavaScript To Delete A Record
Step 16: PHP Script That Actually Deletes A Record
Step 17: Change How It Looks Using Custom CSS

4.0 Download Source Code

1.0 Program Output

For our code’s final output, here’s a video demo:

LIVE DEMO

2.0 File Structure

Coming soon.

3.0 CRUD with jQuery and PHP: Step by Step

Now here’s the fun part!

Step 1: Create the Database Table

Run the following SQL code on your PhpMyAdmin. This is to create our database table.

--
-- Table structure for table `users`
--
 
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(32) NOT NULL,
  `lastname` varchar(32) NOT NULL,
  `gender` varchar(6) NOT NULL,
  `email` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=76 ;
1

<h3>Step 2: Dump Sample Data On The Table</h3>

Run the following SQL code on your PhpMyAdmin again, this will pre-insert sample data or record on our ‘users’ database.

1
--
-- Dumping data for table `users`
--
  
INSERT INTO `users` (`id`, `firstname`, `lastname`, `email`, `username`, `password`, `modified`) VALUES
(28, 'John', 'Dalisay', '', 'john', 'john123', '2012-01-15 07:26:14'),
(39, 'Jem', 'Panlilio', '', 'jemboy09', 'jem123', '2012-01-15 07:26:46'),
(40, 'Darwin', 'Dalisay', '', 'dada08', 'dada123', '2012-01-15 07:25:34'),
(46, 'Jaylord', 'Bitamug', '', 'jayjay', 'jay123', '2012-01-15 07:27:04'),
(49, 'Justine', 'Bongola', '', 'jaja', 'jaja123', '2012-01-15 07:27:21'),
(50, 'Jun', 'Sabayton', '', 'jun', 'jun123', '2012-02-05 10:15:14'),
(51, 'Lourd', 'De Veyra', '', 'lourd', 'lourd123', '2012-02-05 10:15:36'),
(52, 'Asi', 'Taulava', '', 'asi', 'asi123', '2012-02-05 10:15:58'),
(53, 'James', 'Yap', '', 'james', 'jame123', '2012-02-05 10:16:17'),
(54, 'Chris', 'Tiu', '', 'chris', 'chris123', '2012-02-05 10:16:29');

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.

Step 3: Create Database Connection PHP File

Create db_connect.php file and put the following code inside it, it answers how to connect to MySQL database with PDO?

<?php
$host = "localhost";
$db_name = "your_db_name";
$username = "your_db_username";
$password = "your_db_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();
}
?>

Step 4: Create Basic HTML Code Structure For index.php

Create the index.php file, this is the only page the user has to interact with. Put the code below inside the newly created add.php file.

<!DOCTYPE HTML>
<html>
    <head>
        <title>CRUD with jQUERY and AJAX</title>
  
    </head>
<body>
 
<!-- dynamic content will be here -->
 
</body>
</html>

Step 5: Add Basic Navigation Button and Loader Image

Our basic navigation buttons here are the “View Users” and “+ New User” buttons. The “loaderImage” is hidden but is shown everytime a user click any button. It lets the user know something is loading or happening. Add before the first “body” tag.

<div style='margin:0 0 .5em 0;'>
    <!-- when clicked, it will show the user's list -->
    <div id='viewUsers' class='customBtn'>View Users</div>
 
    <!-- when clicked, it will load the add user form -->
    <div id='addUser' class='customBtn'>+ New User</div>
 
    <!-- this is the loader image, hidden at first -->
    <div id='loaderImage'><img src='images/ajax-loader.gif' /></div>
 
    <div style='clear:both;'></div>
</div>

Step 6: Put The Most Important DIV Tag

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

<!-- this is wher the contents will be shown. -->
<div id='pageContent'></div>

Step 7: Show The Create New Record Form

The “create new record” form will be show if the user clicks on the “+ New User” button. We’ll work wit the JavaScript code now. Put the following code before the end “body” tag.

<!-- Step 7.1, include jquery library -->
<script src='js/jquery-1.9.1.min.js'></script>
 
<script type='text/javascript'>
// Step 7.2, capture the click event of "+ New User" button
$(document).ready(function(){
    
	// clicking the 'VIEW USERS' button
    $('#viewUsers').click(function(){
        // show a loader img
        $('#loaderImage').show();
 
        showUsers();
    });
	
    // Step 7.4: Save the data when the create from was submitted
     $(document).on('submit', '#addUserForm', function() {
 
        // show a loader img
        $('#loaderImage').show();
 
        // post the data from the form
        $.post("create.php", $(this).serialize())
            .done(function(data) {
                // 'data' is the text returned, you can do any conditions based on that
                showUsers();
            });
 
        return false;
    });
	
});

// Step 7.3, JavaScript function the will show the creat user form
function showCreateUserForm(){
    // show a loader image
    $('#loaderImage').show();
 
    // read and show the records after 1 second
    // we use setTimeout just to show the image loading effect when you have a very fast server
    // otherwise, you can just do: $('#pageContent').load('read.php');
    setTimeout("$('#pageContent').load('create_form.php', function(){ $('#loaderImage').hide(); });",1000);
}
</script>

Step 8: The File That Contains The Create Record HTML Form

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 user information will be entered
    -we used the 'required' html5 property to prevent empty fields
-->
<form id='addUserForm' action='#' method='post' border='0'>
    <table>
        <tr>
            <td>Firstname</td>
            <td><input type='text' name='firstname' required /></td>
        </tr>
        <tr>
            <td>Lastname</td>
            <td><input type='text' name='lastname' required /></td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type='text' name='username' required /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type='password' name='password' required /></td>
        <tr>
            <td></td>
            <td>
                <input type='submit' value='Save' class='customBtn' />
            </td>
        </tr>
    </table>
</form>

Step 9: Script To Create New Record In The Database

This script is executed when the form was filled out and the “Save” button was clicked.

<?php
//include database connection
include 'libs/db_connect.php';
 
try{
    //write query
    $query = "INSERT INTO users SET firstname = ?, lastname = ?, username = ?, password  = ?";
 
    //prepare query for excecution
    $stmt = $con->prepare($query);
 
    //bind the parameters
    //this is the first question mark
    $stmt->bindParam(1, $_POST['firstname']);
 
    //this is the second question mark
    $stmt->bindParam(2, $_POST['lastname']);
 
    //this is the third question mark
    $stmt->bindParam(3, $_POST['username']);
 
    //this is the fourth question mark
    $stmt->bindParam(4, $_POST['password']);
 
    // Execute the query
    if($stmt->execute()){
        echo "User was created.";
    }else{
        echo "Unable to created user.";
    }
}
 
//to handle error
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>

Step 10: Prepare To Read Records From The Database

JavaScript needed to read records from the database. These codes are inside the index.php file, on the JavaScript part.

// Step 10.1 read users on load of index.php
$('#loaderImage').show();
showUsers();

// Step 10.2: Read users on click of the 'view users' button
$('#viewUsers').click(function(){
	// show a loader img
	$('#loaderImage').show();

	showUsers();
});

// Step 10.3: Function to read users
function showUsers(){
    // read and show the records after at least a second
    // we use setTimeout just to show the image loading effect when you have a very fast server
    // otherwise, you can just do: $('#pageContent').load('read.php', function(){ $('#loaderImage').hide(); });
    // THIS also hides the loader image
    setTimeout("$('#pageContent').load('read.php', function(){ $('#loaderImage').hide(); });", 1000);
}

Step 11: Script That Read Database Records

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

<?php
//include database connection
include 'libs/db_connect.php';
 
//select all data
$query = "SELECT id, firstname, lastname, username FROM users ORDER BY id desc";
$stmt = $con->prepare( $query );
$stmt->execute();
 
//this is how to get number of rows returned
$num = $stmt->rowCount();
 
if($num>0){ //check if more than 0 record found
 
    echo "<table id='tfhover' class='tftable' border='1'>";//start table
 
        //creating our table heading
        echo "<tr>";
            echo "<th>Firstname</th>";
            echo "<th>Lastname</th>";
            echo "<th>Username</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['firstname'] to
            //just $firstname only
            extract($row);
 
            //creating new table row per record
            echo "<tr>";
                echo "<td>{$firstname}</td>";
                echo "<td>{$lastname}</td>";
                echo "<td>{$username}</td>";
                echo "<td style='text-align:center;'>";
                    // add the record id here
                    echo "<div class='userId'>{$id}</div>";
 
                    //we will use this links on next part of this post
                    echo "<div class='editBtn customBtn'>Edit</div>";
 
                    //we will use this links on next part of this post
                    echo "<div class='deleteBtn customBtn'>Delete</div>";
                echo "</td>";
            echo "</tr>";
        }
 
    echo "</table>";//end table
 
}
 
// tell the user if no records found
else{
    echo "<div class='noneFound'>No records found.</div>";
}
 
?>

Step 12: JavaScript For Updating A Record

Executed when the “edit” button was clicked. This code is inside the JavaScript part of index.php file.

// clicking the EDIT button
$(document).on('click', '.editBtn', function(){ 

	var user_id = $(this).closest('td').find('.userId').text();
	console.log(user_id);

	// show a loader image
	$('#loaderImage').show();

	// read and show the records after 1 second
	// we use setTimeout just to show the image loading effect when you have a very fast server
	// otherwise, you can just do: $('#pageContent').load('update_form.php?user_id=" + user_id + "', function(){ $('#loaderImage').hide(); });
	setTimeout("$('#pageContent').load('update_form.php?user_id=" + user_id + "', function(){ $('#loaderImage').hide(); });",1000);

});

// UPDATE FORM IS SUBMITTED
$(document).on('submit', '#updateUserForm', function() {

	// show a loader img
	$('#loaderImage').show();

	// post the data from the form
	$.post("update.php", $(this).serialize())
		.done(function(data) {
			// 'data' is the text returned, you can do any conditions based on that
			showUsers();
		});

	return false;
});

Step 13: Edit HTML For That Contains Data

Create update_form.php file with the following code inside it.

<?php
try {
    include 'libs/db_connect.php';
 
    // select record to be edited
    $query = "select
                id, firstname, lastname, username, password
            from
                users
            where
                id = ?
            limit 0,1";
 
    $stmt = $con->prepare( $query );
 
    //this is the first question mark
    $stmt->bindParam(1, $_REQUEST['user_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'];
        $firstname = $row['firstname'];
        $lastname = $row['lastname'];
        $username = $row['username'];
        $password = $row['password'];
 
    }else{
        echo "Unable to read record.";
    }
}
 
//to handle error
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>
<!--we have our html form here where new user information will be entered-->
<form id='updateUserForm' action='#' method='post' border='0'>
    <table>
        <tr>
            <td>Firstname</td>
            <td><input type='text' name='firstname' value='<?php echo $firstname; ?>' required /></td>
        </tr>
        <tr>
            <td>Lastname</td>
            <td><input type='text' name='lastname' value='<?php echo $lastname;  ?>' required /></td>
        </tr>
        <tr>
            <td>Username</td>
            <td><input type='text' name='username'  value='<?php echo $username;  ?>' required /></td>
        </tr>
        <tr>
            <td>Password</td>
            <td><input type='password' name='password' value='<?php echo $password;  ?>' required/></td>
        <tr>
            <td></td>
            <td>
                <!-- so that we could identify what record is to be updated -->
                <input type='hidden' name='id' value='<?php echo $id ?>' />
                <input type='submit' value='Update' class='customBtn' />
 
            </td>
        </tr>
    </table>
</form>

Step 14: Save Changes To The Database

<?php
//include database connection
include 'libs/db_connect.php';
 
try{
 
    //write query
    //in this case, it seemed like we have so many fields to pass and
    //its kinda better if we'll label them and not use question marks
    //like what we used here
    $query = "update
                users
            set
                firstname = :firstname,
                lastname = :lastname,
                username = :username,
                password = :password
            where
                id = :id";
 
    //prepare query for excecution
    $stmt = $con->prepare($query);
 
    //bind the parameters
    $stmt->bindParam(':firstname', $_POST['firstname']);
    $stmt->bindParam(':lastname', $_POST['lastname']);
    $stmt->bindParam(':username', $_POST['username']);
    $stmt->bindParam(':password', $_POST['password']);
 
    $stmt->bindParam(':id', $_POST['id']);
 
    // Execute the query
    if($stmt->execute()){
        echo "User was updated.";
    }else{
        echo "Unable to update user.";
    }
 
}
 
//to handle error
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>

Step 15: JavaScript To Delete A Record

This code is inside the JavaScript part of index.php file.

// when clicking the DELETE button
$(document).on('click', '.deleteBtn', function(){
	if(confirm('Are you sure?')){

		// get the id
		var user_id = $(this).closest('td').find('.userId').text();

		// trigger the delete file
		$.post("delete.php", { id: user_id })
			.done(function(data) {
				// you can see your console to verify if record was deleted
				console.log(data);

				$('#loaderImage').show();

				// reload the list
				showUsers();

			});

	}
});

Step 16: PHP Script That Actually Deletes A Record

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

<?php
//include database connection
include 'libs/db_connect.php';
 
try {
 
    $query = "DELETE FROM users WHERE id = ?";
    $stmt = $con->prepare($query);
    $stmt->bindParam(1, $_POST['id']);
 
    if($stmt->execute()){
        echo "User was deleted.";
    }else{
        echo "Unable to delete user.";
    }
 
}
 
//to handle error
catch(PDOException $exception){
    echo "Error: " . $exception->getMessage();
}
?>

Step 17: Change How It Looks Using Custom CSS

Here’s the CSS code I used. The code below is inside style.css

body{
    font: normal normal 110% Arial, Serif;
}
 
.customBtn {
    cursor:pointer;
    margin:0 .3em 0 0;
    -moz-box-shadow:inset 0px 1px 0px 0px #caefab;
    -webkit-box-shadow:inset 0px 1px 0px 0px #caefab;
    box-shadow:inset 0px 1px 0px 0px #caefab;
    background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #77d42a), color-stop(1, #5cb811) );
    background:-moz-linear-gradient( center top, #77d42a 5%, #5cb811 100% );
    filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#77d42a', endColorstr='#5cb811');
    background-color:#77d42a;
    -moz-border-radius:6px;
    -webkit-border-radius:6px;
    border-radius:6px;
    border:1px solid #268a16;
    display:inline-block;
    color:#ffffff;
    font-family:arial;
    font-size:15px;
    font-weight:bold;
    padding:6px 24px;
    text-decoration:none;
    /*text-shadow:1px 1px 0px #f1f1f1;*/
}
 
.customBtn:hover {
    background:-webkit-gradient( linear, left top, left bottom, color-stop(0.05, #5cb811), color-stop(1, #77d42a) );
    background:-moz-linear-gradient( center top, #5cb811 5%, #77d42a 100% );
    filter:progid:DXImageTransform.Microsoft.gradient(startColorstr='#5cb811', endColorstr='#77d42a');
    background-color:#5cb811;
}
 
.customBtn:active {
    position:relative;
    top:1px;
}
 
input[type=text],
input[type=password]{
    padding:.5em;
}
 
table.tftable {font-size:12px;color:#333333;width:50%;border-width: 1px;border-color: #a9a9a9;border-collapse: collapse;}
table.tftable th {font-size:12px;background-color:#b8b8b8;border-width: 1px;padding: 8px;border-style: solid;border-color: #a9a9a9;text-align:left;}
table.tftable tr {background-color:#ffffff;}
table.tftable td {font-size:12px;border-width: 1px;padding: 8px;border-style: solid;border-color: #a9a9a9;}
 
.userId{
    display:none;
}
 
#viewUsers,
#addUser{
    float:left;
}
 
#loaderImage{
    float:left;
    line-height:32px;
}

4.0 Download 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 and skill upgrade it can bring you, or income you can get from your website project or business. For a limited time, I will give you the source code for a low price. DOWNLOAD THE SOURCE CODE by clicking the green button below.

buy

What you will get? A source code featuring CRUD (create, read, update and delete) database records with PHP PDO and AJAX (jQuery), free code updates and free email support from me as well! DOWNLOAD THE SOURCE CODE by clicking the green button below.

buy

Thanks for your support!

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

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

Loading comments...