In this tutorial, we are going to see how to do inline insert the HTML5 table data to a database table using jQuery and AJAX. HTML5 allows editing table cells by setting contentEditable attribute as true.
After adding data to the table cells, we can pass the cell data to PHP via jQuery AJAX. In the PHP page, we create insert query using cell data to store to 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 to a database on clicking a button. We use custom data attribute 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 with the product field name by using data attribute. After entering data by inline editing, we have to confirm to save the data to the database by 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 with the reference of 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, 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
}
?>