0

I am trying to update multiple rows on submit of a form (in particular this one is the "hours" field.

I have it working but only one of the value updates vs all of them.

There is the possibility of having different values for each update.

The form code:

$query2 = "select * FROM work_hours WHERE formid = $formid ";
$result = $mysqli->query( $query2 );

$num_results = $result->num_rows;
if( $num_results > 0){ 

echo " <table border='0' align='center'>
<tr>
<td colspan='2' align='center'>
<strong> Time Away Break Down</strong>
 </td>
 </tr>
<tr>
<td align='center'>Date</td>
<td align='left'>Hours</td>
</tr>";

while( $row = $result->fetch_assoc() ){

extract($row);

 echo " <tr>
 <td class='hidden_sm' align='center'>
  <input type='text' name='id' size='10' value='$id' class='dept' readonly   style='width:30px;'>
<input type='text' name='date' size='40' value='$date' class='dept'   readonly>              <input type='text' name='end_date' size='40' value='$end_date' class='dept'    readonly> 
</td>
 <td class='hidden_sm' align='left' >
  <input type='text' name='hours' size='10' style='width:30px;' value='$hours'     class='dept' >        
</td>
</tr>

";
}

echo "<tr>
<td colspan='2' align='center'>
<input type='submit' name='Submit' value='Submit Request'>
</td>
</tr>
</form>
</table>";//end table

Submit Code:

$id = $_POST['id'];
$formid = $_POST['formid'];
$hours = $_POST['hours'];
include 'connect-db.php'; 
$stmt = $mysqli->prepare("UPDATE work_hours SET hours = ? WHERE formid = ?");
$stmt->bind_param('si',
  $_POST['hours'],
  $_POST['formid']);
$stmt->execute(); 
 if ( $stmt ) {
echo "<p align='center'>Thank you, this request has been approved.<BR>You will be     redirected in 5 seconds</p>";
} else {
echo "Error, you status cannot be updated. <BR> Please contact your system     administrator.";
 }
$stmt->close();
?>

Could anyone point me in the right direction to have all values update on submit, as I have had zero luck.

As well I do understand the need to prevent SQL Injections, and that I am working, so no need to remind me.

Thanks in advance!

Robert
  • 21
  • 4
  • If you're updating hours WHERE formid equals something then it's going to update all the rows where formid is equal to some value. Is formid a unique value? – Dan Mar 03 '14 at 22:23
  • Actually, id is the unique value - I updated my query to $stmt = $mysqli->prepare("UPDATE work_hours SET hours = ? WHERE id = $id"); $stmt->bind_param('i', $_POST['hours']); But it only updates the last field – Robert Mar 03 '14 at 22:31
  • What is formid though? You're going to want to update the table based on a shared value of the rows...if they have one, that is. Otherwise you will have to update using a loop or a batch update. – Dan Mar 03 '14 at 22:34
  • The formid is unique to each form submission, this way I can query out all the values related to that form (in this case it would be all the requested days off) - here a png of what the form looks like [link](https://www.dropbox.com/s/p5ic5pmgpldk0yc/img1.PNG) I am guessing I will need to do it using a loop but I am use of how. – Robert Mar 03 '14 at 22:38
  • I guess it would be helpful if you posted the relevant part of your database to the question. It's difficult to say more without seeing that. – Dan Mar 03 '14 at 22:42
  • here you go: [link](https://www.dropbox.com/s/fmqdmpffuhtdbje/db.PNG) – Robert Mar 03 '14 at 22:47
  • And you know about sql injection, right? – Strawberry Mar 03 '14 at 23:27
  • I do, no need to worry there. – Robert Mar 03 '14 at 23:30

1 Answers1

0

Looks like you'll want to use a CASE statement as explained here: How does MySQL CASE work?

Use a loop to build the statement and you're better off using the id as the identifier instead of formid, since the id is the unique value and you could have different results in the form.

Community
  • 1
  • 1
Dan
  • 9,391
  • 5
  • 41
  • 73