Update MySQL Table Using HTML, jQuery & AJAX

In this post, you will learn how to use HTML and jQuery AJAX to update the rows of a MySQL database table.

For this example, the HTML table will have a total of five columns. The first four will contain data pulled from a MySQL database. The cells will be editable so that the user can make changes to the data. The fifth column will contain a Save button that the user can click to save their changes to the database.

There are three components to the solution:

• HTML table code, generated by PHP (server side)
• jQuery AJAX code (client side) that sends asynchronous POSTS to the web server
• PHP script that processes the asynchronous post (server side)

HTML Table

First, we use PHP to generate an HTML table using data from a MySQL database. Each element of the table needs to be uniquely identified so that jQuery will know which row to send to the server when a Save button is clicked. The item of each row is uniquely identified by appending the row’s id to the end of each item’s id tag.

The contents of the table are made editable by using the HTML5 “contenteditable” tag which should work in all modern web browsers.

jQuery AJAX

jQuery is a Javascript library that simplifies AJAX. jQuery is invoked in a regular JavaScript file by using the $ symbol, followed by the element’s id in brackets, a period, and then the method to be called. There are two ways that we can use jQuery:

1. Pull it from a content delivery network, such as Google.
2. Download the code from jQuery and upload it to our own web server.

Our jQuery AJAX code will be invoked when the user clicks the “Save” button beside one of the rows. One of the first things the code does is create a local variable, “rowId”, and assigns it the unique id of the button that was pressed. This identifies the row that the user intends to update. The data of all the columns of the specified row are gathered and put into an array, which is then passed to the AJAX function. The AJAX function specifies the PHP script that the row’s data is to be sent to (“update.php”, in this case). It also contains a function to be performed if the AJAX call is successful. In this case, we simply display a dialog containing the server’s response.

Customize the following code and place it in a file called “jquery.js”, upload it to the web server, and make sure that the index.php file above has included it.

If you choose to download the jQuery source, instead of use a CDN, your javascript includes in your index.php will look something like this:

<!-- Jquery source -->
<script src="/javascript/jquery-2.1.4.min.js"></script>
<!-- Functions that use jquery -->
<script src="/javascript/jquery.js</script>
<!-- My Javascript (not jQuery) -->
<script src="/javascript/javascript.js</script>

PHP Script

The PHP script, “update.php” processes the POST request sent by jQuery. First, the elements of the array are extracted and the input is sanitized by PHP’s mysqli_real_escape_string() function to prevent SQL injection attempts. An SQL UPDATE query is generated and sent to the MySQL server. If successful, the script echoes a success message, which is returned to the jQuery code and displayed to the user as a popup message.

Place this code in the same directory as your index.php file.

Make Blank Cells Editable

If you have an existing MySQL table filled with NULL (blank) columns, you may notice that several columns cannot be edited when you click on them. Empty cells in HTML tables are not editable. You can change all NULL entries in MySQL table to &nbsp with an SQL statement like the following:

UPDATE customer SET email=" " WHERE email="";

Change the MySQL logic, so that when new rows are created, blank cells are automatically filled with  

Use the following SQL statement to change the default value of a column:

ALTER TABLE customer CHANGE COLUMN phone VARCHAR(20) NOT NULL DEFAULT " ";

Debugging AJAX

In Firefox, you can press Ctl+Shift+K to open the Web Console. Select the Console tab and then refresh the page to view all the requests made from the web browser to the server.

When you click the “Save” button, you should see another request being sent. Click on the request to get more detailed information. This information is often helpful when trying to determine what information is actually being sent to the server via jQuery.

References

Basic structure of Jquery, PHP script, and HTML table

Add jquery source & personal jquery files to javascript.php

AJAX query structure

Change specific HTML table row with AJAX

Live table edit with jQuery and AJAX

Use text() method for grabbing contents of td element,  instead of val()

How to prevent HTML injection and Cross Site Scripting

Debugging AJAX

Improve HTML Table Design