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 — apokryfos
– apokryfos, Commented Feb 14, 2018 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
– kscherrer, Commented Feb 14, 2018 at 12:39

kscherrer · Accepted Answer · 2018-02-14 12:56:20Z

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.

Praneeth Nidarshan · Accepted Answer · 2018-02-14 15:08:12Z

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, 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) . "')";

Claudiu D. · Accepted Answer · 2018-02-14 12:40:24Z

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, 2018 at 12:40

Claudiu D.

4864 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related