I've been tasked to fix a problem on our website and I'm not very well versed in SQL/PHP but we have a system that uses a front page datatable, displaying records from our database. When you click a serial number link in that table, it brings up a web based form/tables filled by the data from the specified record. I've now been told to make two of the tables editable so that it still shows the current fields from the database, but this way we would have the option to change them, hit the submit button and save the new values to the database.
The code below covers the instance from the front page and the query that selects the right row from our 'staging' database table and displays the info from only that record. This works fine and since I upded the code with input tags I can now edit the fields, but I've yet to implement the option to save them.
If I understand right, I would create a 'Submit' button on this page (although I was thinking of an option that may have a save button for every html table) and when the button is pressed, I would attach a SQL UPDATE statement.
I have two questions here:
If I were to edit every one of the input fields in the below code, what is the proper way to run that UPDATE statement to properly set each of those values?
If you see below, I have a formula that averages some values and I put that result in
$testFinalForm. This is all done on the web page and that value doesn't exist in the database table. If I were to add testFinalForm to my database table, I would like to save that value as well.
Again, this is not something I usually have to do and I'm a bit overwhelmed so I'm just hoping for some helpful guidance.
if(isset($_GET['id']))
{
$query1 = "SELECT * FROM staging WHERE serialNumber = ".$_REQUEST['id'].";";
$result1 = mysqli_query($connect,$query1);
while($row = mysqli_fetch_array($result1)){
?>
<form>
<!--Meter Test (1) Table-->
<div class="FirstMeterTable" style=" width:100%; clear: both;">
<table style="width:100%; border:none;
border-collapse:collapse;">
<tr style="border:none;">
<th style="border:none; text-align: left;" >Meter Test</th>
<th style="border:none;">Test 1</th>
<th style="border:none;">Test 2</th>
<th style="border:none;">Test 3</th>
<th style="border:none;">Test 4</th>
<th style="border:none;">Test 5</th>
<th style="border:none;">Test 6</th>
<th style="border:none;">Test 7</th>
<th style="border:none;">Test 8</th>
</tr>
<tr style="border: none;" >
<td style="border:none; text-align: left;">Test Rate GPM: </td>
<td><input type="text" value="<? echo $row['test1TestRateGPM'];?>"> </td>
<td><input type="text" value="<? echo $row['test2TestRateGPM'];?>"> </td>
<td><input type="text" value="<? echo $row['test3TestRateGPM'];?>"> </td>
<td><input type="text" value="<? echo $row['test4TestRateGPM'];?>"> </td>
<td><input type="text" value="<? echo $row['test5TestRateGPM'];?>"> </td>
<td><input type="text" value="<? echo $row['test6TestRateGPM'];?>"> </td>
<td><input type="text" value="<? echo $row['test7TestRateGPM'];?>"> </td>
<td><input type="text" value="<? echo $row['test8TestRateGPM'];?>"> </td>
</tr>
<tr>
<td style="border:none; text-align: left;">Meter Volume: </td>
<td><input type="text" value="<? echo $row['test1MeterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test2MeterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test3MeterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test4MeterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test5MeterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test6MeterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test7MeterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test8MeterVol'];?>"> </td>
</tr>
<tr>
<td style="border:none; text-align: left;">Tester Volume: </td>
<td><input type="text" value="<? echo $row['test1TesterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test2TesterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test3TesterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test4TesterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test5TesterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test6TesterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test7TesterVol'];?>"> </td>
<td><input type="text" value="<? echo $row['test8TesterVol'];?>"> </td>
</tr>
<tr>
<td style="border:none; text-align: left;">Tester Accuracy: </td>
<td><input type="text" value="<? echo $row['test1Accuracy'];?>">% </td>
<td><input type="text" value="<? echo $row['test2Accuracy'];?>">% </td>
<td><input type="text" value="<? echo $row['test3Accuracy'];?>">% </td>
<td><input type="text" value="<? echo $row['test4Accuracy'];?>">% </td>
<td><input type="text" value="<? echo $row['test5Accuracy'];?>">% </td>
<td><input type="text" value="<? echo $row['test6Accuracy'];?>">% </td>
<td><input type="text" value="<? echo $row['test7Accuracy'];?>">% </td>
<td><input type="text" value="<? echo $row['test8Accuracy'];?>">% </td>
<td style="border:none;">Overall</td>
</tr>
<tr>
<td style="border:none; text-align: left;">Corrected Accuracy: </td>
<?php
$sum=0;
for($i=1; $i<=8; $i++){
if($row["test".$i."TesterVol"] == 0){
$row["test".$i."TesterVol"] = 0.001;
}
$testFormA = $row["test".$i."MeterVol"] / $row["test".$i."TesterVol"];
$testFormB = $testFormA * $row["test".$i."Accuracy"];
$testFinalForm = $testFormB;
$sum += $testFinalForm;
?>
<td><?php echo round($testFinalForm,3) ?>%</td>
<?php }
$average = $sum / 7 ;
?>
<td><?php echo round($average,5)?>% </td>
</tr>
</table>
</div>
<br>
<br>
</form>
