Updating SQLite database with PHP

After browsing your source files, it is found that the record will be locked because you are doing the select and then doing the update immediately (which is of course not necessary, in all cases).

Hence, Please use the following code to fix the problem (I have included a hidden field known as actionx to prevent the PHP to do both select and update at the same time) :

So for the edit.php, it should be:

<?php 
    // $db->close();
    // echo $_GET['id'];
?> 
<?php if ($_REQUEST["actionx"] =="") { ?>
<!-- get database content -->
<?php
    // define PDO - tell about the database file
    $db = new PDO("sqlite:database.db");

    try {
        $sql = "SELECT * FROM students_tb WHERE id=:myId";

        // prepare statement
        $statement = $db->prepare($sql);

        // get value from querystring and bind
        $id = filter_input(INPUT_POST, "id");
        $statement->bindValue(":myId", $id, PDO::PARAM_INT);

        // execute the query
        $statement->execute();

        // create array of records
        $r = $statement->fetch();
        $db = null;

        // check contents of array
        if (!$r) {
            echo "No record found";
        } else {
            echo "record found";
        }
    }

    catch (PDOException $e) {
        print "We had an error: " . $e->getMessage() . "<br>";
        die();
    }
?>

<form action="edit.php" method="post">

    <label for="sname">Student's Name</label>
    <input type="text" name="sname" required value="<?php echo htmlspecialchars($r['sname']); ?>">

    <label for="score">Score</label>
    <input type="number" name="score" required value="<?php echo htmlspecialchars($r['score']); ?>">
<input type=hidden name=id value="<?php echo $_REQUEST["id"]; ?>">

<input type=hidden name=actionx value="update">

<button type="submit" name="submit">Submit</button>

</form>
<?php } ?>


<?php if ($_REQUEST["actionx"] !="") { ?>

<?php



        try {
            $id = $_POST['id'];

            $db = new PDO("sqlite:database.db");
            // print out error messages is something goes wrong
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $sql = "UPDATE students_tb SET sname = :sname, score = :score WHERE id = :id";


            $stat = $db->prepare($sql);

            // named params

            $sname = filter_input(INPUT_POST, "sname");
            $stat->bindValue(":sname", $sname, PDO::PARAM_STR);

            $score = filter_input(INPUT_POST, "score");
            $stat->bindValue(":score", $score, PDO::PARAM_INT);

            $id = filter_input(INPUT_POST, "id");
            $stat->bindValue(":id", $id, PDO::PARAM_INT);

            
            $success = $stat->execute();

            // does the value exist?
            if ($success) {
                echo "The student has been updated in the database.";
                echo "<p><a href='index.php'>Go back to the main page.</a></p>";
            } else {
                echo "The student has NOT been updated in the database.";
                echo "<p><a href='index.php'>Go back to the main page.</a></p>";
            }

            $db = null;

        } catch (PDOException $e) {
            // for development
            print "We had an error: " . $e->getMessage() . "<br>";
            die();
        }

}    
?>

On the other hand, for the one.php (displaying a single record), please use this:

<?php 
    echo $_GET['id'];
?> 



<?php
    // define PDO - tell about the database file
    $db = new PDO("sqlite:database.db");

    try {
        $sql = "SELECT * FROM students_tb WHERE id=:myId";

        // prepare statement
        $statement = $db->prepare($sql);

        // get value from querystring and bind
        $id = filter_input(INPUT_GET, "id");
        $statement->bindValue(":myId", $id, PDO::PARAM_INT);

        // execute the query
        $statement->execute();

        // create array of records
        $r = $statement->fetch();
        $db = null;

        // check contents of array
        if (!$r) {
            echo "No record found";
        } else {
            echo "record found";
        }
    }

    catch (PDOException $e) {
        print "We had an error: " . $e->getMessage() . "<br>";
        die();
    }
?>

<h1><?php echo htmlspecialchars($r['id']); ?></h1>
<p>Description: <?php echo htmlspecialchars($r['sname']); ?></p>
<p>Score: <?php echo htmlspecialchars($r['score']); ?></p>

<form action="<?php echo 'delete.php?id=' . htmlspecialchars($r['id']) ?>" method="POST">
    <button type="submit" name="delete">Delete this record</button>
</form>


<form action="edit.php" method="POST">
    <button type="submit" name="delete">Edit this record</button>
<input type=hidden name=id value="<?php echo $r['id']; ?>">
</form>