How to insert the multiple row values of a table into the database using php

Question

<html>
    <body>
        <form method="POST" action="">
          <label>Select your Category:</label>
          <input type="text" name="category" id="cat-list"/>
          </br>
          <label> Display Name:</label>
          <input type="text" name="display_name" id="displayname"/>
          </br>
          <label> Select your Subcategory:</label>
          <input type="text" name="pagename" id="subcat-list"/>
          </br>
          <label>Set Order</label>
          <select name="privilage" id="privilage" required>
            <option value="">Select</option>
            <option value="1">1</option>
            <option value="2">2</option>
          </select>
          </br>
          <button type="button" name="add" id="savebutton"><i class="icon-check-sign" aria-hidden="false"></i>Add</button>

          <table id="pTable" class="table table-hover" width="100%" cellspacing="0" style="border: 1px; height:10px;" >
            <thead style="background-color:#CCE5FF">
              <tr id="row">
                <th>Category</th>
                <th>DisplayName</th>
                <th>Subcategory</th>
                <th>Order</th>
                <th colspan=2>Action</th>
              </tr>
            </thead>
            <tbody>
            </tbody>
          </table>
          <button type="submit" name="import" > Import Database</button>
        </form>

        <script src="  https://code.jquery.com/jquery-3.2.1.js" type="text/javascript"></script> 
        <script>
        var i=0;
        currentRow = null;
        $("button#savebutton").click(function(){  
            var cat = $("#cat-list").val();
            var display = $("#displayname").val();
            var subcat = $("#subcat-list").val(); 
            var order = $("#privilage").val();                              
            i++;    
            //Inserting first row to the table with edit and delete button  
            var new_row = "<tr id='row"+i+"' class='info'><td class='cat'>" + cat + "</td><td class='display'>" + display + "</td><td class='type'>" + subcat +"</td><td>"+order +"</td><td><span class='editrow'><a class='fa fa-edit' href='javascript: void(0);'>Edit</a></span></td><td><span class='deleterow'><a class='glyphicon glyphicon-trash' href=''>Delete</a></span></tr>";   
            //Appending the new row to the table                        
            if(currentRow){
                $("table tbody").find($(currentRow)).replaceWith(new_row);
                currentRow = null;
            }
            else{
                $("table tbody").append(new_row);
            }
        });
        //Editing the row entered
        $(document).on('click', 'span.deleterow', function () {
            $(this).parents('tr').remove();
            return false;
        });

        //Deleting the row          
        $(document).on('click', 'span.editrow', function () {
            currentRow= $(this).parents('tr');                  
        });
        </script>
        <?php
        require('connection1.php');
        echo 123;

        if(isset($_POST['import'])){
            $query=mysqli_query($db,"INSERT INTO selectedpages(display_name,category,pagename,privilage) values('".$_POST['display_name']."','".$_POST['category']."','".$_POST['pagename']."','".$_POST['privilage']."')") or die(mysqli_error($db));      
            if($query>0){
            echo "Success";
            }
            else {
                echo "failed";
            }
        }
        ?>

Content: The code is to enter the multiple row values to the database,but the problem is only the last row value is entering into database.If there are 3 rows ,only the third row details entering into the database.Firstly, the form having a add button and an import button.Add button is used for adding the rows and then have to click the import button for entering these row values to the database.

Please ensure you only include the **minimal** verifyable and complete example. [More info on how to create an MVCE](https://stackoverflow.com/help/mcve) — apokryfos, Feb 14 '18 at 12:31
When you submit your form it sends the inputs via post, not the whole table. And since in the inputs are still the values of the last added row, it sends these values via POST, which are getting inserted into the db. — kscherrer, Feb 14 '18 at 12:39

score 1 · Answer 1 · answered Feb 14 '18 at 12:56

When you submit your form it sends the inputs via post, not the whole table. And since in the inputs are still the values of the last added row, it sends these values via POST, which are getting inserted into the db.

Here is how you could solve it:
1) intercept the formsubmit with a javascript onclick method (where you will have to use e.preventDefault to stop the actual submit).
2) read all table rows with jQuery, create an array selectedPages with them that looks something like this:
^{(this is how it should look after, its not how you create it :P)}

selectedPages {
    [
        "category": "Foo",
        "displayName": "FooBar",
        "pageName": "Bar",
        "privilage": 1
    ],[
        "category": "Foo",
        "displayName": "FooBar",
        "pageName": "Bar",
        "privilage": 1
    ],[
        "category": "Foo",
        "displayName": "FooBar",
        "pageName": "Bar",
        "privilage": 1
    ]
}

3) create new php file where you put your php code that you currently have in the bottom of the same file.
4) make an AJAX post request to the new php file send your selectedPages as data
5) in the php file, iterate over the array and insert each item in your DB.

score 0 · Answer 2 · answered Feb 14 '18 at 12:40

0

Every input that u are adding in the #pTable table should be array, for example, product[], then in your PHP code you traverse the array in a for/foreach and insert each row in DB

answered Feb 14 '18 at 12:40

Claudiu D.

446
3
11

Praneeth Nidarshan · Accepted Answer · 2018-02-14T15:08:12.520

You must prevent the form submission. And create a JSON data string by reading the table #pTable.
Add a hidden field <input type="hidden" name="import_data"> inside your form. It passes the JSON string to the server side.

I have made some changes in you HTML:

var i = 0;
currentRow = null;
$("button#savebutton").click(function() {

  var cat = $("#cat-list").val();
  var display = $("#displayname").val();
  var subcat = $("#subcat-list").val();
  var order = $("#privilage").val();
  i++;


  //Inserting first row to the table with edit and delete button  
  var new_row = "<tr id='row" + i + "' class='info'><td class='cat'>" + cat + "</td><td class='display'>" + display + "</td><td class='type'>" + subcat + "</td><td>" + order + "</td><td><span class='editrow'><a class='fa fa-edit' href='javascript: void(0);'>Edit</a></span></td><td><span class='deleterow'><a class='glyphicon glyphicon-trash' href=''>Delete</a></span></tr>";

  //Appending the new row to the table                         

  if (currentRow) {
    $("table tbody").find($(currentRow)).replaceWith(new_row);
    currentRow = null;
  } else {
    $("table tbody").append(new_row);
  }

});
//Editing the row entered
$(document).on('click', 'span.deleterow', function() {
  $(this).parents('tr').remove();
  return false;
});

//Deleting the row          
$(document).on('click', 'span.editrow', function() {
  currentRow = $(this).parents('tr');
});

$('#import-form').submit(function(event) {

  var import_data = $('[name=import_data]');

  if (import_data.val() == "") {
    event.preventDefault();
    var tbl = $('table#pTable tr').get().map(function(row) {
      return $(row).find('td').get().map(function(cell) {
        return $(cell).html();
      });
    });

    var convertTableToJson = function() {
      var rows = [];
      $('table#pTable tr').each(function(i, n) {
        // Ignore empty
        if (i != 0) {
          var $row = $(n);
          rows.push({
            display_name: $row.find('td:eq(0)').text(),
            category: $row.find('td:eq(1)').text(),
            pagename: $row.find('td:eq(2)').text(),
            privilage: $row.find('td:eq(3)').text()
          });

        }
      });
      return JSON.stringify(rows);
    };

    import_data.val(convertTableToJson);
    $(this).submit();
  }


});

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<html>

<body>
  <form method="POST" action="" id="import-form">
    <label>Select your Category:</label>
    <input type="text" name="category" id="cat-list" />
    </br>

    <label> Display Name:</label>
    <input type="text" name="display_name" id="displayname" />
    </br>

    <label> Select your Subcategory:</label>
    <input type="text" name="pagename" id="subcat-list" />
    </br>


    <label>Set Order</label>
    <select name="privilage" id="privilage" required>
  <option value="">Select</option>
  <option value="1">1</option>
  <option value="2">2</option>
</select>
    </br>

    <button type="button" name="add" id="savebutton"><i class="icon-check-sign" aria-hidden="false"></i>Add</button>

    <table id="pTable" class="table table-hover" width="100%" cellspacing="0" style="border: 1px; height:10px;">
      <thead style="background-color:#CCE5FF">
        <tr id="row">
          <th>Category</th>
          <th>DisplayName</th>
          <th>Subcategory</th>
          <th>Order</th>
          <th colspan=2>Action</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>
    <button type="submit" name="import"> Import Database</button>

    <input type="hidden" name="import_data">

  </form>
</body>

</html>

and also replace your PHP block,

<?php
        require('connection1.php');
        echo 123;

        if(isset($_POST['import'])){
            $query=mysqli_query($db,"INSERT INTO selectedpages(display_name,category,pagename,privilage) values('".$_POST['display_name']."','".$_POST['category']."','".$_POST['pagename']."','".$_POST['privilage']."')") or die(mysqli_error($db));      
            if($query>0){
            echo "Success";
            }
            else {
                echo "failed";
            }
        }
        ?>

with below

<?php 

require('connection1.php');

if (isset($_POST['import_data'])) {
          $import_data = json_decode($_POST['import_data'], true);

          $query = "INSERT INTO selectedpages (display_name,category,pagename,privilage) VALUES ";

          $values = array();

          // Create value rows array 
          foreach ($import_data as $key => $row) {
            // Added single quotes
            $values[]  = "('" . implode("', '", $row) . "')";
          }

          $query .= implode(", ", $values);
          echo $query;

          $execute_query = mysqli_query($db, $query) or die(mysqli_error($db));  

          if($execute_query>0)
          {
            echo "Success";
          }
        }

?>

It shows an error - INSERT INTO selectedpages (display_name,category,pagename,privilage) VALUES (hai, hello, 123, 1), (hello, world, 123, 2)Unknown column 'hai' in 'field list' — Ani, Feb 14 '18 at 14:21
@Ani, the error occurred because of missing single quotes of values, try the PHP block of mine now. I have made a little change on $values[] = "('" . implode("', '", $row) . "')"; — Praneeth Nidarshan, Feb 14 '18 at 15:11

How to insert the multiple row values of a table into the database using php

3 Answers3