ANSWERED: Form is updating database correctly

Question

This code now works to update each row of data individually if submit button is clicked.

Original issue was that I could not get each record updated individually and it was updating ALL rows instead of just the one matching the ID I wanted.

CONNECTIONS STUFF

 <form method='post'>";

 $query="SELECT * FROM table WHERE approved='no'";
 $result = mysql_query($query) or die(mysql_error());

$count = mysql_num_rows($result);
echo "<p>$count pending approval.</p>";

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { 

$id=$row['id'];
$name = $row['name'];
$extra = $row['extra'];

echo "
<table>
 <tr> 
  <td>ID:</td>
  <td>$id <input type='hidden' name='id[]' value='$id'></td>
 </tr>
 <tr> 
  <td>Name:</td>
  <td>$name <input type='hidden' name='name[]' value='$name'></td>
 </tr>
 <tr> 
  <td>Extra:</td>
  <td>$extra <input type='hidden' name='extra[]' value='$extra'></td>
 </tr>
 <tr colspan='2'>
  <td>
   <center><input name='submit' type='submit' value='Approve'></form></center>
  </td>
 </tr>
</table><br>
";}

if($_POST['submit']) {
$update = "UPDATE table SET approved='yes' WHERE id='$id' LIMIT 1";
if(mysql_query($update)) $count++; 
else die("Error in query:<br>$sql<br>"); 

echo "<p><b>$name has been approved</b></p>";
} 
?>

@ld04 please mark an answer as accepted if you consider your question answered ( as implied by the title edit ) — RyanS, Jan 31 '14 at 17:12
Possible duplicate of [Update query PHP MySQL](https://stackoverflow.com/questions/8893551/update-query-php-mysql) — mickmackusa, May 05 '18 at 11:27

score 0 · Answer 1 · answered Jan 29 '14 at 18:58

0

You have to move your update statement outside the while (($i < $num)) {...}. Currently, that's inside the loop...

answered Jan 29 '14 at 18:58

Harryman

62
3

score 0 · Answer 2 · answered Jan 29 '14 at 18:58

You are looping over each row, and then checking if the submit button was clicked, and if so updating the row.

The issue is that you dont identify which button was clicked and so each row is updated when any button is pressed. Try this:

if (isset($_POST['accepted']) && isset($_POST['id']) && $_POST['id'] == $id)

This will check to see if the submited form corresponds to the current row

score 0 · Answer 3 · answered Jan 29 '14 at 18:59

The fault is in here:

...
<?php
if (isset($_POST['accepted'])) {
    $query_update = "UPDATE mytable SET accepted='yes' WHERE id ='$id'";
    $result_update=mysql_query($query_update);}
    $i++;
}
mysql_close();

?>
....

$i is the run vairable to iterate over ALL rows. it only gets incremented when $_POST['accepted'] is set. And in this particular case, it's generateing an update for each and evry single row with an $id which has come from the databse instead of the current POST.

Thus: all records will be updated.

Modfify:

...
<?php
if (isset($_POST['accepted']) && isset($_POST['id']) ) {
    $updateId = $_POST['id'];
    $query_update = "UPDATE mytable SET accepted='yes' WHERE id ='$updateId '";
    $result_update=mysql_query($query_update);
     mysql_close();
}

$i++;
?>
....

I THINK.. That you misread this, there is a closing braket on the same line as the update query, this should close the if statement. Then the i++ is in the loop, and the next bracket closes the while loop — RyanS, Jan 29 '14 at 19:51

ANSWERED: Form is updating database correctly

3 Answers3