I've got a form in which the user can make some changes to a grading scale. They also can select to apply those changes to 1 or more records (class section numbers). If they have selected a non-existing section number, I need an INSERT statement and if it is already present, then an UPDATE statement.
How can I fix this so that after submitting the form the records for each class section number are the same, all except for the section field? All of the relevant information is below:
This is what my database table correctly looks like before I submit my form: http://sqlfiddle.com/#!9/13b489/1
This is how it SHOULD look after I submit my form: http://sqlfiddle.com/#!9/c31955/1
This is the messed up version after I submit my form: http://sqlfiddle.com/#!9/ebaff3/1
html form:
https://jsfiddle.net/bigtime/qorphh9x/
<form action="admin_grading_scale2.php" method="post">
<p>
<label><input type="checkbox" name="SectionNumber[]" value="5011" id="SectionNumber_0">5011</label>
<br>
<label><input type="checkbox" name="SectionNumber[]" value="5013" id="SectionNumber_1">5013</label>
<br>
</p>
<div class="form-inline">
<label for="input">Total Points</label>
<input type="text" class="form-control mx-sm-3" id="PointsPossible" name="PointsPossible" aria-describedby="points" placeholder="Total Points" value="<?php echo "$PointsPossible"; ?>">
<table class="table table-striped">
<thead>
<tr class="table-text-center">
<th scope="col"> Letter Grade</th>
<th scope="col">Percent Toward Grade</th>
<th scope="col">Avg Steps/Day</th>
<th scope="col">Average Active Minutes/Week</th>
</tr>
</thead>
<tbody>
<?php
while ($row = $result->fetch_assoc()) {
$id = $row['id'];
$letter = $row['letter'];
$AssignmentID = $row['AssignmentID'];
$percent = $row['percent'];
$avgsteps = $row['avgsteps'];
$avgweeklymin = $row['avgweeklymin'];
$section = $row['section'];
?>
<input name="id[]" type="hidden" value="<?php echo "$id"; ?>"/>
<input name="section[]" type="hidden" value="<?php echo "$section"; ?>"/>
<input name="AssignmentID" type="hidden" value="<?php echo "$AssignmentID"; ?>"/>
<tr class="table-text-center">
<td><div class="col-sm-10">
<input type="text" class="form-control" id="letter" name="letter[]" aria-describedby="letter" placeholder="Grade" value="<?php echo "$letter"; ?>"> </div></td>
<td><div class="col-sm-10">
<input type="text" class="form-control" id="percent" name="percent[]" aria-describedby="percent" placeholder="Percent" value="<?php echo "$percent"; ?>"> </div></td>
<td><div class="col-sm-10">
<input type="text" class="form-control" id="avgsteps" name="avgsteps[]" aria-describedby="points" placeholder="Average Steps" value="<?php echo "$avgsteps"; ?>"> </div></td>
<td><div class="col-sm-10">
<input type="text" class="form-control" id="avgweeklymin" name="avgweeklymin[]" aria-describedby="avgweeklymin" placeholder="Average Weekly Activity Minutes" value="<?php echo "$avgweeklymin"; ?>"> </div></td>
</tr>
<?php } ?>
</tbody>
</table>
<input class="btn btn-lg btn-primary btn-block" type="submit" value="Save Changes">
</form>
PHP code:
<?php
$size = count($_POST['id']);
$stmt = $connection->prepare("INSERT INTO GradingScale
SET letter=?,percent=?,avgsteps=?,avgweeklymin=?,section=?,AssignmentID=?
ON DUPLICATE KEY UPDATE letter=?,percent=?,avgsteps=?,avgweeklymin=?,section=?,AssignmentID=?");
$i = 0;
while ($i < $size) {
// define each variable
$id = filter_var($_POST['id'][$i], FILTER_SANITIZE_NUMBER_INT);
$letter = filter_var($_POST['letter'][$i], FILTER_SANITIZE_STRING);
$percent = filter_var($_POST['percent'][$i], FILTER_SANITIZE_NUMBER_INT);
$avgsteps = filter_var($_POST['avgsteps'][$i], FILTER_SANITIZE_NUMBER_INT);
$avgweeklymin = filter_var($_POST['avgweeklymin'][$i], FILTER_SANITIZE_NUMBER_INT);
$AssignmentID = filter_var($_POST['AssignmentID'][$i], FILTER_SANITIZE_NUMBER_INT);
$SectionNumber = filter_var($_POST['SectionNumber'][$i], FILTER_SANITIZE_STRING);
$stmt->bind_param("siiisisiiisi", $letter, $percent, $avgsteps, $avgweeklymin, $SectionNumber, $AssignmentID, $letter, $percent, $avgsteps, $avgweeklymin, $SectionNumber, $AssignmentID);
$stmt->execute();
++$i;
}
database schema:
--
-- Table structure for table `GradingScale`
--
CREATE TABLE `GradingScale` (
`id` int(11) NOT NULL,
`letter` enum('A','B','C','D','F') NOT NULL,
`percent` smallint(4) NOT NULL,
`avgsteps` smallint(6) NOT NULL,
`avgweeklymin` smallint(4) NOT NULL,
`section` varchar(8) NOT NULL,
`AssignmentID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Grading Scale';
--
-- Dumping data for table `GradingScale`
--
INSERT INTO `GradingScale` (`id`, `letter`, `percent`, `avgsteps`, `avgweeklymin`, `section`, `AssignmentID`) VALUES
(1, 'A', 100, 10000, 100, '5011', 1),
(2, 'A', 90, 9500, 90, '5011', 1),
(3, 'B', 80, 9000, 80, '5011', 1),
(4, 'C', 70, 8500, 70, '5011', 1),
(5, 'D', 60, 8000, 60, '5011', 1),
(6, 'F', 50, 7500, 50, '5011', 1),
(7, 'F', 40, 7000, 40, '5011', 1),
(8, 'F', 30, 6500, 30, '5011', 1),
(9, 'F', 20, 6000, 20, '5011', 1),
(10, 'F', 10, 5500, 10, '5011', 1),
(11, 'F', 0, 5000, 0, '5011', 1);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `GradingScale`
--
ALTER TABLE `GradingScale`
ADD PRIMARY KEY (`id`) USING BTREE,
ADD UNIQUE KEY `section` (`section`,`percent`);