PHP MySQL update only updates one row
I have set up an update query that will update the values entered in the text boxes in a while loop. This works great as long as there are not multiple data loops in the database. Then, for some reason, only the last data in the loop is updated, and the rest will remain unchanged.
<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>
source to share
This is because all of your inputs have the same name if there is more than one, so php cannot differentiate from each other.
If your mysql query has more than one row, you need to specify that each id enters a different name, each fname has a different name, and each name contains 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>
source to share
$_POST
is an array. And $_POST
they get their elements by the name of the input fields in the used form. This way you always overwrite records. If you want to have multiple updates, you need to write a loop (see comments). And use this code below for 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 it helps.
source to share
If id
unique, the proposal WHERE id=foo
restricts updates to one field. But more to do with your PHP: you actually set $id
, $fname
and $lname
before the loop, so that the line UPDATE
uses the "old values" if the loop is executed more than once. Likewise, you haven't put the query UPDATE
in a loop, so at most, the variables will be set once, the loop will run multiple times (possibly), and then the query is only executed once.
The code runs fine; you should be more specific with the placement of the code where you want to run it.
source to share
Updating the list table outside of the while loop. Which will contain the values that were last selected (on line 7).
As your echo from multiple input fields, you might want to declare them as array elements.
echo"<input type=\"text\" name=\"lname[]\" value=" . $row['lname'] . " />";
And iterate over each one at $ _POST.
source to share
It sounds like you want to display the contents of the table and let the user edit it.
There are some really good tools out there that will do this for you, like jqGrid.
http://www.trirand.net/demophp.aspx Click the edit link on the left side of the jqGrid page
The component will take care of all display and editing. It even shows you how to implement PHP in the back.
source to share
I think you are misinterpreting how the loop works. Indeed, you are outputting multiple rows from your table as groups of input fields, but only one update after submit. If you want to update multiple rows, you will need multiple ids (namely one for each row you want to update).
Your browser seems to prefer http-post to send the latest instances of the id, fname and lname input fields that are in the html form, and the ones that will contain your $ _POST fields when it receives that http post request.
It is generally a bad idea to have multiple inputs with the same name in the same form, which is what happens here if multiple rows are selected from the table.
You might want to consider:
-
Having one form (including a submit button) per line. This way, your browser knows, based on each form, what uniquely identifiable input field values it should provide.
-
If you want a multi-line right-handed form, there are many tricks for that. The simplest would probably be a naming convention such as fname_42 for the 42nd fname input field, and one hidden field on the form containing the number of lines your form displays. When processing a post request, you will be able to recover the input names by counting 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 completely syntactically correct;) I don't have a PHP interpreter with me right now.
source to share
Sorry for putting up the old post, but I had the same problem and found a fix. The problem arises because the form properties and submit properties are not included in the php code, but are saved as html outside of the loop. When submit is inside a while statement, each line will have its own submit and the change will only affect that line. 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>";
}
source to share