PDO动态查询构建 [英] PDO Dynamic Query Building

问题描述

我已经老学校使用mysql_query，现在开始使用PDO。这是很棒的！

但是在我的旧脚本中，我构建了一个动态查询构建器，我有一个艰难的时间移植使用PDO。

如果任何人都可以给我一些方向，这将是巨大的！

这里是它的理论。

1. 我有一个数组


2. 数据库字段和值（插入时） li>
   
3. 创建查询字符串以产生有效的PDO事务

m尝试做。

  public $ dbFields; //这是一个字段数组加上VALUES 
 
 public function select（$ where，$ limit）{
 //这是什么我** **之前
 $ query =SELECT。 implode（，，$ this-> dbFields）。FROM。 $ this->表。WHERE。 $ where。。 $ limit。; 
 
 //现在我需要将它转换为PDO 
 $ this-> connection-> beginTransaction（）; 
 
＃b $ b＃b 
 
 
 $ select = $ this-> connection-> prepare（SELECT {$ this-> fieldNames} FROM {$ this-> table}） ; 
 
 //我需要BIND我的参数和值，但是我不确定最好的路由，当我有一个WHERE子句，包括AND/OR运算符。 
 
＃执行查询
 $ select-> execute（）; 
 
 $ this-> connection-> commit（）; 
} 
  

这是

之前的 HAD >

  $ results = $ db-> select（userId = 111 OR userId = 222）; 
  

但是我思考我需要做的是使用更多

  $ results = $ db-> select（array（userId=> 111，userId=> ; 222））; 
  

我知道这是一个很高的订单，我希望它在我试图做，但任何帮助尝试构建这些查询将非常感谢。

解决方案

您需要一个单独的 $ params code> select 方法。我自由地为方法参数提供了默认值。像@userXxxx注释，你不需要一个事务只是做一个 SELECT 。

 code><？php 
 
 class db {
 
 public $ connection; // PDO 
 public $ dbFields; //这是一个字段加VALUES数组
 
 public function select（$ where ='1'，$ params = array（），$ limit =''，$ fetchStyle = PDO :: FETCH_ASSOC ）{// fetchArgs，etc 
 $ fields = implode（'，'，$ this-> dbFields）; 
 
 //创建查询
 $ query =SELECT $ fields FROM {$ this-> table} WHERE $ where $ limit; 
 
 //准备语句
 $ 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 加上VALUES。你能解释一下吗？

---

b $ b

您可能需要查看的docs /示例PDOStatement :: execute ，因为这似乎是您的混淆根源 - 特别是 $ input_parameters 方法参数。

I have been old school using mysql_query and starting out now using PDO. Which is great!

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!

Here is the theory of it.

1. I have an array of
2. the DB Fields and Values (upon insert).
3. Create the query string to product a valid PDO transaction

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

This is what I HAD before

$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.

解决方案

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

Notes:

• 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?

---

[Edit]

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屋！