PDO Dynamic Query Building

Question

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.

Answer 1

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.

Answer 2

What about this?

public function select($where, $limit) {
    $query = "SELECT ". implode(", ", $this->dbFields) ." FROM ". $this->table." WHERE ". $where ." ". $limit."";
    $this->connection->query($query);
}

//Use what you had before:
$results = $db->select("userId = 111 OR userId = 222");

Not sure why you want to use transaction (for all-or-nothing basis or catching exceptions and rollback) or prepared queries (for sending multiple queries)...