How to insert data to a mysql database from an HTML Table

Question

I have a HTML table with information. Right now I can add rows and delete the rows with a button using javascript. I can also add the information to the database directly using the Add Rows button, and remove the data from the database with the Delete Rows button. But I don't want to use those buttons because I think it is better to have another button for inserting all the information to the database at once. So I need suggestions on how to read information from a HTML table and inserts its data to a mysql database.

Here is the code: Right now the code does not insert data to the database.

<HTML>
<HEAD>
<TITLE> Add/Remove dynamic rows in HTML table </TITLE>
<SCRIPT language="javascript">
    function addRow(tableID) {

        var table = document.getElementById(tableID);

        var rowCount = table.rows.length;
        var row = table.insertRow(rowCount);

        var cell1 = row.insertCell(0);
        var element1 = document.createElement("input");
        element1.type = "checkbox";
        element1.name="chkbox[]";
        cell1.appendChild(element1);

        var cell2 = row.insertCell(1);
        cell2.innerHTML = rowCount;

        var cell3 = row.insertCell(2);
        cell3.innerHTML = rowCount;

        var cell4 = row.insertCell(3);
        cell4.innerHTML = rowCount;

        var cell5 = row.insertCell(4);
        cell5.innerHTML = rowCount;

        var cell6 = row.insertCell(5);
        cell6.innerHTML = rowCount;
    }

    function deleteRow(tableID) {
        try {
        var table = document.getElementById(tableID);
        var rowCount = table.rows.length;

        for(var i=1; i<rowCount; i++) {
            var row = table.rows[i];
            var chkbox = row.cells[0].childNodes[0];
            if(null != chkbox && true == chkbox.checked) {
                table.deleteRow(i);
                rowCount--;
                i--;
            }
        }
        }catch(e) {
            alert(e);
        }
    }

</SCRIPT>
</HEAD>
<BODY>

<INPUT type="button" value="Add Row" onclick="addRow('dataTable')" />

<INPUT type="button" value="Delete Row" onclick="deleteRow('dataTable')" />

<TABLE id="dataTable" border="1">
<tr>
<th><INPUT type="checkbox" name="chk[]"/></th>
<th>Make</th>
<th>Model</th>
<th>Description</th>
<th>Start Year</th>
<th>End Year</th>
</tr>
</TABLE>

</BODY>
</HTML>

I don't believe your deleteRow() call is in fact removing anything from the database based on the above code. — Chris Rasco, Oct 09 '13 at 20:08
Yes it is not adding/removing from the database right now but I don't want to do it that way. Instead I want to insert to the database while all information is inserted on the HTML table. — EM10, Oct 09 '13 at 20:10
There is no concept of a server reading from an HTML table. Tables are just a nice visual way of presenting data. What you want is an HTML [form](https://developer.mozilla.org/en-US/docs/Web/Guide/HTML/Forms). — acobster, Oct 09 '13 at 20:18
Also it's worth noting that it's not necessarily "better" to insert all the information into the database at once; that depends on what you're trying to do. Lastly, this ought to go with out saying, but PLEASE PLEASE PLEASE *VALIDATE YOUR DATA* before the db insert. — acobster, Oct 09 '13 at 20:20
I think you can use JSON, here https://stackoverflow.com/a/23770671/2080202 — Hossam, Jul 28 '17 at 23:52

score 7 · Answer 1 · edited Mar 18 '17 at 21:30

Yes.. You have good JavaScript code to adding dynamic content..wow.. Now you want to insert that content to MySQL table..yes you can... Before that small modification to do your code.. First you should understand insert something to database, you have a HTML form element.. and controls..you can add dynamically HTML form element as following

  function addRow(tableID) { 

        var table = document.getElementById(tableID);

        var rowCount = table.rows.length;
        var row = table.insertRow(rowCount);

        var cell1 = row.insertCell(0);
        var element1 = document.createElement("input");
        element1.type = "checkbox";
        element1.name="chkbox[]";
        cell1.appendChild(element1);

        var cell2 = row.insertCell(1);
        cell2.innerHTML = "<input type='text' name='item[]'>";

        var cell3 = row.insertCell(2);
        cell3.innerHTML = "<input type='text'  name='price[]' />";

        var cell4 = row.insertCell(3);
        cell4.innerHTML =  "<input type='text'  name='qty[]' />";
        }

keep your delete method same, but change this line only

var i=1

to

var i=0

Now Change your HTML code as following , make sure your table body tag has a id named "dataTable", and remove you check box ,put form element to cover your table..bang...

<INPUT type="button" value="Add Row" onClick="addRow('dataTable')" />

<INPUT type="button" value="Delete Row" onClick="deleteRow('dataTable')" />

<form action="" method="post" name="f">  

<TABLE width="425" border="1">
<thead>
<tr>
<th width="98"></th>
<th width="94">Item</th>
<th width="121">Price</th>
<th width="84">Qty</th>

</tr>
</thead>
<tbody id="dataTable">

</tbody>
</TABLE>

<INPUT type="submit" value="Insert" name="submit" />
</form>

// create mysql database and then create table // following is the example

CREATE TABLE `your_table_name` (
  `id` int(11) NOT NULL auto_increment,
  `item` varchar(200) NOT NULL,
  `price` varchar(200) NOT NULL,
  `qty` varchar(200) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

greate ... now this is the interesting part.. I use the php language to insert data to database.. make sure you should create database connection..

<?php
    if($_POST[submit])
    {
     foreach ($_POST['item'] as $key => $value) 
        {
            $item = $_POST["item"][$key];
            $price = $_POST["price"][$key];
            $qty = $_POST["qty"][$key];

            $sql = mysql_query("insert into your_table_name values ('','$item', '$price', '$qty')");        
        }

    }   
?>

I think this post is important to all ..

score 1 · Answer 2 · answered Oct 09 '13 at 21:40

First of all you should separate client and server side:

Client is browser, and HTML table is stored in "browser's" memory, all editorial is done on client's computer, you can disconnect from internet and still use this page - and it will work (add/delete rows)

Server's side works on remote server and don't know what rows/columns are inserted into client's HTML table.

So, you need some mechanism to send data from client to server, after you finished.

Second item: HTML table and Relational Database table are different entities, HTML table is only a visual representation of data, relational database table is entity in specific database (you can have several databases, each database can have several tables) stored on disc (on server usually).

HTML table can have dynamic rows/columns, but RD table can have dynamic rows only, NOT columns, (not fairly true, some RDBMS allows removing columns).

Finally - you should solve 2 items:

Sending data from client to server, this can be achieved via placing <form action="phpscript.php">...</form> around <table> and adding "submit" button to it, dont forget to store amount of columns/rows in some "hidden" fields, also - I suppose you need data in this cells, so add <input> in each HTML table cell
Storing data on server - for mysql you really can go with dynamic columns add/remove, but also you can just store ROW and COLUMN index with data, like:

0, 0, dataincell_0_0
1, 0, dataincell_1_0

How to insert data to a mysql database from an HTML Table

2 Answers2

Linked