速记 PDO 查询 [英] shorthand PDO query

查看:59
本文介绍了速记 PDO 查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

目前使用 PDO 执行查询,我使用以下代码行:

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

但我收到以下错误:

致命错误:在非对象上调用成员函数 fetchAll()/home/.../index.php 第 20 行

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

问题:为什么会出现此错误?根据我的理解, $stmt_test->execute([$id]) 应该首先执行,然后执行结果将执行 ->fetchAll(PDO::FETCH_ASSOC) 并从那里将数组返回到 $result,但由于错误正在发生,我的逻辑中一定存在缺陷.我究竟做错了什么?另外,有没有人知道执行上一个查询的更好的速记方法?

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?

推荐答案

所以你已经得到了问题为什么我收到这个错误"的答案,但没有得到速记 PDO 查询"的答案;.

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 instead of a whole set of old tools.

面向对象编程尤其擅长这方面,因为我们可以使用现有对象并只添加一些功能,其余部分保持原样.

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

例如,假设我们想要一种在 PDO 中运行准备好的查询的速记方式.我们所需要的只是使用新的速记方法扩展 PDO 对象.最难的部分是为新方法命名.

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

这是您需要的所有代码.您可以将其存储在存储数据库凭据的同一文件中.请注意,此添加不会以任何方式影响您现有的代码 - 它保持完全相同,您可以像往常一样继续使用所有现有的 PDO 功能.

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 functionality as usual.

现在你只需要改变 PDO 构造函数中的 2 个字母,调用它

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

或者,给它一些优化,

$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]);

更不用说后者并不总是可用,因为它只适合获取数组.虽然使用 real 速记,任何结果格式都是可能的:

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

这篇关于速记 PDO 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆