Read out data and edit

Question

i would like to read out all data records from a table and give individual data records a new value in "status" according to their "id".

<!DOCTYPE html>
<html lang="de">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, shrink-to-fit=no">
    <title>Table</title>
    <link rel="stylesheet" href="assets/bootstrap/css/bootstrap.min.css">
    <script src="assets/bootstrap/js/bootstrap.min.js"></script>
</head>
<body>  
    <?php
        $conn = new mysqli ("localhost", "root", "Z&r*%/Nm@X4x", "cms");
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $sql = "SELECT id, position, status FROM data";
        $stmt = $conn->prepare($sql); 
        $stmt->execute();
        $result = $stmt->get_result();
        $data = $result->fetch_all(MYSQLI_ASSOC);

        if ($data) {
            foreach($data as $row) {
    ?>
    <form method="post">
        <div class="table-responsive d-table">
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>POSITION</th>
                        <th>STATUS</th>
                        <th><button type="submit" name="change">CHANGE STATUS</button></th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td><?php echo $row['id']; ?></td>
                        <td><?php echo $row["position"]; ?></td>
                        <td><?php echo $row["status"]; ?></td>
                        <td>
                            <?php
                                if (isset($_POST['change'])) {
                                    $update = $update = "UPDATE data Set status = '2' WHERE id = {$row['id']}";
                                    mysqli_query($conn, $update);
                                    echo "Successful";
                                } elseif (!isset($_POST['change'])) {
                                    echo "Not clicked";
                                } else {
                                    echo "Failed";
                                }
                            ?>                      
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>
    </form>
</body>
<?php
        }
    } else {
        echo "No data found!";
        $conn->close();
    }
    ?>
</html>

My problem is, when I click the button, all records are changed and not just one. I hope someone can help to solve the problem.

.........................................................................................................................................................................................................

You are preparing and executing the SELECT statement, which doesn't have any variable or dynamic part, but not when you need to run an UPDATE with a variable. That's basically the opposite use case. In any case, you run UPDATE queries in a foreach loop for every row—not sure what you were expecting to happen. — Álvaro González, Oct 25 '21 at 18:04
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Oct 25 '21 at 19:15

F.Igor · Answer 1 · 2021-10-25T17:56:24.533

Try using {} to embed array elements into a string and enclose 'id' value

$update = "UPDATE data Set status = '0' WHERE id = {$row['id']}"

Otherwise, your query will interpret $row and [id] as separated items:

UPDATE data Set status = '0' WHERE id = [id]

and as a result, all your rows updated

Also consider to apply the status update button to each single row, there is no way to update only one row with a general "update status" button without sending a parameter of which row id will update. For example:

<!DOCTYPE html>
<html lang="de">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, shrink-to-fit=no">
    <title>Table</title>
    <link rel="stylesheet" href="assets/bootstrap/css/bootstrap.min.css">
    <script src="assets/bootstrap/js/bootstrap.min.js"></script>
</head>
<body>  
    <?php
        $conn = new mysqli ("localhost", "root", "Z&r*%/Nm@X4x", "cms");
        if ($conn->connect_error) {
            die("Connection failed: " . $conn->connect_error);
        }

        $sql = "SELECT id, position, status FROM data";
        $stmt = $conn->prepare($sql); 
        $stmt->execute();
        $result = $stmt->get_result();
        $data = $result->fetch_all(MYSQLI_ASSOC);

        if ($data) {
            foreach($data as $row) {
    ?>
    <form method="post">
        <div class="table-responsive d-table">
            <table class="table table-bordered">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>POSITION</th>
                        <th>STATUS</th>
                        <th></th>
                    </tr>
                </thead>
                <tbody>
                    <tr>
                        <td><?php echo $row['id']; ?></td>
                        <td><?php echo $row["position"]; ?></td>
                        <td><?php echo $row["status"]; ?></td>
                        <td>
                            <button type="submit" name="change" value="<?php echo $row['id']; ?>">CHANGE STATUS</button>
                            <?php
                                if (isset($_POST['change']) && $_POST["change"]==$row['id']) {
                                    $update = $update = "UPDATE data Set status = '2' WHERE id = {$row['id']}";
                                    mysqli_query($conn, $update);
                                    echo "Successful";
                                } elseif (!isset($_POST['change'])) {
                                    echo "Not clicked";
                                } else {
                                    echo "Failed";
                                }
                            ?>                      
                        </td>
                    </tr>
                </tbody>
            </table>
        </div>
    </form>
</body>
<?php
        }
    } else {
        echo "No data found!";
        $conn->close();
    }
    ?>
</html>

According to your updated posted code I've seen you are applying the update on every row (foreach) without any condition. If you post the id to be changed, you can do it individually. I've updated the answer with an example — F.Igor, Oct 25 '21 at 17:57

Read out data and edit

1 Answers1