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:
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
We used a users table in this post. Below is the SQL command that you can use.
2.0 Files Used
To give you a brief overview, below are the files used to run this sample application.
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:
-- -- 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.
- index.php - contains all the jQuery code, "View Users" and "+ New User" button.
- create_form.php - shows the HTML form for creating a record.
- create.php - executes the insert query.
- read.php - reads the data from the database and put it in a table.
- update_form.php - shows the HTML form populated with data to be updated.
- update.php - executes the update query.
- delete.php - executes the delete query.
- images/ajax-loader.gif - animated image used to indicate the system is working.
- js/jquery-1.9.1.min.js - the great JavaScript library.
- libs/db_connect.php - so that we'll be able to connect to the database.
- 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. Click to enlarge. |
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(); } ?>
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>"; } ?>
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(); } ?>
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(); } ?>
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.
9.0 CodeOfaNinja.com Resources
For FREE programming tutorials, click the red button below and subscribe! :)
website