PHP & MySQL update query only ever updates the las

问题:

I have a HTML form which I am using to update a MySQL table via a post script - however, I am having an issue where the update buttons which are on each row only ever work for the last row in the table.

<?php

$link = mysql_connect ('localhost', 'root', 'password');
mysql_select_db ('cardatabase');

$select = 'SELECT * from cars';
$result = mysql_query($select, $link);

if(isset($_POST['update'])){
    $updatequery = "UPDATE cars SET 
        ID='$_POST[id]',
        CARMAKE='$_POST[carmake]',
        CARMODEL='$_POST[carmodel]',
        FUELTYPE='$_POST[fueltype]',
        TRANSMISSION='$_POST[transmission]',
        DOORS='$_POST[doors]',
        AMOUNT='$_POST[amount]',
        AVAILABLE='$_POST[available]'
        WHERE ID='$_POST[hidden]'";

    mysql_query($updatequery, $link);
};

echo '<table><form action=update.php method=post>';
echo '<tr><td>ID</td><td>Make</td><td>Model</td><td>Fuel Type</td><td>Transmission</td><td>Engine Size</td><td>Doors</td><td>Amount</td><td>Available</td></tr>';



while($row = mysql_fetch_array($result)) {
    echo '<tr>';
    echo "<td><input type='text' name='id' value='{$row['ID']}'</td>";
    echo "<td><input type='text' name='carmake' value='{$row['CARMAKE']}'</td>";
    echo "<td><input type='text' name='carmodel' value='{$row['CARMODEL']}'</td>";
    echo "<td><input type='text' name='fueltype' value='{$row['FUELTYPE']}'</td>";
    echo "<td><input type='text' name='transmission' value='{$row['TRANSMISSION']}'</td>";
    echo "<td><input type='text' name='enginesize' value='{$row['ENGINESIZE']}'</td>";
    echo "<td><input type='text' name='doors' value='{$row['DOORS']}'</td>";
    echo "<td><input type='text' name='amount' value='{$row['AMOUNT']}'</td>";
    echo "<td><input type='text' name='available' value='{$row['AVAILABLE']}'</td>";
    echo "<td><input type='hidden' name='hidden' value='{$row['ID']}'</td>";
    echo '<td><input type="submit" name="update" value="Update"</td>';
    echo '</tr>';
}

echo '</form></table>';
mysql_close ($link);

cars table:

回答1:

Let's start with this for visibility:

For future users seeing your questions. If you know this, great! Welcome to Stack Overflow! Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

---

The problem you're experiencing is caused by giving all of your inputs the same names (id, carmake etc). This means only the last row would ever be submitted.

Give them names like so:

name="id[]"

That would make PHP treat the $_POST values as arrays, and you can access

$_POST["id"][3]

To get the fourth row (Starting from 0), for example.

回答2:

@MadaraUchiha has proved a solution for your updating problem but I would like to suggest an alternative for the way you format your code.

One of the features of php is being able to intermingle it with html. I've reformatted a chunk of your code. As you can see I have gotten rid of most of your echos. The nice thing about formatting your code this way is you don't have to worry about nesting quotes properly in your strings and if you are using a code editor with syntax highlighting you don't lose the highlighting on the html.

<table cellspacing="3" border="1" style="font-size:13px;background-color:white;">
    <form action="update.php" method="post">
        <tr style="background-color:#38C0CC;">
            <td>ID</td>
            <td>Make</td>
            <td>Model</td>
            <td>Fuel Type</td>
            <td>Transmission</td>
            <td>Engine Size</td>
            <td>Doors</td>
            <td>Amount</td>
            <td>Available</td>
        </tr>

        <?php while ( $row = mysql_fetch_array( $result ) ) { ?>
        <tr>
            <td><input type="text" name="id" value="<?php echo $row['ID']; ?>"/></td>
            <td><input type="text" name="carmake" value="<?php echo $row['CARMAKE]'; ?>"/></td>
            <td><input type="text" name="carmodel" value="<?php echo $row['CARMODEL']; ?>"/></td>
            <td><input type="text" name="fueltype" value="<?php echo $row['FUELTYPE']; ?>"/></td>
            <td><input type="text" name="transmission" value="<?php echo $row['TRANSMISSION']; ?>"/></td>
            <td><input type="text" name="enginesize" value="<?php echo $row['ENGINESIZE']; ?>"/></td>
            <td><input type="text" name="doors" value="<?php echo $row['DOORS']; ?>"/></td>
            <td><input type="text" name="amount" value="<?php echo $row['AMOUNT']; ?>"/></td>
            <td><input type="text" name="available" value="<?php echo $row['AVAILABLE']; ?>"/></td>
            <td><input type="hidden" name="hidden" value="<?php echo $row['ID']; ?>"/></td>
            <td><input type="submit" name="update" value="Update" /></td>
        </tr>
        <?php } ?>

    </form>
</table>
<?php mysql_close ($link); ?>

回答3:

Its because you are ovewriting $_POST[carmake], $_POST[carmodel], etc. since they all have the same form names.

You can fix this by appending [] to the end of the names of the forms, which then makes them an array.

You woul then access them by doing:

 $_POST[carmake][n]