PDO 动态查询构建 [英] PDO Dynamic Query Building
问题描述
我一直在使用 mysql_query,现在开始使用 PDO.哪个好!
I have been old school using mysql_query and starting out now using PDO. Which is great!
但在我的旧脚本中,我构建了一个动态查询构建器,我很难使用 PDO 移植它.
But in my old scripts I had build a dynamic query builder, and i'm having a tough time porting that over using PDO.
如果有人能给我一些指导那就太好了!
If anyone can give me some direction that would be great!
这是它的理论.
- 我有一组
- 数据库字段和值(插入时).
- 创建查询字符串以生成有效的 PDO 交易
这是我正在尝试做的一部分.
Here is a portion of what i'm trying to do.
public $dbFields; // This is an array of the fields plus VALUES
public function select($where, $limit) {
// This is what I **had** before
$query = "SELECT ". implode(", ", $this->dbFields) ." FROM ". $this->table." WHERE ". $where ." ". $limit."";
// Now i need to convert that to PDO
$this->connection->beginTransaction();
# START Query
$select = $this->connection->prepare("SELECT {$this->fieldNames} FROM {$this->table}");
// I need to BIND my params and values, but i'm not sure the best route to take when I have a WHERE clause that includes, "AND" / "OR" operators.
# EXECUTE the query
$select->execute();
$this->connection->commit();
}
这是我之前拥有的
$results = $db->select("userId = 111 OR userId = 222");
但是我认为我需要做的是使用更像
But what i'm thinking I need to do is use something more like
$results = $db->select(array("userId"=>111, "userId"=>222));
我知道这是一项艰巨的任务,我希望它对我正在尝试做的事情有意义,但是在尝试构建这些查询方面的任何帮助将不胜感激.
I know this is a tall order, and I hope it makes sense in what i'm trying to do, but any help in trying to build these queries would be greatly appreciated.
推荐答案
您的 select
方法需要一个单独的 $params
参数.我冒昧地为方法参数提供了默认值.就像@userXxxx 注释一样,您不需要为了执行SELECT
而进行交易.
You'll need a separate $params
parameter to your select
method. I took the liberty of providing defaults for the method parameters. Like @userXxxx notes, you don't need a transaction just to do a SELECT
.
<?php
class db {
public $connection; //PDO
public $dbFields; // This is an array of the fields plus VALUES
public function select($where = '1', $params = array(), $limit = '', $fetchStyle = PDO::FETCH_ASSOC) { //fetchArgs, etc
$fields = implode(', ', $this->dbFields);
//create query
$query = "SELECT $fields FROM {$this->table} WHERE $where $limit";
//prepare statement
$stmt = $this->connection->query($query);
$stmt->execute($params);
return $stmt->fetchAll($fetchStyle);
}
//...
}
$where = 'userId IN(:userId1, :userId2)';
$params = array(':userId1' => 111, ':userId2' => 2222);
$db->select($where, $params);
注意事项:
- 如果你真的想要,你可以添加额外的方法参数来匹配PDOStatement::fetchAll.
- 我不确定您所说的
$dbFields
是字段加值"是什么意思.你能解释一下吗?
- If you really want, you can add additional method parameters to match up with all the flexibility of PDOStatement::fetchAll.
- I'm not sure what you mean about
$dbFields
being "fields plus VALUES". Can you explain?
您可能想查看 PDOStatement:: 的文档/示例执行,因为这似乎是您混淆的根源——特别是 $input_parameters
方法参数.
You might want to take a look at the docs/examples for PDOStatement::execute, since that seemed to be where your confusion was rooted--in particular, the $input_parameters
method parameter.
这篇关于PDO 动态查询构建的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!