CRUD with jQuery and PHP for a Better User Experience

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 and faster loading of page data because your whole page won’t have to refresh every time. For our code’s final output, here’s a video demo:


Video Demo Permalink
DOWNLOAD CODE LIVE DEMO

Let’s Code!

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:

1.0 Database Structure and Sample Data
2.0 Files Used
3.0 Main Page
4.0 Creating a Record
5.0 Reading Records
6.0 Updating a Record
7.0 Deleting a Record
8.0 Styling the User Interface

1.0 Database Structure and Sample Data

We used a users table in this post. Below is the SQL command that you can use.

--
-- 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 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `firstname`, `lastname`, `gender`, `email`, `username`, `password`, `created`, `modified`) VALUES
(49, 'Justine', 'Bongola', 'Male', 'justine@coanmail.com', 'jaja', 'jaja123', '0000-00-00 00:00:00', '2013-03-03 14:06:03'),
(51, 'Lourd', 'De Veyra', 'Male', 'lourd@coanmail.com', 'lourd', 'lourd123', '0000-00-00 00:00:00', '2013-03-03 14:06:03'),
(73, 'Mike', 'Dalisay', '', '', 'ninjazhai', 'allisfine', '0000-00-00 00:00:00', '2013-05-12 06:39:04'),
(74, 'Darwin', 'Dalisay', '', '', 'dada', 'dada123', '0000-00-00 00:00:00', '2013-05-12 06:39:24'),
(75, 'Marykris', 'Dalisay', '', '', 'mary143', 'mary123', '0000-00-00 00:00:00', '2013-05-13 16:51:14');

2.0 Files Used

To give you a brief overview, below are the files used to run this sample application.

  1. index.php – contains all the jQuery code, “View Users” and “+ New User” button.
  2. create_form.php – shows the HTML form for creating a record.
  3. create.php – executes the insert query.
  4. read.php – reads the data from the database and put it in a table.
  5. update_form.php – shows the HTML form populated with data to be updated.
  6. update.php – executes the update query.
  7. delete.php – executes the delete query.
  8. images/ajax-loader.gif – animated image used to indicate the system is working.
  9. js/jquery-1.9.1.min.js – the great JavaScript library.
  10. libs/db_connect.php – so that we’ll be able to connect to the database.
  11. css/style.css – to make our UI look good.

3.0 Main Page

Our main page is the index.php, users interact with this page without refresh. All the jQuery code is inside this file. Here’s the code:

<!DOCTYPE HTML>
<html>
    <head>
        <title>PDO Tutorial</title>
        <link rel="stylesheet" type="text/css" href="css/style.css">
    </head>
<body>

<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>

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

<script src='js/jquery-1.9.1.min.js'></script>

<script type='text/javascript'>
$(document).ready(function(){
    
    // VIEW USERS on load of the page
    $('#loaderImage').show();
    showUsers();
    
    // clicking the 'VIEW USERS' button
    $('#viewUsers').click(function(){
        // show a loader img
        $('#loaderImage').show();
        
        showUsers();
    });
    
    // clicking the '+ NEW USER' button
    $('#addUser').click(function(){
        showCreateUserForm();
    });

    // 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);
        
    }); 
    
    
    // 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();
                    
                });

        }
    });
    
    
    // CREATE FORM IS 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;
    });
    
    // 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;
    });
    
});

// 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);
}

// CREATE 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>

</body>
</html>
CRUD with jQuery and PHP.

CRUD with jQuery and PHP.

4.0 Creating A Record

create_form.php

<!--
    -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>

create.php

<?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();
}
?>

5.0 Reading Records

read.php

<?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>";
}

?>

6.0 Update a Record

update_form.php

<?php
try {
    include 'libs/db_connect.php';
    
    //prepare query
    $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>

update.php

<?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();
}
?>

7.0 Delete A Record

delete.php

<?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();
}
?>

8.0 Styling the User Interface

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;
}

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!

The Code of a Ninja Resources

  • Messa

    A file upload in this project would be nice. So a input type=”file”. Add / edit / delete file.

    • ninjazhai

      I think that will be useful @Messa, thanks for the idea, I’ll add it once I got a chance.

  • Messa

    Also if creat form submit click twice it create two new entries. Thats not goood

    • ninjazhai

      I see your point, we can disable the button or form elements once the submit button was clicked.

  • Messa

    So if you wanna handle the problem with the creating form that the user only created one time (try an push the button often :) ). I put this in the creat_form.php
    Do disabled after sending the form the button. (Jquery)

    $(‘form’).submit(function(){
    $(‘.customBtn’).prop(“disabled”, true);
    });

    • ninjazhai

      Wow thanks for sharing your code @Messa, this is really helpful! :)

  • tip

    how do i add pagination to the above code