Hello-
I'm trying to develop a form to dynamically input data into a table named tblRates_balance. I'm attempting to modify the code found here http://blog.calendarscripts.info/dynamically-adding-input-form-fields-with-jquery/.
Here is the table layout:
ID | depratecat | MinBalance | InterestRate | APY | suborder
row|
ID is auto-increment.
The form fields for depratecat are visible in my code only for testing; normally the user would not be able to change this value. The value of depratecat would come from a POST and should be the same for all rows inputted or edited in this instance.
The problems:
1. The "Add row" script function does not work.
2. Won't add new data thru form; nothing happens.
Editing or deleting pre-existing rows seems to work.
Below is my test code. Any help would be greatly appreciated.
// below value 14 is just for testing. In use, the value for $new_depratecat would come from a POST
$new_depratecat='14';
// store in the DB
if(!empty($_POST['ok'])) {
// first delete the records marked for deletion. Why? Because we don't want to process them in the code below
if( !empty($_POST['delete_IDs']) and is_array($_POST['delete_IDs'])) {
// you can optimize below into a single query, but let's keep it simple and clear for now:
foreach($_POST['delete_IDs'] as $ID) {
$sql = "DELETE FROM tblRates_balance WHERE ID=$ID";
$link->query($sql);
}
}
// now, to edit the existing data, we have to select all the records in a variable.
$sql="SELECT * FROM tblRates_balance WHERE depratecat='$new_depratecat' ORDER BY suborder";
$result = $link->query($sql);
// now edit them
while($rates = mysqli_fetch_array($result)) {
// remember how we constructed the field names above? This was with the IDea to access the values easy now
$sql = "UPDATE tblRates_balance SET depratecat='".$_POST['depratecat'.$rates['ID']]."',
MinBalance='".$_POST['MinBalance'.$rates['ID']]."',
InterestRate='".$_POST['InterestRate'.$rates['ID']]."',
APY='".$_POST['APY'.$rates['ID']]."',
suborder='".$_POST['suborder'.$rates['ID']]."'
WHERE ID='$rates[ID]'";
$link->query($sql);
}
// (feel free to optimize this so query is executed only when a rate is actually changed)
// adding new
if(!empty($_POST['$depratecat'])) {
foreach($_POST['depratecat'] as $cnt => $depratecat) {
$sql = "INSERT INTO tblRates_balance (depratecat, MinBalance, Interst_Rate, APY, suborder) VALUES ('$depratecat','".$_POST['MinBalance'][$cnt]."', '".$_POST['InterestRate'][$cnt]."', '".$_POST['APY'][$cnt]."','".$_POST['suborder'][$cnt]." );";
$link->query($sql);
}
}
}
// select existing rates here
$sql="SELECT * FROM tblRates_balance where depratecat='$new_depratecat' ORDER BY ID";
$result = $link->query($sql);
?>
<html>
<head>
<title>Simple example of dynamically adding rows with jQuery</title>
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.3/jquery-ui.min.js"></script>
</head>
<body>
<div style="width:90%;margin:auto;">
<h1>Simple example of dynamically adding rows with jQuery </h1>
<form method="POST">
<div ID="itemRows">
Dep_rate_cat:<input type="text" name="depratecat" size="30" value="<?php $new_depratecat; ?>"/>
Minimum Balance: <input type="text" name="Minimum_Balance" size="30" />
Interest Rate: <input type="text" name="InterestRate" />
APY: <input type="text" name="APY" />
Order: <input type="text" name="suborder" size="2"/>
<input onClick="addRow(this.form);" type="button" value="Add row" /> (This row will not be saved unless you click on "Add row" first)
<?php
// let's assume you have the rate data from the DB in variable called $rates
while($rates = mysqli_fetch_array($result)): ?>
<p ID="oldRow<?=$rates['ID']?>">
<?php echo $rates['ID']; ?>
Dep_rate_cat:<input type="text" name="depratecat<?=$rates['ID']?>" size="4" value="<?=$rates['depratecat']?>" />
Minimum Balance: <input type="text" name="MinBalance<?=$rates['ID']?>" value="<?=$rates['MinBalance']?>" />
Interest Rate: <input type="text" name="InterestRate<?=$rates['ID']?>" value="<?=$rates['InterestRate']?>" />
APY: <input type="text" name="APY<?=$rates['ID']?>" value="<?=$rates['APY']?>" />
Order: <input type="text" name="suborder<?=$rates['ID']?>" value="<?=$rates['suborder']?>" />
<input type="checkbox" name="delete_IDs[]" value="<?=$rates['ID']?>"> Mark to delete</p>
<?php endwhile;?>
</div>
<p><input type="submit" name="ok" value="Save Changes"></p>
</form>
</div>
<script type="text/javascript">
var rowNum = 0;
function addRow(frm) {
rowNum ++;
var row = '<p id="rowNum'+rowNum+'">
Cat:<input type="text" name="depratecat" value="'+frm.add_depratecat.value+'">
Minimum Balance: <input type="text" name="MinBalance[]" value="'+frm.add_MinBalance.value+'">
Interest Rate: <input type="text" name="InterestRate[]" value="'+frm.add_InterestRate.value+'">
APY: <input type="text" name="APY[]" value="'+frm.add_APY.value+'">
Order: <input type="text" name="suborder[]" value="'+frm.add_suborder.value+'">
<input type="button" value="Remove" onclick="removeRow('+rowNum+');"></p>';
jQuery('#itemRows').append(row);
frm.add_depratecat.value = '';
frm.add_MinBalance.value = '';
frm.add_InterestRate.value = '';
frm.add_APY.value = '';
frm.add_suborder.value = '';
}
function removeRow(rnum) {
jQuery('#rowNum'+rnum).remove();
}
