PHP MySQL update only updating one row

Question

I have set up an update query which will update values entered into text fields on a while loop. This works fine until multiple data is being looped from the database. Then for some reason only the last data in the loop will be updated and the rest will stay the same.

    <form method="post" action="update.php">
    <?php
    $id = $_POST["id"];
    $fname = $_POST["fname"];
    $lname = $_POST["lname"];

   $query= "SELECT * FROM list ORDER BY id ASC" ;
   $result= mysql_query($query);
   while($row = mysql_fetch_assoc($result) ){
   echo"<input type=\"hidden\" name=\"id\" value=" . $row['id'] . " />";
   echo"<input type=\"text\" name=\"fname\" value=" . $row['fname'] . " />";
   echo"<input type=\"text\" name=\"lname\" value=" . $row['lname'] . " />";
 }
?>
  <input type="submit" value="Save Changes" />

 <?php
 $sql = "UPDATE list SET fname = '{$fname}', lname = '{$lname}' WHERE id = {$id}";

  $result = mysql_query( $sql );
 ?>
</form>

Your update isn't in a loop so why should it update more than once? — j08691
– j08691, Commented Mar 27, 2012 at 23:11

F. Müller · Accepted Answer · 2012-03-27 23:14:48Z

2

$_POST is an array to. And $_POST get its elements by the name of the input fields in the form used. So you are always overwriting the entries. If you want to have multiple updates you have to write a loop (see comments). And use this code below for the input fields.

Try:

   echo"<input type=\"hidden\" name=\"id[]\" value=" . $row['id'] . " />";
   echo"<input type=\"text\" name=\"fname[]\" value=" . $row['fname'] . " />";
   echo"<input type=\"text\" name=\"lname[]\" value=" . $row['lname'] . " />";

Hope that helps.

answered Mar 27, 2012 at 23:14

F. Müller

4,0629 gold badges44 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Max Hudson · Accepted Answer · 2012-03-27 23:26:05Z

This is because all of your inputs have the same name if there are more than one, so php can't differentiate from one to another.

If there is more than 1 row generated from your select mysql query, then you need to give each id input a different name, each fname input a different name, and each lname input a different name.

<form method="post" action="update.php">
    <?php
    $id = $_POST["id"];
    $fname = $_POST["fname"];
    $lname = $_POST["lname"];

    $query= "SELECT * FROM list ORDER BY id ASC" ;
    $result= mysql_query($query);
    while($row = mysql_fetch_assoc($result) ){
        echo"<input type=\"hidden\" name=\"id[]\" value=" . $row['id'] . " />";
        echo"<input type=\"text\" name=\"fname[]\" value=" . $row['fname'] . " />";
        echo"<input type=\"text\" name=\"lname[]\" value=" . $row['lname'] . " />";
    }
    ?>
    <input type="submit" value="Save Changes" />

    <?php
    $sql = "UPDATE list SET fname = '{$fname}', lname = '{$lname}' WHERE id = {$id}";

    $result = mysql_query( $sql );
    ?>
</form>

Neil Alexander · Accepted Answer · 2012-03-27 23:15:56Z

1

If id is unique, then the WHERE id=foo clause will restrict the updates to a single field. But more related to your PHP: You are actually setting $id, $fname and $lname before the loop, so that UPDATE line is using "old values" if the loop runs more than once. Similarly, you haven't actually put the UPDATE query into the loop, so at most, the variables will be set once, the loop will run a few times (possibly) and then the query is only run once.

The code is run in-order; you have to be more specific with putting the code where you want it to run.

answered Mar 27, 2012 at 23:15

Neil Alexander

1105 bronze badges

Comments

Lee Davis · Accepted Answer · 2012-03-27 23:17:47Z

1

Your updating the list table outside of your while loop. Which will contain the values that where last selected (in line 7).

As your echo'ing out multiple input fields you may want to declare these as array elements.

echo"<input type=\"text\" name=\"lname[]\" value=" . $row['lname'] . " />";

And iterate over each one upon $_POST.

answered Mar 27, 2012 at 23:17

Lee Davis

4,7763 gold badges31 silver badges40 bronze badges

Comments

Nick · Accepted Answer · 2012-03-27 23:24:38Z

1

Sounds like you want to display the contents of a table ,and let the user edit it.

There are some really nice tools out there that will do that for you, like jqGrid.

http://www.trirand.net/demophp.aspx Click on the link about editing on the left hand side of the jqGrid page

The component will take care of all the display and editing. It even shows you how to implement the PHP on the back end.

answered Mar 27, 2012 at 23:24

Nick

3,1763 gold badges23 silver badges25 bronze badges

Comments

derabbink · Accepted Answer · 2012-03-27 23:29:11Z

I think you are misinterpreting the works of the loop. Indeed, you are outputting multiple rows from your table as groups of input fields, but your only updating one row after submitting. If you wanted to update multiple rows, you'd need multiple ids (namely one for each row you'd want to update)

Your browser apparently choses to http-post submit the last instances of your id, fname and lname input fields that are in the html form, and those are the ones that your $_POST fields will contain when receiving that http post request.

It is generally a bad idea to have multiple inputs with the same name in one form, which is what happens here if more than one row from the table is selected.

You might want to consider:

Having one form (including submit button) per row. That way your browser knows on a per-form-basis what uniquely identifiable input field values it should submit.
If you do want a multi-row edit form then there are many tricks to achieve this. The simplest would probably be a naming convention like fname_42 for the 42nd fname input field, and one hidden field in the form containing the row count that your form shows. When processing the submit request, you'll be able to reconstruct the inputs' names by counting up to the total number of rows.

example:

   for ($i=0;$i<$_POST["row_count"];$i++) {
     $fname = $_POST["fname_$i"];
     //use $fname here in an update query
   }

PS: please don't blame me if this example is not entirely syntactically correct ;) I don't have a PHP interpreter with me right now.

Guns · Accepted Answer · 2012-11-22 23:11:44Z

Sorry for bringing the old post up, but I had the same problem and just found the fix. The problem occurs because the form properties and submit properties are not included as php code but kept as html outside of loop. When the submit is inside the while statement, each row will have its own submit and change will effect only that row. The working code is below;

    if(isset($_POST['update'])){    
    $sql = "UPDATE Persons SET fname = '$_POST[fname]', lname = '$_POST[lname]' WHERE id = '$_POST[id]'";
mysql_query($sql);
};

    $query= "SELECT * FROM Persons ORDER BY id ASC" ;
    $result= mysql_query($query);
    while($record = mysql_fetch_assoc($result))
    {
echo"<form method=\"post\" action=\"update.php\">";
    echo"<input type=\"hidden\" name=\"id\" value=" . $record['id'] . " />";
    echo"<input type=\"text\" name=\"fname\" value=" . $record['fname'] . " />";
    echo"<input type=\"text\" name=\"lname\" value=" . $record['lname'] . " />";
    echo"<input type=\"submit\" name=\"update\" value=\"Save Changes\" />";
    echo"</form>";
}

Collectives™ on Stack Overflow

PHP MySQL update only updating one row

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related