Rudibwoy Rudibwoy -4 years ago 63
MySQL Question

How to store dynamically added rows into database mysql php

I'm stuck on this for a couple of days. I hope any of you can help me out.

Usually when you ask user input through an HTML form you can access the data by calling the $_POST function.

The problem in this approach is that I don't have a static set of input fields. When the user clicks on the addition button another input field shows up and they can make as many input fields as feel neccesary.

I do know that I should loop through it, but I don't have alot of experience in doing this. See the code below which adds dynamic rows in Jquery/Javascript:

<script type="text/javascript">
$(document).ready(function(){

var counter = 2;

$("#addButton").click(function () {

if(counter>10){
alert("Only 10 textboxes allow");
return false;
}

var newTextBoxDiv = $(document.createElement('div'))
.attr("id", 'TextBoxDiv' + counter);

newTextBoxDiv.after().html('<label>Source Name #'+ counter + ' : </label>' +
'<input type="text" placeholder ="Source Name" name="source_name' +
counter +
'" id="textbox' + counter + '" value="" > <label>IP address from #'+
counter + ' : </label>' +
'<input type="text" placeholder="IP Address From"
name="source_ip_from' + counter +
'" id="textbox' + counter + '" value="" > <label>IP address till #'+
counter + ' : </label>' +
'<input type="text" placeholder="IP Address Till"
name="source_ip_till' + counter +
'" id="textbox' + counter + '" value="" >'
);


newTextBoxDiv.appendTo("#TextBoxesGroup");


counter++;
});

$("#removeButton").click(function () {
if(counter==2){
alert("No more textbox to remove");
return false;
}

counter--;

$("#TextBoxDiv" + counter).remove();

});

$("#getButtonValue").click(function () {

var msg = '';
for(i=1; i<counter; i++){
msg += "\n Textbox #" + i + " : " + $('#textbox' + i).val();
}
alert(msg);
});
});
</script>


The code below is where the form is created and posted in HTML/PHP

<form method="post" class="form-inline" action="addverkeersstroom.php">
<div id='TextBoxesGroup'>
<div id="TextBoxDiv1">
<label>Source Data : </label>
<input type="text" class="form-control mb-2 mr-sm-2 mb-sm-0"
placeholder="Source" name="source_name">
<input type="text" class="form-control mb-2 mr-sm-2 mb-sm-0"
placeholder="APP Nummer" name="source_app">
<?php
mysql_connect('localhost', '', '');
mysql_select_db('');

$sql = "SELECT * FROM zone";
$result = mysql_query($sql);

echo "<select name='zone_1' id='zone_1' class='form-control
ip_or_zone'>";
echo "<option value=''></option>";
while ($row = mysql_fetch_array($result)) {

echo "<option value='". $row['idzone'] . "'>" . $row['zone_naam'] . "
</option>";
}
echo "</select>";

?>
OR <input type="text" class="form-control ip_or_zone" placeholder="IP
Address from" name="source_ip_from">
<input type="text" class="form-control ip_or_zone" placeholder="IP
Address till" name="source_ip_till">
<input type="text" class="form-control mb-2 mr-sm-2 mb-sm-0"
placeholder="NAT IP" name="source_nat">
<input type="text" class="form-control mb-2 mr-sm-2 mb-sm-0"
placeholder="Netmask" name="source_netmask">
</div>
</div>
<input type='button' value='+' id='addButton'>
<input type='button' value='-' id='removeButton'>


And last but not least the code below that should insert it into the database.

<?php
session_start();

if(isset($_POST['submit'])) {

echo $source_name = $_POST['source_name'];
echo $source_app = $_POST['source_app'];
echo $source_zone = $_POST['zone_1'];
echo $source_ip_from = $_POST['source_ip_from'];
echo $source_ip_till = $_POST['source_ip_till'];
echo $source_nat = $_POST['source_nat'];
echo $source_netmask = $_POST['source_netmask'];

echo $destination = $_POST['destination'];
echo $dest_app = $_POST['destination_app'];
echo $dest_zone = $_POST['zone_2'];
echo $dest_ip_from = $_POST['dest_ip_from'];
echo $dest_ip_till = $_POST['dest_ip_till'];
echo $dest_nat = $_POST['destination_nat'];
echo $dest_netmask = $_POST['destination_netmask'];

mysql_connect('localhost', '', '');
mysql_select_db('');

//Maak een nieuwe verkeersstroom aan in de database
mysql_query("INSERT INTO verkeersstroom(changes_idchange, protocol, tcpudp,
port_nr)
VALUES('".$changeid."', '".$protocol."', '".$tcpudp."', '".$portnr."')");
$verkeerstroomid = mysql_insert_id();

//Maakt de eigenschappen van de verkeersstroom aan
mysql_query("INSERT INTO verkeersstroom_eigenschappen(changes_idchange,
verkeersstroom_idverkeersstroom, source_name, source_app, source_zone,
source_ip_from, source_ip_till, source_nat, source_netmask, destination,
destination_app, destination_zone, destination_ip_from, destination_ip_till,
destination_nat, destination_netmask)
VALUES('".$changeid."', '".$verkeerstroomid."', '".$source_name."',
'".$source_app."', '".$source_zone."', '".$source_ip_from."',
'".$source_ip_till."', '".$source_nat."', '".$source_netmask."',
'".$destination."', '".$dest_app."', '".$dest_zone."', '".$dest_ip_from."',
'".$dest_ip_till."', '".$dest_nat."', '".$dest_netmask."')");

header("Location:"."");
}


After submitting the dynamic values are like: source_name2, source_ip_from2, source_ip_till2, destination2, dest_ip_from2, dest_ip_till2
and counting up to the amount of rows generated by pressing the addition(+) button.

Like I said before I need to loop through the query somehow like this;
foreach source_name(or source_name2 or 3,4 etc), source_ip_from, source_ip_till, destination, dest_ip_from, dest_ip_till

Insert it seperately into the table like everything with a 2 in the same row and everything with a 3 in the same row and counting up like that.

I hope anyone can help me it is giving me an headache ;-p

Answer Source

html forms allow you to give input fields a name: <input type='text' name='name' value='22'>

In the event you get multiple inputs but you do not know how many. You can make an array of the input fields by changing the <input name='name'> to <input name='name[]'>

Then you can write a simple for loop in php to work through the array:

<?php
$count = count($_POST['name']);
$name = $_POST['name'];
for($i = 0; $i < $count; $i++){
    // do your sql stuff with:
    $name[$i]; // this is the value of the inputfield number $i.
}
?>

Also I'd advice to look into using prepared statements. With your current php code you are vulnerable to SQL-injection.

<?php
$sql = "INSERT INTO verkeersstroom(`changes_idchange`, `protocol`, `tcpudp`, `port_nr`) 
VALUES( ?, ?, ?, ?);";
$stmt = mysql_connect('localhost', '', '')->prepare($sql);
$stmt->bind_param("ssss", $changeid, $protocol, $tcpudp, $portnr);
$stmt->execute;
?>

This is an example, for more look at: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download