使用PHP更新SQLite資料庫-有解無憂

在過去的 3 或 4 個小時里，我一直在研究這個問題。我有一個與 PHP 互動的 SQLite 資料庫。到目前為止，我已經能夠執行 4 個 CRUD 操作中的 3 個。唯一不起作用的是 U 部分（更新）。我可以將資料列印到我的表單中，但單擊提交按鈕只會使應用程式掛起一段時間，直到我從我的應用程式收到自定義錯誤訊息。此外，記錄不會更新。

非常感激你的幫助！

這是我的應用程式中編輯/更新表單和頁面的當前代碼：

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

    }
?>

uj5u.com熱心網友回復：

瀏覽完你的源檔案后，發現記錄會被鎖定，因為你是在做select，然后在做update（這當然不是必須的，在所有情況下）。

因此，請使用以下代碼來解決問題（我包含了一個名為 actionx 的隱藏欄位，以防止 PHP 同時執行選擇和更新）：

所以對于edit.php，它應該是：

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

}    
?>

另一方面，對于one.php（顯示單個記錄），請使用：

<?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>

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/352005.html

標籤：php sqlite

上一篇：盡管受互斥鎖保護，但SQLite更新失敗并顯示“資料庫被鎖定”

下一篇：沒有關系的房間中的一對多關系