Update selected rows from form with checkbox - data from wrong rows added

Question

I have a form with many rows. I want to allow users to update a selection of rows by checking a checkbox in each of the rows they wish to to update.

The code works reliably for single line updates but when multiple lines are chosen the data updated is not from the relevant rows. I.e., the checkboxes work correctly in that only rows with a checked checkbox are updated but if there are multiple checked rows values are not from the correct row (but from some other row - checked or not - in the form).

Here are the relevant form elements:

<tr class='job-row recent<?php echo $editRecent; ?>'>
                      <td class="jobnr-cell table-tooltip"><input type="checkbox" value="<?php echo $row['jobnr']; ?>" name="editCheck[]"/><input id='hiddenCheck' type='hidden' value='0' name='editCheck[]'> <a href='editOpg.php?jobnr=<?php echo $row['jobnr']; ?>&month=<?php echo $monthno; ?>&plx=<?php echo urlencode($plx); ?>'><?php echo $row['jobnr']; ?></a><span class="table-tooltiptext"><?php echo $projekt; ?></span></td>
                      <input type="hidden" name="jobnr[]" value="<?php echo $row['jobnr']; ?>"> 
                      <td class="job-cell name-cell"><?php echo $knshort; ?></td>
                      <!-- <td><?php echo $projekt; ?></td> -->
                      <td class="job-cell name-cell"><?php echo $kundeansvarlig; ?></td>
                      <!-- <td class="job-cell name-cell"><a href='editJobs.php?plx=<?php echo urlencode($row['projektleder']); ?>'><?php echo $projektleder; ?></a></td> -->
                      <td class="job-cell"><div class="UI_cell"><input type="text" class="UI_input" name="tilbudspris" id="tilbudspris<?php echo $row['jobnr']; ?>" value="<?php echo $row['tilbudspris']; ?>" readonly> kr.</div></td>
                      <input type="hidden" class="UI_input" name="via0" id="via0<?php echo $row['jobnr']; ?>" value="<?php echo $row['via0']; ?>" readonly>
                      <td class="job-cell via-tooltip UI_cell">
                      <input type="text" class="UI_input border-top" name="bookedMonth" id="bookedMonth<?php echo $row['jobnr']; ?>" value="<?php echo $bookedMonth; ?>" readonly> kr.
                      <span class="via-tooltiptext"><?php echo $row['viadd']; ?> kr. - <?php echo $row['viadd']; ?> kr.</span></td>
                      <td class="job-cell UI_edit"><div class="UI_cell"><input type="text" class="UI_input UI_edit" name="forbrug[]" id="forbrug<?php echo $row['jobnr']; ?>" value="<?php echo $row['UI_forbrug']; ?>"> kr.</div></td>
                      <td class="job-cell UI_edit"><div class="UI_cell"><input type="text" class="UI_input UI_edit" name="rekvisitioner[]" id="rekvisitioner<?php echo $row['jobnr']; ?>" value="<?php echo $row['UI_rekvisitioner']; ?>"> kr.</div></td>
                      <td class="job-cell via-tooltip UI_cell"><input type="text" class="UI_input" name="forvEXreg" id="forvEXreg<?php echo $row['jobnr']; ?>" value="<?php echo $forvEXreg; ?>" readonly> kr.
                      <span class="forv-tooltiptext"><input type="text" class="UI_input" name="restValTilbud" id="restValTilbud<?php echo $row['jobnr']; ?>" value="<?php echo $restValTilbud; ?>" readonly> kr.</span></td>
                      <td class="job-cell"><div class="UI_cell"><?php echo $row['reguleringer']; ?> kr.</div></td>
                      <td class="job-cell UI_edit"><div class="UI_cell"><input type="text" class="UI_input UI_edit" name="reguleringer[]" id="reguleringer<?php echo $row['jobnr']; ?>" value="<?php echo $row['UI_reguleringer']; ?>"> kr.</div></td>
                      <td class="job-cell"><div class="UI_cell"><input type="text" class="UI_input" name="forvINCreg" id="forvINCreg<?php echo $row['jobnr']; ?>" value="<?php echo $forvINCreg; ?>" readonly> kr.</div></td>
                      <td class="job-cell"><div class="UI_cell"><input type="text" class="UI_input" name="forecast" id="forecast<?php echo $row['jobnr']; ?>" value="<?php echo $forecast; ?>" readonly> kr.</div></td>
                      <td class="job-cell"><div class="UI_cell"><input type="text" class="UI_input" name="restValNextMonths" id="restValNextMonths<?php echo $row['jobnr']; ?>" value="<?php echo $restValNextMonths; ?>" readonly> kr.</div></td>
                      <td class="job-cell UI_edit"><div class="UI_cell"><input type="text" class="UI_input UI_edit" name="forecast2[]" id="forecast2<?php echo $row['jobnr']; ?>" value="<?php echo $row['UI_forecast2']; ?>"> kr.</div></td>
                    </tr>

And here is how I process it:

$monthno = $_GET['month']; 
$weekno = $_GET['week']; 
$yearno = $_GET['year']; 
$editCheck = $_POST['editCheck'];
$jobnr = $_POST['jobnr'];
$forbrug = $_POST['forbrug'];
$rekvisitioner = $_POST['rekvisitioner'];
$reguleringer = $_POST['reguleringer'];
$forecast2 = $_POST['forecast2'];

 $chkcount = count($jobnr);
 for($i=0; $i<$chkcount; $i++)
 {
 $con->query("UPDATE jobs SET UI_forbrug='$forbrug[$i]', UI_rekvisitioner='$rekvisitioner[$i]', UI_reguleringer='$reguleringer[$i]', UI_forecast2='$forecast2[$i]', edate = NOW() WHERE monthno = $monthno AND jobnr=".$editCheck[$i]);
 }

Hello. First of all, please learn about SQL injection and hwo to prevent them. Your code is really vulnerable and not safe at all! Your database could be hacked in a few seconds without any need of deeper knowledge. Visit http://bobby-tables.com to learn more about SQL injection and how to prevent them with prepared statements. — Twinfriends, Aug 15 '17 at 09:21

Rajdeep Paul · Accepted Answer · 2017-08-15T10:56:05.670

0

There's no point having hidden inputs, just checkbox input elements would be fine. Here the problem is, you're not associating input text rows with the corresponding checkbox inputs, that's why the correct rows(corresponding to the checked checkboxes) are not getting updated properly.

Change the name attribute of your text input elements in the following way,

name="forbrug[]" to name="forbrug[<?php echo $row['jobnr']; ?>]"
name="rekvisitioner[]" to name="rekvisitioner[<?php echo $row['jobnr']; ?>]"
name="reguleringer[]" to name="reguleringer[<?php echo $row['jobnr']; ?>]" and
name="forecast2[]" to name="forecast2[<?php echo $row['jobnr']; ?>]"

And after form submission, update the checked rows in the following way,

// your code
$chkcount = count($editCheck);
for($i=0; $i < $chkcount; $i++){    
    $con->query("UPDATE jobs SET UI_forbrug='".$forbrug[$editCheck[$i]]."', UI_rekvisitioner='".$rekvisitioner[$editCheck[$i]]."', UI_reguleringer='".$reguleringer[$editCheck[$i]]."', UI_forecast2='".$forecast2[$editCheck[$i]]."', edate = NOW() WHERE monthno = $monthno AND jobnr = ".$editCheck[$i]);
}

For your reference, this is the updated query:

"UPDATE jobs 
SET UI_forbrug='".$forbrug[$editCheck[$i]]."',
UI_rekvisitioner='".$rekvisitioner[$editCheck[$i]]."', 
UI_reguleringer='".$reguleringer[$editCheck[$i]]."', 
UI_forecast2='".$forecast2[$editCheck[$i]]."', 
edate = NOW() 
WHERE monthno = $monthno AND jobnr = ".$editCheck[$i]

Sidenote: Learn about prepared statement because right now your query is susceptible to SQL injection attack. Also see how you can prevent SQL injection in PHP.

edited Aug 15 '17 at 10:56

answered Aug 15 '17 at 09:28

Rajdeep Paul

16,887
3
18
37

Thanks Rajdeep. But this had the effect of updating with the same values in all rows that I chose - values from a row outside the selection. – Mads Stenbjerre Aug 15 '17 at 09:36
@MadsStenbjerre Quite a few unknowns here. Is the entire ` ... ` inside any loop? What's the purpose of this *hidden* input element, `` ? Which columns you see the same value when you update? – Rajdeep Paul Aug 15 '17 at 09:45
Yes, entire inside loop: while($row = $query->fetch_assoc()){ ... } . Purpose of hidden is to ensure that a editCheck element is posted (but I had removed it when I tried your code). The columns that are updated are the expected/correct ones and only for the selected rows. – Mads Stenbjerre Aug 15 '17 at 09:52
@MadsStenbjerre If you have removed that *hidden* input element, then you must have removed the `for` loop as well. Also, since you said, *The columns that are updated are the expected/correct ones and only for the selected rows.*, that is what expected out of it, update only those rows whose corresponding checkboxes are checked. What exactly are you looking for here? – Rajdeep Paul Aug 15 '17 at 09:59
Nope. Did not remove loop. Only hidden editCheck (which was inserted to add value when checkbox not checked and may or may not be necessary). The columns that are updated are updated with the wrong values. I.e., not the values from the corresponding rows in the form but from seemingly random other rows. So, changes take place in the right columns and for the right rows BUT the values that are inserted are from other - wrong - rows. – Mads Stenbjerre Aug 15 '17 at 10:11
@MadsStenbjerre I've completely updated my answer. Hopefully this will resolve your issue. – Rajdeep Paul Aug 15 '17 at 10:56
Thank you, Rajdeep. I will try this later today (just been called away). Thanks for your patience. – Mads Stenbjerre Aug 15 '17 at 11:18
@MadsStenbjerre You're welcome! Glad I could help. :-) – Rajdeep Paul Aug 15 '17 at 13:10

Update selected rows from form with checkbox - data from wrong rows added

1 Answers1