This tutorial will show how to inline insert the HTML5 table data into a database table using jQuery and AJAX. HTML5 allows editing table cells by setting the contentEditable attribute as ‘true.’
After adding data to the table cells, we can pass the cell data to PHP via jQuery AJAX. We create an insert query in the PHP page using cell data to store in the database.
In this example, we have an HTML5 table to add product information. We enter the product data in the editable cell and confirm to store it in a database by clicking a button. We use custom data attributes to store the product field name.
With the reference of the field name, we get its value entered. This will be useful to form the post data to be sent to PHP via AJAX.
The following code shows an editable HTML5 table and a Save button. In the table, the editable cells are referred to with the product field name using the data attribute. After entering data by inline editing, we must confirm to save the data to the database using the Save button.
<div class="txt-heading">Add Product</div>
<table cellpadding="10" cellspacing="1">
<tbody>
<tr>
<th><strong>Name</strong></th>
<th><strong>Code</strong></th>
<th><strong>Description</strong></th>
<th style="text-align:right;"><strong>Price</strong></th>
</tr>
<tr>
<td contentEditable="true" data-id="product_name"></td>
<td contentEditable="true" data-id="product_code"></td>
<td contentEditable="true" data-id="product_desc"></td>
<td contentEditable="true" data-id="product_price" style="text-align:right;"></td>
</tr>
</tbody>
</table>
<div id="btnSaveAction">Save to Database</div>
The following jQuery script reads editable cell content referencing the custom attribute and form data to be posted via AJAX.
PHP will use the post data and fire insert query to add this data to the database. On successful product insert, the PHP page will return the newly added row as a response to the AJAX call, which will be shown to the user.
<script>
$("#btnSaveAction").on("click",function(){
params = ""
$("td[contentEditable='true']").each(function(){
if($(this).text() != "") {
if(params != "") {
params += "&";
}
params += $(this).data('id')+"="+$(this).text();
}
});
if(params!="") {
$.ajax({
url: "insert-row.php",
type: "POST",
data:params,
success: function(response){
$("#ajax-response").append(response);
$("td[contentEditable='true']").text("");
}
});
}
});
</script>
The PHP file to insert data into the database is,
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
$product_name = "";
$product_code = "";
$product_desc = "";
$product_price = "";
if(!empty($_POST["product_name"])) {
$product_name = $db_handle->cleanData($_POST["product_name"]);
}
if(!empty($_POST["product_code"])) {
$product_code = $db_handle->cleanData($_POST["product_code"]);
}
if(!empty($_POST["product_desc"])) {
$product_desc = $db_handle->cleanData($_POST["product_desc"]);
}
if(!empty($_POST["product_price"])) {
$product_price = $db_handle->cleanData($_POST["product_price"]);
}
$sql = "INSERT INTO tbl_product (product_name,product_code,product_desc,product_price) VALUES ('" . $product_name . "','" . $product_code . "','" . $product_desc . "','" . $product_price . "')";
$product_id = $db_handle->executeInsert($sql);
if(!empty($product_id)) {
$sql = "SELECT * from tbl_product WHERE id = '$product_id' ";
$productResult = $db_handle->readData($sql);
}
?>
<?php
if(!empty($productResult)) {
?>
<tr>
<td><?php echo $productResult[0]["product_name"]; ?></td>
<td><?php echo $productResult[0]["product_code"]; ?></td>
<td><?php echo $productResult[0]["product_desc"]; ?></td>
<td style="text-align:right;"><?php echo $productResult[0]["product_price"]; ?></td>
</tr>
<?php
}
?>