PHP PDO Update ID Not Passing Dynamically

Question

I am trying to perform a simple update CRUD operation and this function only works if my id is static. For example, when I add id = 1, it works. But when I try to pass an id using :id or id = ?, it will not work. However, I don't get an error. Any ideas what I'm missing? I have searched for an answer for two days. Any help would be appreciated. My code is below. The function below is in my Scripture class. I have the object instantiated on the edit page like this.

if(isset($_GET['id'])){
    $scripture->id = $_GET['id'];
    echo $scripture->id;
} else {
    echo "There is no id";
}

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

    $scripture->book = $_POST['book'];
    $scripture->chapter = $_POST['chapter'];
    $scripture->verse = $_POST['verse'];
    $scripture->body = $_POST['body'];

    $scripture->editScripture($scripture->id, $scripture->book, $scripture->chapter, $scripture->verse, $scripture->body);

}


class Scripture {

    public $id;
    public $book;
    public $chapter;
    public $verse;
    public $body;

    public function createScripture($postData)
    {
        global $db;
        $this->book = $_POST['book'];
        $this->chapter = $_POST['chapter'];
        $this->verse = $_POST['verse'];
        $this->body = $_POST['body'];

        try {
            $createQuery = $db->prepare("INSERT INTO scriptures (book, chapter, verse, body) VALUES (:book, :chapter, :verse, :body) ");
            $createQuery->execute(["book" => $this->book, "chapter" => $this->chapter, "verse" => $this->verse, "body" => $this->body]);
        } catch (PDOException $ex) {
            echo "An error occurred " . $ex->getMessage();
        }

        header("Location: show-all.php");

        ?>
            <div class="container">
                <div class="alert alert-success">
                    <p>Scripture created successfully</p>
                </div>
            </div>
        <?php

    }

    public function editScripture($id, $book, $chapter, $verse, $body)
    {
        global $db;

        $this->book = $_POST['book'];
        $this->chapter = $_POST['chapter'];
        $this->verse = $_POST['verse'];
        $this->body = $_POST['body'];

        try {
            $query = "UPDATE scriptures SET book = :book, chapter = :chapter, verse = :verse, body = :body WHERE id = :id";

            $statement = $db->prepare($query);

            $statement->bindparam(":id", $this->id);
            $statement->bindparam(":book", $this->book);
            $statement->bindparam(":chapter", $this->chapter);
            $statement->bindparam(":verse", $this->verse);
            $statement->bindparam(":body", $this->body);

            $statement->execute();

            return true;

        } catch (PDOException $ex) {
            echo "An error occurred " . $ex->getMessage();
        }

    }

}


<form action="edit.php" method="POST">
    <div class="form-group">
        <label for="book">Book</label>
        <input type="text" class="form-control" name="book" id="book">
    </div>
    <div class="form-group">
        <label for="chapter">Chapter</label>
        <input type="text" class="form-control" name="chapter" id="chapter">
    </div>
    <div class="form-group">
        <label for="verse">Verse</label>
        <input type="text" class="form-control" name="verse" id="verse">
    </div>
    <div class="form-group">
        <label for="body">Body</label>
        <input type="text" class="form-control" name="body" id="body">
    </div>
    <div class="form-group">
        <input type="submit" class="btn btn-success" name="edit">
    </div>
</form>

Don't use `bindParam` and pass the values in the `execute` statement. Only do one of them. I use the parameters passed in the `execute` statement, — Ryan Vincent, Oct 24 '17 at 22:03
No where in your code sample have you shown where `$id` or `$this->id` come from... — aknosis, Oct 24 '17 at 22:16
I tried this but was still unable to pass the id dynamically. I have all the other CRUD functionalities working. — jkloster7, Oct 25 '17 at 21:01
debugging: `var_dump($id, $this->id);`. I suspect that you haven't set the values in the object property? — Ryan Vincent, Oct 26 '17 at 11:43
Thanks for looking into this. I used the var_dump and the error is an undefined variable for ID. When I try to set the id in the function I use $this->id = $_POST['id']; but it still tells me that it is undefined. So I'm not sure where the problem lies. I also tried using the $_GET['id'] in the same manner but it fails. Thanks so much for the help. — jkloster7, Oct 26 '17 at 13:25
Using var_dump I see that the form is picking up the variables just fine. As I type through the fields everything is passed into an array like it should. But it's not passing to the database. This is what I just typed into the array. I typed into the form and hit submit, this is the var_dump. string(4) "Test" string(1) "2" string(2) "10" string(15) "This is a test.". — jkloster7, Oct 26 '17 at 13:40
Why don't you show us the whole class? Because no one knows how you setup your id, at least. And show us the table structure, e.g. include the create table syntax in your question. — , Oct 26 '17 at 14:52
@aendeerei - I just added the full class. Thanks for the help! — jkloster7, Oct 26 '17 at 17:12
Show also the line where you call `editScripure` please. I hope it's not the one showed in your question. — , Oct 26 '17 at 17:48
@aendeerei - Ok I just updated my code above to show how I call editScripture. — jkloster7, Oct 26 '17 at 17:59
@jkloster7 No, don't use $_GET. Use only $_POST. See my answer ("with your version"). And read the answer provided by YourCommonSense too. — , Oct 26 '17 at 18:33

score 1 · Answer 1 · 2017-10-26T21:32:24.653

With your code version:

<?php
include('classes/config.php');
include('classes/scripture.php');

$scripture = new Scripture();

if (isset($_POST['edit'])) {
    // Validate id.
    if (!isset($_POST['id']) || empty($_POST['id'])) {
        echo 'Not a valid id. Please provide a valid one.';
    } else {
        $id = $_POST['id'];
        $book = $_POST['book'];
        $chapter = $_POST['chapter'];
        $verse = $_POST['verse'];
        $body = $_POST['body'];

        $affectedRows = $scripture->editScripture($id, $book, $chapter, $verse, $body);

        $message = 'Scripture successfully updated. Affected rows: ' . $affectedRows;
    }
} elseif (isset($_POST['create'])) {
    $book = $_POST['book'];
    $chapter = $_POST['chapter'];
    $verse = $_POST['verse'];
    $body = $_POST['body'];

    $lastInsertId = $scripture->createScripture($book, $chapter, $verse, $body);

    $message = 'Scripture successfully created. Id: ' . $lastInsertId;
}

class Scripture {

    public function createScripture($book, $chapter, $verse, $body) {
        global $db;

        try {
            $sql = "INSERT INTO scriptures (book, chapter, verse, body) VALUES (:book, :chapter, :verse, :body)";

            $bindings = [
                "book" => $book,
                "chapter" => $chapter,
                "verse" => $verse,
                "body" => $body,
            ];

            $query = $db->prepare($sql);
            $query->execute($bindings);
        } catch (PDOException $ex) {
            echo "An error occurred " . $ex->getMessage();
        }

        // Id of the last inserted row.
        return $db->lastInsertId();
    }

    public function editScripture($id, $book, $chapter, $verse, $body) {
        global $db;

        try {
            $sql = "UPDATE scriptures SET book = :book, chapter = :chapter, verse = :verse, body = :body WHERE id = :id";

            $bindings = [
                "book" => $book,
                "chapter" => $chapter,
                "verse" => $verse,
                "body" => $body,
                "id" => $id,
            ];

            $statement = $db->prepare($sql);
            $statement->execute($bindings);
        } catch (PDOException $ex) {
            echo "An error occurred " . $ex->getMessage();
        }

        // Number of affected rows.
        return $statement->rowCount();
    }

}
?>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1" />
        <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=yes" />
        <meta charset="UTF-8" />
        <!-- The above 3 meta tags must come first in the head -->

        <title>Demo</title>

        <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" type="text/css" rel="stylesheet" />
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" type="text/css" rel="stylesheet" />
        <script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript" integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=" crossorigin="anonymous"></script>
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" type="text/javascript"></script>
    </head>
    <body>

        <form action="edit.php" method="POST">
            <input type="hidden" class="form-control" name="id" id="id" value="1">
            <div class="form-group">
                <label for="book">Book</label>
                <input type="text" class="form-control" name="book" id="book">
            </div>
            <div class="form-group">
                <label for="chapter">Chapter</label>
                <input type="text" class="form-control" name="chapter" id="chapter">
            </div>
            <div class="form-group">
                <label for="verse">Verse</label>
                <input type="text" class="form-control" name="verse" id="verse">
            </div>
            <div class="form-group">
                <label for="body">Body</label>
                <input type="text" class="form-control" name="body" id="body">
            </div>
            <div class="form-group">
                <input type="submit" class="btn btn-success" name="edit">
            </div>
        </form> 

    </body>
</html>

Comments are not for extended discussion; this conversation has been [moved to chat](http://chat.stackoverflow.com/rooms/157625/discussion-on-answer-by-aendeerei-php-pdo-update-id-not-passing-dynamically). — Andy, Oct 26 '17 at 22:59

PHP PDO Update ID Not Passing Dynamically

1 Answers1

With your code version: