Multiple Inline Insert into Mysql using Ajax JQuery in PHP
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>IFIXNET 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;