0

I am trying to update a MySQL field from 0 to 1 with a submit button. The table displays around 50 rows and has a submit button for each row.

I think I am misunderstanding something fundamental to where either the variable is set or the hidden input should be in relation to the while loop. I realise Ajax could be an option but trying to learn one thing at a time. I am expecting the button to update only the row that the submit button is displayed in and instead like so many other posts it updates only the very last row.

<?php
    include 'config.php';
?>

<?php
    
    $result = mysqli_query($con,"SELECT * FROM ppm");
    echo    '<table>
                <tr>
                    <td>ID</td>
                    <td>Extinguisher Location</td>
                    <td>ID No.</td>
                    <td>Type</td>
                    <td>Done?</td>
                    <td>?</td>
                    <td>Time Stamp</td>
                </tr>';
    echo "<form action='' method='post'>";
    
    while($row = mysqli_fetch_array($result))
        {
            $id = $row['id'];

            echo "<tr>";
            //echo "<input type='hidden' name='id' value='" . $id . "'>";
            echo "<td>$id</td>";
            echo "<td>" . $row['pretty_name'] . "</td>";
            echo "<td>" . $row['ident'] . "</td>";
            echo "<td>" . $row['type'] . "</td>";
            echo "<td>  <input value='done' name='Submit' type='submit' /> </td>";
            echo "<td>" . $row['done'] . "</td>";
            echo "<td>" . $row['updated_at'] . "</td>";
        }
                     
    echo "</form>";
    echo "</tr>";
    echo "</form>";
    echo "</table>";
    echo "<input value='Reset All' name='reset_all' type='submit' />";
?>
<?php
if(isset($_POST['Submit']))
{
    $sql = "UPDATE ppm SET done=1 WHERE id = '$id'";
    mysqli_query($con, $sql) or die("Cannot update");
}
?>
Dharman
  • 30,962
  • 25
  • 85
  • 135
meinnit2000
  • 1
  • 1
    There are a couple of issues with your code. For starters, read how you can have [a form in a table](https://stackoverflow.com/questions/5967564/form-inside-a-table), because currently, you're just inserting it at a random place. Next up, you close the `` tag twice, even though it's opened once. And since you're looking to have a single form, then it's invalid to have elements of the same name (in those cases only the last one is submitted). I'd recommend a separate form for each of the ids. – El_Vanja Mar 07 '21 at 09:49
  • 1
    On to your SQL. It's vulnerable as it's open to [SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). You should learn how to use prepared statements to [prevent it](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Also, since you're using the `$id` variable from the loop, but after the loop has finished, your query will be hardcoded to the last id, no matter which button you click. The id needs to be part of the data you submit. – El_Vanja Mar 07 '21 at 09:52
  • Thanks @El_Vanja, much appreciated. So in this scenario is it acceptable to wrap the whole table in the form? I assume I can still pull individual row id's eventually? Reading up on the prepared statements now. – meinnit2000 Mar 07 '21 at 10:10
  • Does this help? https://stackoverflow.com/a/13521863/1839439 You need a new `
    ` for every button. You can't wrap the whole table in a form
     – Dharman Mar 07 '21 at 13:17
  • While it's theoretically possible to wrap the whole table in a form here, I'd advise against it. If you did so, you'd need to generate unique names for all your buttons and that would massively complicate the code that handles the submission. If you create a single form for each row, then you can allow all the form elements to have the same name and keep your submission handling code as simple as it is currently. – El_Vanja Mar 07 '21 at 16:06
  • Thanks @Dharman yes very helpful. I can now see in the generated html that each hidden input now has the value of the row id. Just trying to work out how to pul that value into the query. – meinnit2000 Mar 07 '21 at 18:06
  • Thank you @El_Vanja I am using prepared statement now and adjusted the form tags to appear in each row. Will repost the code shortly. – meinnit2000 Mar 07 '21 at 18:08
  • Ok great its working! With the
    In the right place I can pull the row id from the hidden Thank you both. If I could just ask for a steer on the prepared statement? I started following the example for prepared statements like this `$sql = "UPDATE ppm SET done=1 WHERE id=?"; $stmt = $con->prepare($sql); $stmt->bind_param("i", $id);` but couldn't work out how to pull in the hidden value.
     – meinnit2000 Mar 07 '21 at 22:27
  • But the one I have working is this `$f0_submitted = (int) $_POST['f0']; $query_update = "UPDATE ppm SET done=1 WHERE id =$f0_submitted"; $stmt = $con->prepare($query_update);`. is this still safe? – meinnit2000 Mar 07 '21 at 22:29
  • No, because it's injected directly into the query and completely defeats the purpose. If you use the hidden input with `name='id'`, then you will have that inside `$_POST['id']`. – El_Vanja Mar 08 '21 at 10:05

0 Answers0