CODEWITHSUNDEEP
phpmysql
user10746010
user10746010

Reputation:

How to update a specific sql row from a html table

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.

Upvotes: 0

Views: 2451

Answers (1)

blahy
blahy

Reputation: 1319

You need to update two things:

  1. 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.

  1. 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.

Upvotes: 2

Related Questions