Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by Cameron Robertson
Cameron Robertson
3,620 Points

Dynamic Database Insert

Hello Team Tree House Friends! I am having a lot of difficulty creating an insert statement into my MySQL Database from a dynamically built form where users can add as many rows as they need. When the user fills out the fields after they've add more than one row, the first row is inserted into the database, but the following rows after the initial row are not.

Here is the HTML and JS:

        <div class="panel-body">
        <input type="hidden" name="username" value="<?php echo ($_SESSION['user']['username']) ?>"/> 
        <input type="hidden" name="carid" value="<?php echo $row['carid']?>"/> 
            <table id="report" class="table table-condensed">
                <thead>
                    <tr>
                        <th>Part</th>
                        <th>Description</th>
                        <th>Current Mileage</th>
                        <th>Tag Category</th>
                    </tr>
                </thead>
                <tr>
                    <td><input id="part1" name="part1" type="text" placeholder="Part" class="form-control input-md" required=""></td>
                    <td><input id="description1" name="description1" type="text" placeholder="Description" class="form-control input-md"></td>
                    <td><input id="currentmileage1" name="currentmileage1" type="text" placeholder="Current Mileage" class="form-control input-md" required="" value="<?php echo $row['mileage']?>"></td>

                    <td>
                        <select id="tag1" name="tag1" class="form-control">
                        <?php
                       $sql = "SELECT tag FROM tbl_tags";
                       $result = $db->prepare($sql);
                       $result->execute();
                       $rows = $result->fetchAll();

                        foreach ($rows as $row) {
                        echo "<option value='" . $row['tag'] . "'>" . $row['tag'] . "</option>";

} echo "</select>";

?> </select> </td>

                </tr>
                </table>
                <table id="tblControls">
                <tr>
                    <td colspan="5"><button onclick="addRow()"class="btn btn-xs btn-primary"><i class="fa fa-plus"></i> Add Row</button></td>


                </tr>


                <tr>
                <th colspan="5">Notes</th>
                <th>Location</th>

                </tr>
                <tr>
                    <td colspan="5">
                    <textarea id="notes" name="notes" type="text" placeholder="Notes" class="form-control input-md"></textarea>
                    </td>
                    <td><input id="location" name="location" type="text" placeholder="Shop or Location" class="form-control input-md"></td>
                </tr>
                <tr>
                    <td colspan="5"><button onclick="saveTableRecords()"class="btn btn-success"><i class="fa fa-plus"></i> Post</button></td>


                </tr>
            </table>
          </form>
          </div>
</div><!-- report -->

<?php endforeach; ?> <script> function addRow(){

var table = document.getElementById("report");

var i = $('#report tr').length

var row = table.insertRow(i); var cell1 = row.insertCell(0); var cell2 = row.insertCell(1); var cell3 = row.insertCell(2); var cell4 = row.insertCell(3); cell1.innerHTML = "<input id=\"part"+i+"\" name=\"part"+i+"\" type=\"text\" placeholder=\"Part\" class=\"form-control input-md\" required=\"\">"; cell2.innerHTML = "<input id=\"description"+i+"\" name=\"description"+i+"\" type=\"text\" placeholder=\"Description\" class=\"form-control input-md\" required=\"\">"; cell3.innerHTML = "<input id=\"currentmileage"+i+"\" name=\"currentmileage"+i+"\" type=\"text\" placeholder=\"Current Mileage\" class=\"form-control input-md\" required=\"\">"; cell4.innerHTML = "<SELECT id=\"tag"+i+"\"NAME=\"tag"+i+"\"><OPTION VALUE=\"\">Selection Option<OPTION VALUE=\"Brakes\">Brakes<OPTION VALUE=\"Power Train\">Power Train<OPTION VALUE=\"Engine\">Engine<OPTION VALUE=\"Suspension\">Suspension</SELECT>";

} function saveTableRecords() { $.ajax({ type: "POST", url: "datacollection.php", data: {RideLionData: getFormDataObject()} }).done(function( msg ){

    //You can create your completion client side
    //code here.  Show a success popup or somerthing else...
          alert( msg );
  });

} function getFormDataObject() { //Find our table and see how many rows it has. var table = document.getElementById("report"); var index = $('#report tr').length 

//Create an object that contains all the information needed to perform the update
//contains Part, Description, Mileage and TagCatagory


var dataObject = [];
for (var i = 1; i < index; i++) {
    dataObject.push({
        Part: $("#part"+i).val(),
        Description: $("#description"+i).val(),
        CurrentMileage: $("#currentmileage"+i).val(),
        //You need to fill out the drop down list below
        TagCatagory:""
    });
} 

return  {RideLionDate:dataObject};

}

function CreateDataObject() {

var table = document.getElementById("report");
var index = $('#report tr').length 

var dataObject = [];
for (var i = 1; i < index; i++) {
    dataObject.push({
        Part: $("#part"+i).val(),
        Description: $("#description"+i).val(),
        CurrentMileage: $("#currentmileage"+i).val(),
        TagCatagory:$("#tag"+i).val()
    });
} 

return dataObject;

}

</script>

Here is the SQL insert:

$notes = !empty($_POST[notes]) ? "$_POST[notes]" : "NULL";
$notes = addslashes($notes);
$mileage = !empty($_POST[currentmileage1]) ? "$_POST[currentmileage1]" : "NULL";
$mileage = str_replace(',', '', $mileage);
$createddate = date("d-m-Y");

$stmt = $db->prepare("INSERT INTO tbl_reportmap (username)
VALUES
(?)"); 

try { 
    $db->beginTransaction(); 
    $stmt->execute( array($_POST['username'])); 
    $reportID = $db->lastInsertId(); 
    $db->commit(); 

} catch(PDOExecption $e) { 
    $db->rollback(); 
    print "Error!: " . $e->getMessage() . "</br>"; 
} 


$query = "INSERT INTO tbl_usernotes (username, part, description, currentmileage, tag, createddate, notes, carid, reportid)
VALUES
('$_POST[username]', '$_POST[part1]', '$_POST[description1]', '{$mileage}', '$_POST[tag1]', '{$createddate}', '{$notes}', '$_POST[carid]','{$reportID}')"; 
try 
{ 

    $stmt = $db->prepare($query); 
    $stmt->execute(); 
} 
catch(PDOException $ex) 
{ 
    die("Failed to run query: " . $ex->getMessage()); 
}

2 Answers

Jeremy Germenis
Jeremy Germenis
29,854 Points
Jeremy Germenis
Jeremy Germenis
29,854 Points

I have done this exact setup. The best option I came up with was to:

Inserting into DB

  1. put the dynamic form fields into an array
  2. base64_encode() + serialize() the array (a reliable way to store an array)
  3. insert the encoded array into a single field

Retrieving from DB

  1. base64_decode() + unserialize() the array (turning it back into an array)
  2. use a foreach loop to extract the field value

You will also need to store your js array values in JSON if you are passing them to php.

Cameron Robertson
Cameron Robertson
3,620 Points

Hey Jeremy,

Thank you for the reply. I haven't done that before so I will need to look into it.

I appreciate the comment! :)

Thanks, Cameron

Jeremy Germenis
Jeremy Germenis
29,854 Points
Jeremy Germenis
Jeremy Germenis
29,854 Points

Another option would be to have a second database with a relation to the first and loop through the dynamic field sets inserting them into the second database.