Geek Answers Handbook

Dynamic PDO query building

I was an old school using mysql_query and starting to use PDO. It's great!

But in my old scripts, I built a dynamic query builder, and I have a hard time porting this to using PDO.

If someone can give me some direction, it will be great!

Here is the theory of this.

  • I have an array
  • Fields and values ​​of the database (after insertion).
  • Create a query string to get a valid PDO transaction

Here is part 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 something more like

 $results = $db->select(array("userId"=>111, "userId"=>222));

I know this is a high 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.

+4
source share
2 answers

You will need a separate $params parameter for your select method. I allowed to provide default parameters for method parameters. Like @userXxxx notes, you don't need a transaction just for 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 the full flexibility of PDOStatement :: fetchAll .
  • I'm not sure what you mean $dbFields as "fields plus VALUES". Can you explain?

[change]

You might want to check out the docs / examples for PDOStatement :: execute , as this seemed to be the cause of your confusion - in particular, the parameter of the $input_parameters method.

+1
source

How 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 a transaction (for all or nothing or catch exceptions and rollbacks) or prepared requests (for sending multiple requests) ...

-1
source

More articles:


All Articles