How to update a specific sql row from a html table

Question

So I have a HTML table that grabs SQL data and shows the data in the HTML table. I have a button in the HTML table aswell witch reredicts to update.php. Here I want the row that the user pressed the button in updates the column "paid" to "Paid". Screenshot of the HTML table: https://emildeveloping.com/screenshots/outredden-pardonableness-amharic.png

The thing is that I can't get it working that it updates just that specific row, it updates all rows right now.

Ive tried searching around for same questions but haven't found any solutions.

This is the PHP code:

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "emildeveloping2";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "UPDATE purchases SET paid='Paid'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

This is the PHP snippet of the HTML Table

            $result = $conn->query($sql);

            if ($result->num_rows > 0) {
                echo "<table id='purchases' class='purchases'><tr class='header'><th>Invoice ID</th><th>Customer ID</th><th>Product</th><th>Name</th><th>Email</th><th>Adress</th><th>Security Number</th><th>City</th><th>Zip Code</th><th>Country</th><th>Cost</th><th>Payment Plan</th><th>Status</th><th>Options</th></tr>";
                // Visa datan
                while($row = $result->fetch_assoc()) {
                    echo "<tr><td>" . $row["id"]. "</td><td>" . $row["customerid"]. "</td><td>" . $row["product"]. "</td><td>" . $row["name"]. "</td><td>" . $row["email"]. "</td><td>" . $row["adress"]. "</td><td>" . $row["securitynumber"]. "</td><td>" . $row["city"]. "</td><td>" . $row["zipcode"]. "</td><td>" . $row["country"]. "</td><td>" . $row["cost"]. "</td><td>" . $row["paymentplan"]. "</td><td>" . $row["paid"]. "</td><td><a class='fas fa-check-square' href='update.php'></a></td></tr>";
                }
                echo "</table>";
            } else {
                echo "There is no active calls.";
            }

I want just a specific row to update where id = id.

I can't tell how you structured your table, but did you tried to change your SQL query and add a "WHERE id= (your preferred id)" ? — Chris Vera
– Chris Vera, Commented Feb 17, 2019 at 20:06
you need to make a small <form> on each row with your update button (note button, not hyperlink) in it and a hidden field containing the ID for that row. Then when it posts back, you retrieve the ID from the POST variables and add it to a WHERE clause in your query. — ADyson
– ADyson, Commented Feb 17, 2019 at 21:34
@ChrisVera Yeah it works with just adding WHERE id = 1 for example so no problems with the SQL query itself. — user10746010
– user10746010, Commented Feb 18, 2019 at 7:29

blahy · Accepted Answer · 2019-02-17 22:16:07Z

2

You need to update two things:

Add the id of the row you want to update as a GET argument in the link so it is passed to PHP script:

<a class='fas fa-check-square' href='update.php?row_id='.$row["id"].'>

Now if you check the links, each of them should be unique: update.php?row_id=1, update.php?row_id=2, etc.

Handle this added url parameter in the PHP script so it is used to select desired row in database table:

$sql = "UPDATE purchases SET paid='Paid' WHERE id=".$_GET['row_id'];

If you get it working use http://php.net/manual/en/mysqli.prepare.php prepare method instead of query, so your code is not prone to sql injecton. Something like this:

$mysqli->prepare("UPDATE purchases SET paid='Paid' WHERE id=?")) {
$stmt->bind_param("i", $_GET['row_id']);
$stmt->execute();

Another thing would be using POST requests instead of GET links to prevent CSRF vulnerability.

edited Feb 17, 2019 at 22:16

answered Feb 17, 2019 at 21:43

blahy

1,3191 gold badge8 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ADyson Over a year ago

Using POST on its own doesn't remove the thread of CSRF. You need to employ CSRF tokens as well. Otherwise, great answer.

Collectives™ on Stack Overflow

How to update a specific sql row from a html table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related