phpmyadmin Question posted in
The official documentation can be found here.

Incorrect record updating – Phpmyadmin

I am trying to update a specific row in a table; however, when the query runs, it updates the last record added instead of the record selected.

The SQL statement was taken straight from phpmyAdmin. I have tried "UPDATE registration_tbl SET Paid = ‘PAID’ WHERE ID=’$row21’" and that still did not work.

Have I put something wrong in the code?

<table class="table table-striped table-hover table table-responsive-sm table-responsive-md table-responsive-lg">
                <tr>
                    <th>Title</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Sex</th>
                    <th>Age</th>
                    <th>Address Type</th>
                    <th>Address 1</th>
                    <th>Address 2</th>
                    <th>Home</th>
                    <th>Work</th>
                    <th>Cell</th>
                    <th>Email Address</th>
                    <th>Congregation</th>
                    <th>RMC</th>
                    <th>Auxillary</th>
                    <th>Occupation</th>
                    <th>Category</th>
                    <th>Username</th>
                    <th>Submission Date</th>
                    <th>Payment Status</th>
                    <th>Action</th>
                </tr>
                <?php
                    $conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

                    $result_set = mysqli_query($conn,"SELECT * FROM registration_tbl");   
                    $num_messages = mysqli_num_rows($result_set);

                    $num = 0;

                    while($row = mysqli_fetch_array($result_set))
                    {
                        $row1 = $row["Title"];
                        $row2 = $row["FirstName"];
                        $row3 = $row["LastName"];
                        $row4 = $row["Sex"];
                        $row5 = $row["Age"];
                        $row6 = $row["AddressType"];
                        $row7 = $row["Address1"];
                        $row8 = $row["Address2"];
                        $row9 = $row["Home"];
                        $row10 = $row["Work"];
                        $row11 = $row["Cell"];
                        $row12 = $row["EmailAdd"];
                        $row13 = $row["Congregation"];
                        $row14 = $row["RMC"];
                        $row15 = $row["Auxillary"];
                        $row16 = $row["Occupation"];
                        $row17 = $row["Category"];
                        $row18 = $row["Username"];
                        $row19 = $row["DateSubmitted"];
                        $row20 = $row["Paid"];
                        $row21 = $row["ID"];

                        
                        $num++;

                        echo "<tr>";
                        echo "<td>$row1</td>";
                        echo "<td>$row2</td>";
                        echo "<td>$row3</td>";
                        echo "<td>$row4</td>";
                        echo "<td>$row5</td>";
                        echo "<td>$row6</td>";
                        echo "<td>$row7</td>";
                        echo "<td>$row8</td>";
                        echo "<td>$row9</td>";
                        echo "<td>$row10</td>";
                        echo "<td>$row11</td>";
                        echo "<td>$row12</td>";
                        echo "<td>$row13</td>";
                        echo "<td>$row14</td>";
                        echo "<td>$row15</td>";
                        echo "<td>$row16</td>";
                        echo "<td>$row17</td>";
                        echo "<td>$row18</td>";
                        echo "<td>$row19</td>";
                        echo "<td>$row20</td>";

                        if($row20 != "PAID")
                        {
                            echo "<td><input type='submit' class='btn btn-success' name='paid' value='PAID' /></td></tr>";
                        }
                        
                    }
                    echo "</table></br>";
                    echo "<table><tr><td>";
                    echo $num_messages . " Registration(s) Found!";
                    echo "</td></tr></table>";

                    if(isset($_POST['paid']))
                    {
                        $conn = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

                        $updatePaymentStmt = "UPDATE `registration_tbl` SET `Paid` = 'PAID' WHERE `registration_tbl`.`ID` = $row21;";

                        if(mysqli_query($conn, $updatePaymentStmt))
                        {
                            echo "<script>alert('Payment updated successfully!')</script>";
                        }
                        else
                        {
                            echo "<script>alert('Error in updating Payment!')</script>";
                        }
                    }
Tags:

2

Answers


  1. 0

    I think you’re going to want a separate form for each row in the table. And you’ll need a hidden field in that form containing the ID so the server knows which ID to process when it receives the submission.

    Remove any <form>...</form> tags you may have placed to wrap around the whole table, and instead use:

    if($row20 != "PAID")
{
  echo "<td><form action='' method='post'><input type='submit' class='btn btn-success' name='paid' value='PAID' /><input type='hidden' name='id' value='".$row["ID"]."'/></form></td></tr>";
}

    and then

    if(isset($_POST['paid']))
{
  $id = $_POST["id"];
  ///etc, you can now use $id in a parameter in your query, to select the correct row

    P.S. The rest of the code could also be greatly simplified, as others have mentioned in the comments, and you should definitely fix the SQL injection issue – that’s a serious security problem.

    Login or Signup to reply.
  2. 0

    This bug comes about from a flaw in your thinking rather than unexpected behaviour in the code.

    Effectively you have a while loop that iterates over the entire results set (from the first query) and updates the $row* variables. What this means is that $row21 is always going to be the last selected record. If you were to chuck an ORDER BY id DESC on the end you’d find that the first record was always updated…

    So what you actually want to do is add the id into the button – and make each button it’s own form – so that when the form is posted the intended id is in the button’s value.

    Something like:

    <?php
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

$registrations = $mysqli->query($conn,"SELECT * FROM registration_tbl");   
$num_messages  = $registration->num_rows;

while ($row = $registrations->fetch_assoc() {
    echo "<tr>";
    echo "<td>{$row["Title"]}</td>";
    echo "<td>{$row["FirstName"]}</td>";
    echo "<td>{$row["LastName"]}</td>";
    echo "<td>{$row["Sex"]}</td>";
    echo "<td>{$row["Age"]}</td>";
    echo "<td>{$row["AddressType"]}</td>";
    echo "<td>{$row["Address1"]}</td>";
    echo "<td>{$row["Address2"]}</td>";
    echo "<td>{$row["Home"]}</td>";
    echo "<td>{$row["Work"]}</td>";
    echo "<td>{$row["Cell"]}</td>";
    echo "<td>{$row["EmailAdd"]}</td>";
    echo "<td>{$row["Congregation"]}</td>";
    echo "<td>{$row["RMC"]}</td>";
    echo "<td>{$row["Auxillary"]}</td>";
    echo "<td>{$row["Occupation"]}</td>";
    echo "<td>{$row["Category"]}</td>";
    echo "<td>{$row["Username"]}</td>";
    echo "<td>{$row["DateSubmitted"]}</td>";
    echo "<td>{$row["Paid"]}</td>";

    echo $row["Paid"]] !== "PAID" ?
        "<td><form method='post'><button class='btn btn-success' name='paid' value='{$row["ID"]}'>Paid</button></form></td>" :
        "<td></td>";
    }

    echo "</tr>";
}

echo "</table></br>";
echo "<table><tr><td>";
echo $num_messages . " Registration(s) Found!";
echo "</td></tr></table>";

if ($_POST['paid'] ?? null) {
    $sql   = "UPDATE `registration_tbl` SET `Paid` = 'PAID' WHERE `registration_tbl`.`ID` = ?";
    $query = $mysqli->prepare($sql);
    $query->bind_param("i", $_POST["paid"]);
    
    echo $query->execute() ?
        "<script>alert('Payment updated successfully!')</script>" :
        "<script>alert('Error in updating Payment!')</script>";
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search