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)
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.
- Pull it from a content delivery network, such as Google.
- 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.
<!-- Jquery source -->
<!-- Functions that use jquery -->
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   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 " ";
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.
Use text() method for grabbing contents of td element, instead of val()