简写PDO查询

Question

Currently to perform a query with PDO, I use the following lines of code:

$sql = "SELECT * FROM myTable WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

And after some research, I found a shorter way of executing the same command:

$stmt_test = $conn->prepare("SELECT * FROM status WHERE status_id = ?");
$stmt_test->execute([$id])->fetchAll(PDO::FETCH_ASSOC);
$result = $stmt_test->fetchAll(PDO::FETCH_ASSOC);

From there I thought I could possibly make it even shorter with the following code:

$stmt_test = $conn->prepare("SELECT * FROM status WHERE status_id = ?");
$result = $stmt_test->execute([$id])->fetchAll(PDO::FETCH_ASSOC);

But I get the following error:

Fatal error: Call to a member function fetchAll() on a non-object in /home/.../index.php on line 20

QUESTION: Why am I getting this error? From my understanding, $stmt_test->execute([$id]) should be executing first, then the result of that would execute the ->fetchAll(PDO::FETCH_ASSOC) and from there return the array to $result, but since the error is happening, something must be flawed in my logic. What am I doing wrong? Also, does anyone know a better shorthand method to perform the previous query?

Answer 1

So you've got an answer for the question "Why I am getting this error", but didn't get one for the "shorthand PDO query".

For this we will need a bit of a thing called "programming".

One interesting thing about programming is that we aren't limited to the existing tools, like with other professions. With programming we can always create a tool of our own, and then start using it in stead of an old one.

And Object Oriented Programming is especially good with it, as we can take an existing object and just add some functionality, leaving the rest as is.

For example, imagine we want a shorthand way to run a prepared query in PDO. All we need is to extend the PDO object with a new shorthand method. The hardest part is to give the new method a name.

The rest is simple: you need only few lines of code

class MyPDO extends PDO
{
    public function run($sql, $bind = NULL)
    {
        $stmt = $this->prepare($sql);
        $stmt->execute($bind);
        return $stmt;
    }
}

This is all the code you need. You may store it in the same file where you store your database credentials. Note that this addition won't affect your existing code in any way - it remains exactly the same and you may continue using all the existing PDO methods as usual.

Now you have to change only 2 letters in PDO constructor, calling it as

$conn = new MyPDO(...the rest is exactly the same...);

And immediately you may start using your shiny new tool:

$sql = "SELECT * FROM myTable WHERE id = :id";
$result = $conn->run($sql, ['id' => $id])->fetchAll(PDO::FETCH_ASSOC);

Or, giving it a bit of optimization,

$result = $conn->run("SELECT * FROM myTable WHERE id = ?", [$id])->fetchAll();

as you can always set default fetch mode once for all, and for just a single variable there is no use for the named placeholder. Which makes this code a real shorthand compared to the accepted answer,

$stmt_test = $conn->prepare("SELECT * FROM status WHERE status_id = ?");
$stmt_test->execute([$id]);
$result = $stmt_test->fetchAll(PDO::FETCH_ASSOC);

and even to the best answer you've got so far,

$result = $conn->prepare("SELECT * FROM status WHERE status_id = ?");
$result->execute([$id]);

not to mention that the latter is not always usable, as it fits for getting an array only. While with a real shorthand any result format is possible:

$result = $conn->run($sql, [$id])->fetchAll(); // array
$result = $conn->run($sql, [$id])->fetch(); // single row
$result = $conn->run($sql, [$id])->fetchColumn(); // single value
$result = $conn->run($sql, [$id])->fetchAll(PDO::FETCH_*); // dozens of different formats