Can't update a selected row by user

Question

I've written this code for a user to edit one row and update it in MySQL, but it always posts the last row no matter which row you have selected (there are 3 rows).

What's the problem?

<?php include("includes/db_connection.php"); ?>

<?php
global $connection;

$sid="s5";



/**select all salesman from store 5**/
$sql ="SELECT * FROM employees WHERE e_type='Salesperson' AND store_assigned='".$sid."';";


/**get the result and put into table, which can be edited by user**/
$result = mysql_query($sql);
echo "<form method='post' action='update_salesman.php'>";
echo "<table border='1'><tr><th>Employee ID</th><th>Name</th><th>Address</th><th>Email</th><th>Job Title</th><th>Store</th><th>Salary</th></tr>";
while ($row = mysql_fetch_assoc($result)) {

    echo "<tr><td><input type='text' name='eid' value='".$row['eid']."' readonly /></td>";
    echo "<td><input type='text' name='e_name' value='".$row['e_name']."' /></td>";
    echo "<td><input type='text' name='e_addr' value='".$row['e_addr']."' /></td>";
    echo "<td><input type='text' name='e_email' value='".$row['e_email']."' /></td>";
    echo "<td><input type='text' name='e_type' value='".$row['e_type']."' /></td>";
    echo "<td><input  type='text' name='store_assigned' value='".$row['store_assigned']."'/></td>";
    echo "<td><input type='text' name='e_salary' value='".$row['e_salary']."' /></td>";
    echo "<td><input type ='submit' value='update' /></td></tr>";

} 
echo "</table>";
echo "</form>";
print($sql);
?>

Get the posted data, and update it in MySQL database:

<?php include("includes/db_connection.php"); ?>

<?php
$eid = $_POST['eid'];
$ename = $_POST['e_name'];
$eaddr = $_POST['e_addr'];
$eemail = $_POST['e_email'];
$etype = $_POST['e_type'];
$estore = $_POST['store_assigned'];
$esalary = $_POST['e_salary'];

$sql = "UPDATE employees SET e_name='" . $ename . "', e_addr='" . $eaddr . "', e_email='" . $eemail . "', e_type='" . $etype . "', store_assigned='" . $estore . "', e_salary='" . $esalary . "' WHERE eid='" . $eid . "' ;";
$result = mysql_query($sql);
print("</br>" . $sql);

?>

The result is always this:

UPDATE employees SET e_name='Norah ', e_addr='111 Melwood,PA', e_email='anorahm@gmiil.com', e_type='Salesperson', store_assigned='s5', e_salary='4000.00' WHERE eid='e334' ;

You are naming the input boxes for each row exactly the same. On post, they will overwrite with just the last value. — Jonathan Kuhn, Nov 20 '14 at 19:39
possible duplicate of [php update script, only updates last record](http://stackoverflow.com/questions/13728744/php-update-script-only-updates-last-record) — andy, Nov 20 '14 at 19:44
As per Jonathan's comment, you should append `[]` to the field names to create an array. You could also do `[" . $row['eid'] . "]` so your array would be indexed by the eid. — scrowler, Nov 20 '14 at 19:48
So many bad practices here. **One:** The php mysql api is deprecated, use PDO or mysqli. **Two:** Research prepared statements, NEVER use user input directly in a query, someone could manipulate or destroy your database with an SQL injection. **Three:** globals... — Devon Bessemer, Nov 20 '14 at 19:48

score 1 · Answer 1 · answered Nov 20 '14 at 20:47

Your problem is twofold. First, when generating the HTML code, you use a while loop to echo the fields. Note that the names of these fields are the same every time the loop runs. (You can see this in the generated HTML (source code). Note that on submitting, one one of the multiple same-named fields will be posted.

Second, in the PHP form handler code, you read the post data and then do one update query, while you may want to update more than one field.

The easiest way to solve this is to make sure that the field names in the HTML form are different for each of the rows, and to use a loop structure when updating the sql table such that there's an update for each row.

score 0 · Accepted Answer · answered Nov 20 '14 at 19:56

even though it may appear fine on the html side, it's clear what's happening on the server side when it gets the form

When the server gets the form it will only see the last record because each record will overwrite the values that come before it resulting in only getting the data from the last record

What you can do is give each set of values its own form (Wouldn't suggest). But with this method, you can leave your code almost as is, just move the form tags into the while loop. OR write the input names as e_name[], etc.

This way it will be passed as an array to the server and you can loop through to get all your values

On the server end, to get the array you would do something like

$e_names = $_POST['e_name']; //Value will be an array

Can't update a selected row by user

2 Answers2