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!

这是它的理论.

1. 我有一组
2. 数据库字段和值(插入时).
3. 创建查询字符串以生成有效的 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屋！