Get ID from inserted row so the table can get updated with a new value

Question

Just trying some different stuff and run into this problem. I'm echoing out some different table values onto an example site, then from a dropdown menu i want to change the data from lets say 1 to 2. I'm just using a post form action to do it, but the problem is how do i get the ID of this exact row with the dropdown menu, when i got 10 different rows that are outputed? Atm I only change all the row values from 1 to 2 when i use this code:

PHP

if (isset($_POST['insert2']))
{
    $status2 = $_POST['status2'];

    $sql2 = ("UPDATE test3 SET status='$status2' WHERE id=id");

    if (mysqli_query($conn, $sql2)) {
    header("Location: index.php");
    exit;
} else {
    echo "Error: " . $sql2 . "<br>" . mysqli_error($conn);
}}

HTML with echo

<?php while($row = mysqli_fetch_assoc($result)) { echo 
'<div class="box">
    <div class="box-id">
        <p class="box-id-text">Bonus id: ' . $row ['id'] . '</p>
    </div>
    <div class="box-date">
        <p class="box-date-text">Date laget: ' . $row ['date'] . '</p>
    </div>
    <div class="box-status">
        <div class="box-endre-status">
            <p class="box-status-text">Status: Pending,</p>
            <form action="" method="post">
                <select name="status2" class="endre-status">
                    <option value="" disabled selected>Endre status</option>
                    <option value="1">1</option>
                    <option value="2">2</option>
                </select>
                <input type="submit" name="insert2" value="Oppdater">
            </form>
        </div>
    </div>
</div>';}?>

Very new to this and would be very much appreciated if someone can help me in the right direction.

score 2 · Accepted Answer · answered Feb 16 '19 at 00:17

Your problem is that you're not passing the id value to update back with your form. You can resolve that by adding a hidden input to your form; add something like this to your echo after <form action="" method="post">:

<input type="hidden" name="id" value="' . $row['id'] . '">

Then in your PHP, add

$id = $_POST['id'];

after $status2 = $_POST['status2']; and change your UPDATE query to

$sql2 = ("UPDATE test3 SET status='$status2' WHERE id=$id");

If id is a string you will need to enclose it in quotes in the line above.

Your next issue will be that you are vulnerable to SQL injection. To avoid that, you will need to use prepared statements. This question has a really good explanation of how to do that, and you can also look at the PHP manual page for MySQLi prepared statements here.

Thank you very much for the nice explanation. I managed to make something similar to what you do, but your way of hiding the input field is much better so i will change it to this. :) Also i will come to SQL injection later, just want to understand the basics of this first. Thanks! — Seb, Feb 16 '19 at 00:22

Get ID from inserted row so the table can get updated with a new value

1 Answers1