Trying to create an editable HTML table using PHP and mySQL but the table won't update

Question

I'm trying to make a HTML table as a frontend to a mySQL database. The table displays fine and I can type in the edits I want to make to each row of the table but when I press the submit button the changes aren't actually made. Can anyone see where I'm going wrong?

<?php 

include("db.php");

$sql = "SELECT * FROM `artist`";
$result = mysqli_query($conn, $sql);

if (isset($_POST['update'])){

    $artID = $_POST['artID'];
    $artName = $_POST['artName'];
    $key = $_POST['hidden'];

    $UpdateQuery = "UPDATE `artist` SET `artID` = '$artID', `artName` = '$artName' WHERE `artist`.`artID` = '$key'"; 

    mysqli_query($conn,$UpdateQuery);
    header("Location: {$_SERVER['HTTP_REFERER']}");
    exit;

};
echo "<table border='1'>";
echo "<tr>";
echo "<th>ID</th>";
echo "<th>Name</th>";
echo "</tr>";

if ($result->num_rows > 0) {
    echo "<form id ='artisttable' action ='getartiststable.php' method ='post'>";
    // output data of each row
    while($row = mysqli_fetch_array($result)) {
        echo "<tr>";
        echo "<td>" ."<input type='text' name ='artID' value ='" . $row['artID'] . "' </td>";
        echo "<td>" . "<input type='text' name ='artName' value ='" . $row["artName"] . "' </td>";
        echo "<td>" . "<input type = 'hidden' name ='hidden' value='" . $row['artID'] . "' </td>";
        echo "<td>" . "<input type='submit' name ='update'" . " </td>";
        echo "</tr>";
    }
    echo "</form>";
    echo "</table>";
} else {
    echo "0 results";
}
$conn->close(); 

?>

The db.php file simply includes the connection info to the mySQL database and I'm 100% sure there's nothing wrong with it as it retrieves the table correctly it just doesn't update.

Having a `form` tag around `tr` is __invalid__ Browser rebuilds your html and you don't get what you expect. — u_mulder, Apr 27 '16 at 18:40
It might be due to your input tags, they aren't closed.. You can try atleast to make sure. — Aparna, Apr 27 '16 at 18:44
Your code is vulnerable to SQL injection. Please read [How can I prevent SQL-injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) for information on how to fix it. — Matt Raines, Apr 27 '16 at 19:36

chris85 · Answer 1 · 2016-04-27T18:59:55.417

Your form is being constructed with multiple elements with the same name. When you submit the form it is using the last elements as the values so regardless of the record you want updated the last record is being updated (or throwing an error because of string encapsulation). You should use parameterized queries as well.

So instead of:

if ($result->num_rows > 0) {
    echo "<form id ='artisttable' action ='getartiststable.php' method ='post'>";
    // output data of each row
    while($row = mysqli_fetch_array($result)) {
        echo "<tr>";
        echo "<td>" ."<input type='text' name ='artID' value ='" . $row['artID'] . "' </td>";
        echo "<td>" . "<input type='text' name ='artName' value ='" . $row["artName"] . "' </td>";
        echo "<td>" . "<input type = 'hidden' name ='hidden' value='" . $row['artID'] . "' </td>";
        echo "<td>" . "<input type='submit' name ='update'" . " </td>";
        echo "</tr>";
    }
    echo "</form>";
    echo "</table>";

Use:

if ($result->num_rows > 0) {
// output data of each row
    while($row = mysqli_fetch_array($result)) {?>
        <form class='artisttable' action ='getartiststable.php' method ='post'>
            <tr>
                <td><input type='text' name ='artID' value ='<?php echo $row['artID'];?>' /></td>
                <td><input type='text' name ='artName' value ='<?php echo $row["artName"];?>' /></td>
                <td><input type = 'hidden' name ='hidden' value='<?php echo $row['artID'];?>' /></td>
                <td><input type='submit' name ='update'" . " </td>
            </tr>
        </form>
    <?php } ?>
    </table>

So you get a form for each data set. Here's a link on prepared statements with mysqli: http://php.net/manual/en/mysqli.quickstart.prepared-statements.php. You also should update your mark up. Tables for formatting aren't the best approach. Your inputs also weren't closed missing >.

Also this changed artisttable from an id to class because there will be multiples. Update CSS/JS accordingly.

score 0 · Answer 2 · answered Apr 27 '16 at 18:54

You are putting form tag inside tr which is not allowed td are only allowed so you have to remove that tr from there.

You have to use jquery or you can replace the table with some other grid structure so that it can look the same and the form can be placed there as well

One more suggestion Don't mix the php and html together separate them for the clean code

If you do all these you code will be like this

Trying to create an editable HTML table using PHP and mySQL but the table won't update

2 Answers2