How Can I Insert Multiple Rows Into a DB from my HTML Form with Multiple Rows Dynamically?

Question

so here's my situation. I have a form that gives the user the ability to add any number of rows to the form and input more data into those newly created rows (using javascript). I HAVE THIS ALREADY set up in the following code (I am using index.html, js/scripts.js and a php/upload.php files, all are externally linked, including an external CSS):

INDEX.HTML

<html>
<header>
<link rel="stylesheet" href="style.css" type="text/css">
 <script type="text/javascript" language="javascript" src="/jquery/js/jquery-1.9.1.js">   
</script>
<script src="http://www.mapquestapi.com/sdk/js/v7.0.s/mqa.toolkit.js?
key=Gmjtd%7Cluua2q6bn9%2C8g%3Do5-lzbsh"></script>
<script type="text/javascript" src="js/scripts.js"></script>
 <title>Central Office Company Survey</title>
</header>
<body onload="get_company_name();">
<h1>Central Office Company Survey</h1>
<div id='map' style='width:0px; height:0px; position:absolute'></div>

<input type="hidden" id="co_city">
<input type="hidden" id="co_state">
<input type="hidden" id="co_zipcode">

<table>
<th>Company</th>
<th>CO Name</th>
<th>Get Current Location</th>
<th>Lat</th>
<th>Long</th>
<th>Address</th>
<tr>
    <td><select id="company_name"></select></td>
    <td><input id="co_name" type="text"></td>
    <td><input type="submit" value="Get GPS" onclick="gpslookup()"></td>
    <td><input id="co_lat" type="text"></td>
    <td><input id="co_long" type="text"></td>
    <td><input id="co_address" type="text"></td>
</tr>
</table>
<table id="tabledata">
<th>Select</th>
<th>Border Location Name</th>
<th>Cable Location</th>
<th>Direction of Vault Wall</th>
<th>Cable Type</th>
<th>Cable Size (pairs)</th>
<th>Cable Gauge</th>
<th>Vertical(s) appeared on Verticals</th>
<th>Approximate Number of Jumpers</th>
<th>Are Protectors Still In?</th>
<th>Metered Distance</th>
<th class="comments">Central Office Comments</th>
<!--Begin Rows-->
<tr>
    <td><input type="checkbox"></td>
    <td><input id="border_location" type="text" name="txt[]"></td>
    <td><input id="cable_location" type="text" name="txt[]"></td>
    <td><input id="vault_direction" type="text" name="txt[]"></td>
    <td><input id="cable_type" type="text" name="txt[]"></td>
    <td><input id="cable_size" type="text" name="txt[]"></td>
    <td><input id="cable_gauge" type="text" name="txt[]"></td>
    <td><input id="vertical" type="text" name="txt[]"></td>
    <td><input id="jumpers" type="text" name="txt[]"></td>
    <td><input id="protectors" type="text" name="txt[]"></td>
    <td><input id="metered_dist" type="text" name="txt[]"></td>
    <td><input id="comments" type="text" name="txt[]"></td>
</tr>
</table>
<input id="addrow_btn" type="submit" value="Add New Row" onclick="addRow(); return false;" />
<input id="delrow_btn" type="submit" value="Delete Row" onclick="deleteRow(); return false;" />
<input id="submit" type="submit" value="Submit" onclick="uploaddata(); return false;"  />
</body>
</html>

As for the backend, I ONLY have the PHP and server side scripts able to submit information for that first row using the below code:

JAVASCRIPT FILE

function addRow() {

var table = document.getElementById("tabledata");
var rowCount = table.rows.length;
var row = table.insertRow(rowCount);

var colCount = table.rows[1].cells.length;

for(var i=0; i<colCount; i++) {

    var newcell = row.insertCell(i);

    newcell.innerHTML = table.rows[1].cells[i].innerHTML;
    //alert(newcell.childNodes);
    switch(newcell.childNodes[0].type) {
        case "text":
                newcell.childNodes[0].value = "";
                break;
        case "checkbox":
                newcell.childNodes[0].checked = false;
                break;
    }
}
//UPLOAD DATA
//Global variables
var survey = {
    'co_name' : "",
    'co_lat' : "",
    'co_long' : "",
    'co_address' : "",
    'border_location' : "",
    'cable_location' : "",
    'vault_direction' : "",
    'cable_type' : "",
    'cable_size' : "",
    'cable_gauge' : "",
    'vertical' : "",
    'jumpers' : "",
    'protectors' : "",
    'metered_dist' : "",
    'comments' : "",
    'company_name' : "",
    'co_city' : "",
    'co_state' : "",
    'co_zipcode' : ""
    }

function uploaddata() { 

//Read all of the data from the page
for (eID in survey) {
    survey[eID] = document.getElementById(eID).value;
}   
 //Insert data into database

            $.ajax({
            type: 'POST',
            url: './php/upload_survey.php',
            data: survey,
            async: false,
            dataType: 'text',
            success: function() {
            alert("Thank you. Your survey has been submitted.");
            window.location.reload();
            },

                error: function(jqXHR, textStatus, errorThrown) {
                    alert("Error... " + textStatus + "\n" + errorThrown);
                }
        });
    }

PHP FILE

//Assign passed parameters

    $co_name = $_POST['co_name'];
    $co_lat = $_POST['co_lat'];
    $co_long = $_POST['co_long'];
    $co_address = $_POST['co_address'];     
    $border_location = $_POST['border_location'];
    $cable_location = $_POST['cable_location'];
    $vault_direction = $_POST['vault_direction'];
    $cable_type = $_POST['cable_type'];
    $cable_size = $_POST['cable_size'];
    $cable_gauge = $_POST['cable_gauge'];
    $vertical = $_POST['vertical'];
    $jumpers = $_POST['jumpers'];
    $protectors = $_POST['protectors'];
    $metered_dist = $_POST['metered_dist'];
    $comments = $_POST['comments'];
    $txt = $_POST['txt'];

    $stringLogInfo =  "INFO: Location: $co_address CO Name = $co_name !!!\n\n";
    log_audit($logAuditFile, $logModule, $stringLogInfo);

//Parse and store the ini file, this will return an associative array
ini_set("display_errors", "1");
error_reporting(E_ALL);



//Insert Survey Form Information into the database
    $fieldlist=$vallist='';
    foreach ($_POST as $key => $value) {
        $fieldlist.=$key.',';
        $vallist.='\''.$value.'\','; 
    }
    $fieldlist=substr($fieldlist, 0, -1);
    $vallist=substr($vallist, 0, -1);
    $sql='INSERT INTO table_name ('.$fieldlist.') VALUES ('.$vallist.')';

mysql_query($sql) or die ("Unable to Make Query:" . mysql_error());

My objective up to this point, having already everything else sort of ready, is to be able to loop through all the data in the form, according to however many rows they create and submit all those new row values into the SQL Database into SEPARATE rows. Please Advise!

Just looking at your HTML, I wouldn't use IDs to distinguish inputs, that is what the name attribute is for — Jason Sperske
– Jason Sperske, Commented Mar 6, 2013 at 19:03
Yeah. You must do what @JasonSperske told you: use name attributes or you won't get anything. — Rodrigo Siqueira
– Rodrigo Siqueira, Commented Mar 6, 2013 at 19:12
Here is one way to organize this: jsfiddle.net/sperske/8e5ae — Jason Sperske
– Jason Sperske, Commented Mar 6, 2013 at 19:14
There are a lot of moving parts to this question, perhaps you should break it out into a set of more specific questions. — Jason Sperske
– Jason Sperske, Commented Mar 6, 2013 at 19:16

Jason Sperske · Accepted Answer · 2013-03-14 16:50:54Z

1

Focusing on the HTML part of this question here is an approach to grow a form dynamically:

First the HTML:

<table id="tabledata">
    <thead>
        <th>Select</th>
        <th>Border Location Name</th>
        <th>Cable Location</th>
        <th>Direction of Vault Wall</th>
        <th>Cable Type</th>
        <th>Cable Size (pairs)</th>
        <th>Cable Gauge</th>
        <th>Vertical(s) appeared on Verticals</th>
        <th>Approximate Number of Jumpers</th>
        <th>Are Protectors Still In?</th>
        <th>Metered Distance</th>
        <th class="comments">Central Office Comments</th>
    </thead>
    <tbody id="input"></tbody>
    <tbody id="template">
        <tr>
            <td><input name="selected" type="checkbox" /></td>
            <td><input name="border_location" type="text" /></td>
            <td><input name="cable_location" type="text" /></td>
            <td><input name="vault_direction" type="text" /></td>
            <td><input name="cable_type" type="text" /></td>
            <td><input name="cable_size" type="text" /></td>
            <td><input name="cable_gauge" type="text" /></td>
            <td><input name="vertical" type="text" /></td>
            <td><input name="jumpers" type="text" /></td>
            <td><input name="protectors" type="text" /></td>
            <td><input name="metered_dist" type="text" /></td>
            <td><input name="comments" type="text" /></td>
        </tr>
    </tbody>
</table>
<button id="ActionAddRow">Add Row</button>
<button id="ActionSubmit">Submit</button>

And This JavaScript:

$(function () {
    var addInputRow = function () {
        $('#input').append($('#template').html());
    };

    addInputRow();
    $('#ActionAddRow').on('click', addInputRow);
    $('#ActionSubmit').on('click', function () {
        var data = $('#input tr').map(function () {
            var values = {};
            $('input', $(this)).each(function () {
                if (this.type === 'checkbox') {
                    values[this.name] = this.checked;
                } else {
                    values[this.name] = this.value;
                }
            });
            return values;
        }).get();
        $.post('/echo/json/', {
            json: JSON.stringify(data),
            delay: 1
        }).done(function (response) {
            alert("POST success");
            console.log(response);
        });
    });
});

And then this CSS:

tbody#template {
    display: none;
}

Produces this demo

Here is a breakdown of what is happening. First the table element can define mutiple bodies, so I've added the HTML of an empty row of inputs and hidden (with CSS) it in a tbody with the ID of template. Using JavaScript, I then define a simple function that just appends the contents of that row to the tbody with the ID of inputs and I bind that function to the click event of a button. Then because the inputs tbody is starts out as empty I call that function once. Then for submitting the form, I select all rows of the inputs tbody and iterate over the inputs found inside them. Next, I am combining them into one large array of JavaScript objects with each element representing a row, and finally I'm posting this showing a round trip with this data from the client to the server (I'm using JSON2.js to serialize the data). Your PHP page would pick up from here to check them on the server, and (using a prepared statement) insert valid rows into the database.

Your PHP would take the POSTed values like this:

$value = json_decode($_POST['json']);

And treat the submitted data as an associative array. This approach uses an AJAX Form Post, so the response of the PHP page should be a valid JSON with a structure something like this:

{Success: true}

edited Mar 14, 2013 at 16:50

answered Mar 6, 2013 at 19:25

Jason Sperske

30.5k8 gold badges77 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

jflay Over a year ago

I edited my initial post, so you can see a little better overview of what I'm working with.. really appreciate your time and help. Whatever you have going above is not working with what I have for some reason, I even tried creating one html file with internal scripts and css. I am a junior level developer just figuring all of this out past couple months, so new to the SQL and jquery world (and javascript). I guess my next question will be, after this gets sorted, is how to create that "prepared statement" in php to iterate through all the rows and submit the values into new separate rows(SQL).

Jason Sperske Over a year ago

I've Updated my answer to show how you could combine the entered data into a single HTTP POST and then how from PHP you could read it back to a PHP var.

jflay Over a year ago

Still not working... =/ I have the most basic setup according to what you did and it won't work when I try to implement it. The rows won't add at all. I am running js/scripts.js in my header, whereas jsfiddle is doing it OnLoad event.. why won't it work when I am linking to my js file in my header?

Robert Over a year ago

@Jason-Sperske hey Jason, you are the man. I have seen plenty of solutions on stackoverflow.com for this topic and it took so long to finally land here (Ok maybe I was doing the wrong query but topics were "on the piece"). Super cool the template row :-). The code is so clean and makes a lot of sense now I'm unstuck.

Robert Over a year ago

For newbies like me, is worth to specify that $.post('/echo/json/' is a JSfiddle feature for simulations

|

Collectives™ on Stack Overflow

How Can I Insert Multiple Rows Into a DB from my HTML Form with Multiple Rows Dynamically?

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related