php only modifies last row sqlite

Question

I'm using the following code to modify a sqlite database.

<?php

$db = new SQLite3("my_sqlite.db");

if( isset($_POST['submit_data']) ){

    $id = $_POST['id'];
    $name = $_POST['name'];
    $email = $_POST['email'];

    $query = "UPDATE students set name='$name', email='$email' WHERE rowid=$id";

    $db->exec($query);
}

$query = "SELECT rowid, * FROM students";
$result = $db->query($query);
?>




        <form action="" method="post">
        <table border="1">

                <tr>
                    <td>ID</td>
                    <td>Name</td>
                    <td>Email</td>
                </tr>

            <?php while($data = $result->fetchArray()) {?>
                
                <tr>
                    <td><input name="id" type="text" value="<?php echo $data['rowid'];?>"></td>
                    <td><input name="name" type="text" value="<?php echo $data['name'];?>"></td>
                    <td><input name="email" type="text" value="<?php echo $data['email'];?>"></td>
                </tr>

            <?php } ?>

        </table>
        <input name="submit_data" type="submit" value="Update Data">
        </form>

I've read many related answers but I can't solve the problem, which is, ONLY THE LAST ROW OF THE INPUT WORKS, IT JUST MODIFIES THE LAST ROW OF THE SQLITE.

you should not skip using prepared queries, enter into name `';--` and your see why — Lawrence Cherone, Jun 16 '21 at 18:58
Wow, as you can see im a beginner. I dont know what prepared queries are but that removed my database :( — pitin, Jun 16 '21 at 19:32
see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Lawrence Cherone, Jun 16 '21 at 19:57

pitin · Accepted Answer · 2021-06-16T20:55:19.590

I SOLVED IT LIKE THIS!!

<?php

$db = new SQLite3("my_sqlite.db");

if( isset($_POST['submit_data']) ){

    foreach ($_POST['id'] AS $k => $id) {
        $name = $_POST['name'][$k];
        $email = $_POST['email'][$k];

    $query = "UPDATE students set name='$name', email='$email' WHERE rowid=$id";

    $db->exec($query);
} }

$query = "SELECT rowid, * FROM students";
$result = $db->query($query);
?>




        <form action="" method="post">
        <table border="1">

                <tr>
                    <td>ID</td>
                    <td>Name</td>
                    <td>Email</td>
                </tr>

            <?php while($data = $result->fetchArray()) {?>

                <tr>
                    <td><input name="id[]" type="text" value="<?php echo $data['rowid'];?>"></td>
                    <td><input name="name[]" type="text" value="<?php echo $data['name'];?>"></td>
                    <td><input name="email[]" type="text" value="<?php echo $data['email'];?>"></td>
                </tr>

            <?php } ?>

        </table>
        <input name="submit_data" type="submit" value="Update Data">
        </form>

php only modifies last row sqlite

1 Answers1