Updating SQLite database with PHP-CodePudding

I have been working on this problem for the past 3 or 4 hours. I have an SQLite database which interacts with PHP. So far, I have been able to perform 3 of the 4 CRUD operations. The only one which won't work is the the U part (update). I am able to print data into my form, but clicking on the submit button will just make the app hang for some time until I get a custom error message from my app. Also, the record doesn't get updated.

Your help is very much appreciated!

Here is my current code for the edit/update form and page from my app:

<?php 
    // $db->close();
    // echo $_GET['id'];
?> 

<!-- 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_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();
    }
?>

<!-- print database content -->
<?php
    // has the form been submitted?
    // if not, show the HTML form
    if (!isset($_POST['submit'])) {
?>

<form action="<?php echo htmlentities($_SERVER['PHP_SELF'] . "?id=" . $r['id']); ?>" 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']); ?>">

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

</form>

<!-- update database content -->
<?php

    } else {


        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";

            // UPDATE table_name
            // SET column1 = value1, column2 = value2...., columnN = valueN
            // WHERE [condition];

            $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);
            
            $success = $stat->execute();

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

            $db = null;

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

    }
?>

CodePudding user response：

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>