Saturday 11 March 2017

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>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;

43 comments:

  1. Hello Sir :)

    Thank you for this<
    but i face the problem which the button (save) does not work or does not send the data

    ReplyDelete
  2. 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.

    For 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!

    ReplyDelete
  3. why append doesn't work in laravel?

    ReplyDelete
  4. thanks so much for the nice tut

    ReplyDelete
  5. Heloo 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??
    kindly rply asap...

    ReplyDelete
  6. 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

    ReplyDelete
  7. with sweetAlert data on page show not proper work... only two data onpage show without refress please sol

    ReplyDelete
  8. how can I implement this in codeigniter?

    ReplyDelete
  9. Thanks for the post with such a wonderful description. Helped me a lot.

    ReplyDelete
  10. I 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....
    please kindly say something about my problem...

    ReplyDelete
  11. 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....

    ReplyDelete
  12. Hi, thank you for the tutorial, it give me a clear understanding.
    But 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.

    ReplyDelete
  13. Thanks a lot sir, your tutorial very useful to me.

    ReplyDelete
  14. how to add in this table in update table it's possible to add update table

    ReplyDelete
  15. can you update this to pdo
    thanks

    ReplyDelete
  16. Can u please make a video on how to download pdf files in php as well as view the pdf

    ReplyDelete
  17. i tried this code to my project but able insert single top most row, the other row are not getting inserted.

    ReplyDelete
    Replies
    1. thnx alot.. I tried it and worked correctly..

      Delete
    2. same...were you able to figure out how to fix it :(?

      Delete
  18. sir, how to write code this project duplicate not allowed in mysqli select query write

    ReplyDelete
  19. Thank you... this worked for me

    ReplyDelete
  20. How to append this code in a large form.

    ReplyDelete
  21. you are so good! thanks!

    ReplyDelete
  22. how we can insert inline when the table have foreign key

    ReplyDelete
  23. how to add html controls like dropdown, checkbox,radio buttons etc. so how can i add this fields in content editable multiple insertion

    ReplyDelete
  24. Thnx alot... I tried it and worked correctly...

    ReplyDelete
  25. can you give this function of code for codeigniter framework

    ReplyDelete
  26. how 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

    ReplyDelete
  27. sorry 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
    Happy New Year :-)

    ReplyDelete
  28. only single row is inserting. when i tired to insert more row values amd getting error alert.please help me.

    ReplyDelete
  29. Anybody else got problem with alert?

    ReplyDelete
  30. How to insert data in 2 different tables?

    ReplyDelete
  31. Hello, 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
  32. Hello, 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