How to insert HTML table data into database

Question

I have an HTMl table in which one column is editable I want to insert that data into my database.

1. First of all here is my running snippet.

var tableData = [{
    "Item Code": "C001",
    "Item Name": "Beverages",
    "Quantity": "0"

  },
  {
    "Item Code": "C003",
    "Item Name": "Juices",
    "Quantity": "0"
  },
  {
    "Item Code": "C004",
    "Item Name": "Soups",
    "Quantity": "0"

  },
  {
    "Item Code": "C005",
    "Item Name": "Cookies",
    "Quantity": "0"

  },

]

function addTable(tableValue) {
  var col = Object.keys(tableValue[0]);
  var countNum = col.filter(i => !isNaN(i)).length;
  var num = col.splice(0, countNum);
  col = col.concat(num);
  var table = document.createElement("table");
  var tr = table.insertRow(-1); // TABLE ROW.
  for (var i = 0; i < col.length; i++) {
    var th = document.createElement("th"); // TABLE HEADER.
    th.innerHTML = col[i];
    tr.appendChild(th);
    tr.classList.add("text-center");
    tr.classList.add("head")
  }
  for (var i = 0; i < tableValue.length; i++) {
    tr = table.insertRow(-1);
    for (var j = 0; j < col.length; j++) {
      let tabCell = tr.insertCell(-1);
      var tabledata = tableValue[i][col[j]];
      if (tabledata && !isNaN(tabledata)) {
        tabledata = parseInt(tabledata).toLocaleString('en-in')
      }
      if (tableData[i]['Item Code'] === tableData[i][col[j]]) {
        tabCell.innerHTML = tabledata;
      }
      if (tableData[i]['Item Name'] === tableData[i][col[j]]) {

        tabCell.innerHTML = tabledata;
      }
      if (tableData[i]['Quantity'] === tableData[i][col[j]]) {
        tabCell.setAttribute('contenteditable', true);
        tabCell.innerHTML = tabledata;
      }

      /* else {
        span = document.createElement("span");
        span.innerHTML = tabledata;
        tabCell.appendChild(span)
      } */
      if (j > 1)

        tabCell.classList.add("text-right");

    }
  }
  var divContainer = document.getElementById("HourlysalesSummary");
  divContainer.innerHTML = "";
  divContainer.appendChild(table);
  table.classList.add("table");
  table.classList.add("table-striped");
  table.classList.add("table-bordered");
  table.classList.add("table-hover");

}
addTable(tableData);

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.2/css/bootstrap.min.css">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.2/js/bootstrap.min.js"></script>
<form action="InsertQuantityIndent" method="post" id="form1">
  <div class="container" align="center">

    <div class="row">
      <div class="col-lg-12">
        <h6>OUTLET :</h6>
        <select name="outlet">
          <option>S001</option>
          <option>S002</option>
          <option>S003</option>
        </select>
      </div>
    </div>
    <div class="row">
      <div class="col-lg-12 table table-responsive" style="margin-bottom: 1px;">
        <table id="HourlysalesSummary"></table>
      </div>
    </div>
    <div>
      <button type="submit" class="btn btn-success" id="save">
     <i class="fas fa-save"></i> Save
    </button>
      <button type="submit" class="btn btn-warning" id="clear">
     <i class="fas fa-eraser"></i> Clear
    </button>

    </div>
  </div>
</form>

So in my snippet I have an HTML table with a dropdown outside it.

1. Quantity is editable
2. On clicking save I want to save the data into my db with outlet name
3. So as on my server code I am easily getting which outlet is selected but not getting the data of table
4. I have no idea how to get table data to my server end on save
5. And also I want to loop the data with my outlet at server end

Here is my java servlet code.

    protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {

      String outlet = request.getParameter("outlet");                 
      System.out.println(outlet);
           try {
         con = DBConnection.createConnection();
        statement = con.createStatement();              
        String query = "insert into student values(?,?,?,?)";
        PreparedStatement ps = con.prepareStatement(query); 
        ps.setInt(1, ItemCode);
        ps.setString(2, Quantity);  
        ps.setString(3, outlet);
        ps.executeUpdate(); 
        System.out.println("successfuly inserted");         
       } catch (SQLException e) {
        e.printStackTrace();
       }
       RequestDispatcher rd = request.getRequestDispatcher("home.jsp");
       rd.forward(request, response);
      }

My database should look like

I know how to do that but the issue I am facing is

1. How to get the table entries to server end i.e at servlet.
2. And after that I am getting one outlet but in database I have to insert it like in row, so I have to loop it.
3. important at UI I have to save only the rows which has quantity > 0
4. If from all 4 rows user want to 3rd row quantity to be 0 so no need to save that.
5. I have searched for several examples on the web but not found a desirable solution as I am using contenteditable not <input type=text,so not able to find how to save this table into db

Answer 1

The problem you are getting because you are not using form element . Form Element

As you can see select element is form element so the only select element data is going to server.

I modified your code so that it can send all data to the server.

Some CSS may be mismatched, but I didn't get chance to add CSS properly; I hope you can manage that on your own.

<html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.2/css/bootstrap.min.css">
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.2/js/bootstrap.min.js"></script>
<form action="InsertQuantityIndent" method="post" id="form1">
    <div class="container" align="center">

        <div class="row">
            <div class="col-lg-12">
                <h6>OUTLET :</h6>
                <select name="outlet">
                    <option>S001</option>
                    <option>S002</option>
                    <option>S003</option>
                </select>
            </div>
        </div>
        <div class="row">
            <div class="col-lg-12 table table-responsive" style="margin-bottom: 1px;">
                <table id="HourlysalesSummary"></table>
            </div>
        </div>
        <div>
            <button type="submit" class="btn btn-success" id="save">
                <i class="fas fa-save"></i> Save
            </button>
            <button type="submit" class="btn btn-warning" id="clear">
                <i class="fas fa-eraser"></i> Clear
            </button>

        </div>
    </div>
</form>
<script>
    var tableData = [{
        "Item Code": "C001",
        "Item Name": "Beverages",
        "Quantity": "0"

    },
    {
        "Item Code": "C003",
        "Item Name": "Juices",
        "Quantity": "0"
    },
    {
        "Item Code": "C004",
        "Item Name": "Soups",
        "Quantity": "0"

    },
    {
        "Item Code": "C005",
        "Item Name": "Cookies",
        "Quantity": "0"

    },

    ]

    function addTable(tableValue) {
        var col = Object.keys(tableValue[0]);
        var countNum = col.filter(i => !isNaN(i)).length;
        var num = col.splice(0, countNum);
        col = col.concat(num);
        var table = document.createElement("table");

        var tr = table.insertRow(-1); // TABLE ROW.
        for (var i = 0; i < col.length; i++) {
            var th = document.createElement("th"); // TABLE HEADER.
            th.innerHTML = col[i];
            tr.appendChild(th);
            tr.classList.add("text-center");
            tr.classList.add("head")
        }
        for (var i = 0; i < tableValue.length; i++) {
            tr = table.insertRow(-1);
            for (var j = 0; j < col.length; j++) {

                let tabCell = tr.insertCell(-1);
                var hiddenField = document.createElement("input");
                hiddenField.style.display = "none";
                var tabledata = tableValue[i][col[j]];
                if (tabledata && !isNaN(tabledata)) {
                    tabledata = parseInt(tabledata).toLocaleString('en-in')
                }
                if (tableData[i]['Item Code'] === tableData[i][col[j]]) {
                    tabCell.innerHTML = tabledata;
                    hiddenField.setAttribute('name', 'Item_Code');
                    hiddenField.setAttribute('value', tabledata);
                    tabCell.appendChild(hiddenField);
                }
                if (tableData[i]['Item Name'] === tableData[i][col[j]]) {

                    tabCell.innerHTML = tabledata;

                    hiddenField.setAttribute('name', 'Item_Name');
                    hiddenField.setAttribute('value', tabledata);
                    tabCell.appendChild(hiddenField);
                }
                if (tableData[i]['Quantity'] === tableData[i][col[j]]) {
                    var quantityField = document.createElement("input");

                    quantityField.style.border = "none";
                    quantityField.style["text-align"] = "center";
                    quantityField.setAttribute('name', 'Quantity');
                    quantityField.setAttribute('value', tabledata);
                    tabCell.appendChild(quantityField);

                }

                /* else {
                  span = document.createElement("span");
                  span.innerHTML = tabledata;
                  tabCell.appendChild(span)
                } */
                if (j > 1)

                    tabCell.classList.add("text-right");

            }
        }

        var divContainer = document.getElementById("HourlysalesSummary");
        divContainer.appendChild(table);
        table.classList.add("table");
        table.classList.add("table-striped");
        table.classList.add("table-bordered");
        table.classList.add("table-hover");
    }
    addTable(tableData);

</script>

</html>

There are many ways you can send data to server. here all validation you want you can made at server side only.

Second Approach is use prevent Default to stop form default submit behavior. Prevent Default Then use JavaScript to get all table data and perform validation and manipulation then send back to server.

For now, the above solution is working, take a look.