In this post, we have learn how can we insert multiple HTML5 contenteditable table data to mysql database table using PHP with jQuery and AJAX. For this things we have use HTML5 contenteditable attribute in table, this attribute allows to edit table cells by writing contenteditable attribute as true. So by using this attribute we can enter data to the table cells. Then after we have use Jquery code for append new table cells with contenteditable attribute tag as true. So we can append number of editable table cells and we can enter data into that table cells. Then after we have pass table cell data to PHP Script by using Jquery and Ajax. In Server Side PHP script will clean table cells data and make multiple Insert data query and by using mysqli_multi_query() function we have execute multiple insert query for Insert Multiple data into Mysql table.
For making Multiple Inline Insert data example, we have use HTML5 Contenteditable attribute in table cell for add multiple Item information. By using jQuery code we can append number of blank table row into Item Add table. So at once We can enter multiple item data in the editable table cell and then after we can store those multiple data into mysql database on single clicking of a button. So by using this feature we can enter multiple inline table data insert into mysql table in a single click. For sending data from table to PHP script we have use JQuery for fetching data from table cell and by using Ajax we have send that data to php script and in PHP script make multiple insert query and execute query. So in single click of button we can Insert Multiple Inline data into table without refresh of web page.
Source Code
index.php
<!DOCTYPE html>
<html>
<head>
<title>Webslesson Tutorial | Multiple Inline Insert into Mysql using Ajax JQuery in PHP</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<br /><br />
<div class="container">
<br />
<h2 align="center">Multiple Inline Insert into Mysql using Ajax JQuery in PHP</h2>
<br />
<div class="table-responsive">
<table class="table table-bordered" id="crud_table">
<tr>
<th width="30%">Item Name</th>
<th width="10%">Item Code</th>
<th width="45%">Description</th>
<th width="10%">Price</th>
<th width="5%"></th>
</tr>
<tr>
<td contenteditable="true" class="item_name"></td>
<td contenteditable="true" class="item_code"></td>
<td contenteditable="true" class="item_desc"></td>
<td contenteditable="true" class="item_price"></td>
<td></td>
</tr>
</table>
<div align="right">
<button type="button" name="add" id="add" class="btn btn-success btn-xs">+</button>
</div>
<div align="center">
<button type="button" name="save" id="save" class="btn btn-info">Save</button>
</div>
<br />
<div id="inserted_item_data"></div>
</div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
var count = 1;
$('#add').click(function(){
count = count + 1;
var html_code = "<tr id='row"+count+"'>";
html_code += "<td contenteditable='true' class='item_name'></td>";
html_code += "<td contenteditable='true' class='item_code'></td>";
html_code += "<td contenteditable='true' class='item_desc'></td>";
html_code += "<td contenteditable='true' class='item_price' ></td>";
html_code += "<td><button type='button' name='remove' data-row='row"+count+"' class='btn btn-danger btn-xs remove'>-</button></td>";
html_code += "</tr>";
$('#crud_table').append(html_code);
});
$(document).on('click', '.remove', function(){
var delete_row = $(this).data("row");
$('#' + delete_row).remove();
});
$('#save').click(function(){
var item_name = [];
var item_code = [];
var item_desc = [];
var item_price = [];
$('.item_name').each(function(){
item_name.push($(this).text());
});
$('.item_code').each(function(){
item_code.push($(this).text());
});
$('.item_desc').each(function(){
item_desc.push($(this).text());
});
$('.item_price').each(function(){
item_price.push($(this).text());
});
$.ajax({
url:"insert.php",
method:"POST",
data:{item_name:item_name, item_code:item_code, item_desc:item_desc, item_price:item_price},
success:function(data){
alert(data);
$("td[contentEditable='true']").text("");
for(var i=2; i<= count; i++)
{
$('tr#'+i+'').remove();
}
fetch_item_data();
}
});
});
function fetch_item_data()
{
$.ajax({
url:"fetch.php",
method:"POST",
success:function(data)
{
$('#inserted_item_data').html(data);
}
})
}
fetch_item_data();
});
</script>
insert.php
<?php
//insert.php
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["item_name"]))
{
$item_name = $_POST["item_name"];
$item_code = $_POST["item_code"];
$item_desc = $_POST["item_desc"];
$item_price = $_POST["item_price"];
$query = '';
for($count = 0; $count<count($item_name); $count++)
{
$item_name_clean = mysqli_real_escape_string($connect, $item_name[$count]);
$item_code_clean = mysqli_real_escape_string($connect, $item_code[$count]);
$item_desc_clean = mysqli_real_escape_string($connect, $item_desc[$count]);
$item_price_clean = mysqli_real_escape_string($connect, $item_price[$count]);
if($item_name_clean != '' && $item_code_clean != '' && $item_desc_clean != '' && $item_price_clean != '')
{
$query .= '
INSERT INTO item(item_name, item_code, item_description, item_price)
VALUES("'.$item_name_clean.'", "'.$item_code_clean.'", "'.$item_desc_clean.'", "'.$item_price_clean.'");
';
}
}
if($query != '')
{
if(mysqli_multi_query($connect, $query))
{
echo 'Item Data Inserted';
}
else
{
echo 'Error';
}
}
else
{
echo 'All Fields are Required';
}
}
?>
fetch.php
<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$output = '';
$query = "SELECT * FROM item ORDER BY item_id DESC";
$result = mysqli_query($connect, $query);
$output = '
<br />
<h3 align="center">Item Data</h3>
<table class="table table-bordered table-striped">
<tr>
<th width="30%">Item Name</th>
<th width="10%">Item Code</th>
<th width="50%">Description</th>
<th width="10%">Price</th>
</tr>
';
while($row = mysqli_fetch_array($result))
{
$output .= '
<tr>
<td>'.$row["item_name"].'</td>
<td>'.$row["item_code"].'</td>
<td>'.$row["item_description"].'</td>
<td>'.$row["item_price"].'</td>
</tr>
';
}
$output .= '</table>';
echo $output;
?>
Database
--
-- Table structure for table `item`
--
CREATE TABLE IF NOT EXISTS `item` (
`item_id` int(11) NOT NULL,
`item_name` varchar(250) NOT NULL,
`item_code` varchar(250) NOT NULL,
`item_description` text NOT NULL,
`item_price` varchar(30) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `item`
--
INSERT INTO `item` (`item_id`, `item_name`, `item_code`, `item_description`, `item_price`) VALUES
(1, 'Grease', 'HP38AST', 'General purpose Grease', '50'),
(2, 'Adhesive Epoxy', 'AS38DM33', 'Sealing epoxy', '20'),
(3, 'Connector 2 Way', 'PH848383', 'To be used for power supply connection in ABB Molding Machine', '500'),
(4, 'Laser Sensor', 'D383', 'Laser sensor for cutting machine', '10'),
(5, 'Power Supply 24V', 'D098', '24 Volt power supply for meter unit packing dept', '5'),
(6, 'V Belt 4', 'S34', 'V Belt for motor coupling drive used in milling machine, cutting machine, vibrator, seprator', '30'),
(7, 'Pressure Sensor', 'P38AST-3938B', 'Pressure sensor 4-20mA unit for storage tanks', '6'),
(8, 'LED Light Bulb', 'L24V3', '\n LED ights', '100'),
(9, 'Item 1', 'Code1', 'Description1', '10'),
(10, 'Item 2', 'Code 2', 'Description 2', '20'),
(11, 'Item 3Â ', 'Code 3Â ', 'Description 3Â ', '30');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `item`
--
ALTER TABLE `item`
ADD PRIMARY KEY (`item_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `item`
--
ALTER TABLE `item`
MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=12;
Hahah thanks so helpful
ReplyDeleteHello Sir :)
ReplyDeleteThank you for this<
but i face the problem which the button (save) does not work or does not send the data
Hello, I have followed your series of tutorial, I have faced a problem now, I want to insert a number first (for example, bill number), and then for the column bill_number(foreign key) will be the same throughout.
ReplyDeleteFor example, in this case, lets assume the description is a foreign key and I would like all items to have the same description.
The page will be a form or a table just like your tutorial, asking for the price. And then the same table as the tutorial up there. All the price will be the same with the one we input earlier and cannot be edit (content non editable).
Example:
Please Enter Price:[_______________]
Item Name | Item Code | Description | Price
a | a1 | xxx | $9
b | b1 | yyy | $9
Need help, thank you!
why append doesn't work in laravel?
ReplyDeletethanks so much for the nice tut
ReplyDeleteHeloo sir..In Same Situation i want to keeep my data betwee td tags same..mean if i got and options b/w these how i can show it every row??
ReplyDeletekindly rply asap...
Hello Sir i want to keep data b/w td tags same if i got and options b/w these how it would possible...kindly rply it as soon as possible
ReplyDeletegthg
ReplyDeletewith sweetAlert data on page show not proper work... only two data onpage show without refress please sol
ReplyDeletehow can I implement this in codeigniter?
ReplyDeleteThanks for the post with such a wonderful description. Helped me a lot.
ReplyDeleteI follow your tutorial. All codes are OK but when I push save button this is not working and don't show any error and also don't send any data in my database....
ReplyDeleteplease kindly say something about my problem...
I am new in php. I trying to know Multiple Inline Insert into Mysql using Ajax JQuery in PHP. Then I follow this tutorial on "webslesson". My all codes and database are Ok as like as webslesson web tutorial . But my save button doesn't work and don't send any data in my database and also not shown any error....
ReplyDeleteHi, thank you for the tutorial, it give me a clear understanding.
ReplyDeleteBut I have two more requirements:
1. How can I make the td non expandable (without giving fixed width in px, but i would like to give fixed %) while typing long data, 2. how can I include select option as td, and how will I take the value for insertion in DB? Please help how to do it. Thanks.
Thanks a lot sir, your tutorial very useful to me.
ReplyDeletenice one sir impressive
ReplyDeletehow to add in this table in update table it's possible to add update table
ReplyDeletecan you update this to pdo
ReplyDeletethanks
Can u please make a video on how to download pdf files in php as well as view the pdf
ReplyDeletei tried this code to my project but able insert single top most row, the other row are not getting inserted.
ReplyDeletethnx alot.. I tried it and worked correctly..
Deletesame...were you able to figure out how to fix it :(?
Deletesir, how to write code this project duplicate not allowed in mysqli select query write
ReplyDeleteThank you... this worked for me
ReplyDeleteHow to append this code in a large form.
ReplyDeleteyou are so good! thanks!
ReplyDeletehow we can insert inline when the table have foreign key
ReplyDeletehow to add html controls like dropdown, checkbox,radio buttons etc. so how can i add this fields in content editable multiple insertion
ReplyDeleteThnx alot... I tried it and worked correctly...
ReplyDeletebest
ReplyDeletebest
ReplyDeletecan you give this function of code for codeigniter framework
ReplyDeleteGood example Sir
ReplyDeletehow can i show item data in item enetred order. that means item 1, item 2 , item3 so on. and how can i post this to another page ? and when i post the table once then again post on a different table ? please help
ReplyDeletesorry sir 1st i ask about lot of help but after i tried so change in your code not change just hide code and got perfect table according to me. still i have i problem 1) i want to post 6 rows table every time with different values there on a different page. 2) post in a new table everytime. but i would like to thank you . your code helped me lot. Thank you
ReplyDeleteHappy New Year :-)
only single row is inserting. when i tired to insert more row values amd getting error alert.please help me.
ReplyDeletejizakAllal good work....
ReplyDeleteAnybody else got problem with alert?
ReplyDeleteGrateful to you and your skillful work.
ReplyDeleteHow to insert data in 2 different tables?
ReplyDeletehow to add hidden rows?
ReplyDeleteHello, how can i add arrow key navigation su h that when i edit a value and press down orr arrow key it automatically saves to the database
ReplyDeleteHello, how can i add arrow key navigation su h that when i edit a value and press down orr arrow key it automatically saves to the database
ReplyDelete