PDO prepared statement with prepared sub-queries

Question

There is a complex query that is prepared in different methods. Here is a pseudo-code (not actual code) : It would prepare a query in one method, then prepare a sub-query in another method.

The idea is that other methods could add some other query parts, adding some conditions, and potentially some joins to other table. And all sub-queries would be prepared in their method.

And finally, the resulting query would be executed in the original method.

class{//pseudo code

public function getData($name,$club){
  $pdo = new PDO($dsn, $user, $pass, $opt);
  $stm1 = $pdo->query('SELECT name FROM users WHERE quote=?');
  $stm1 = $pdo->prepare('1');

  $stm2 = $this->getQueryPart();
  $stm1->statementBind($stm2);
  $stm1->execute();

  return $query;
}
protected function getQueryPart(){
  $pdo = new PDO($dsn, $user, $pass, $opt);
  $pdo->prepare('AND email = ? AND status=?', ['email@foobar.com','1']);
  return $pdo;
}

You've got a syntax error in the first query there. And it doesn't sound very efficient to create a new connection in each function, why not just pass it as an argument? — Qirel, Sep 17 '17 at 20:28
And you can't chain queries like that, `prepare()` takes a single querystring, you can't add conditions to it by calling it like that. The final querystirng is what should be passed to `prepare()`. — Qirel, Sep 17 '17 at 20:29
@Qirel, it is stated not once, but _thrice_ that it's a pseudo code - and you cannot expect pseudo code to run, as it's not actually some proper code. Let alone syntax errors. — Cedric, Sep 24 '17 at 10:24

score 1 · Accepted Answer · answered Sep 17 '17 at 22:00

Not possible using just PDO. You'll have to create a query builder class (or use one of the many existing ones out there) that wraps around PDO that will hold all of the query's properties, and build the full query in one go just before you execute it.

Here's a basic example of how this would work:

class Query {
    private $select;
    private $from;
    private $where = [];

    /**
     * Defines the column list (SELECT {$columns})
     *
     * @param string $columns
     * @return this
     */
    public function select($columns) {
        $this->select = $columns;
        return $this;
    }

    /**
     * Defines the table to select from (SELECT * FROM {$table})
     *
     * @param string $table
     * @return this
     */
    public function from($table) {
        $this->from = $table;
        return $this;
    }

    /**
     * List of conditions to AND (key = column, value = value)
     * e.g. $query->where(['name' => 'bob'])
     *
     * @param mixed[string] $conditions
     * @return $this
     */
    public function where(array $conditions) {
        $this->where = $conditions;
        return $this;
    }

    /**
     * Adds an AND condition
     *
     * @param string $column
     * @param mixed $value
     * @return this
     */
    public function where_and($column, $value) {
        $this->where[$column] = $value;
        return $this;
    }

    /**
     * Builds and executes the query, returning a PDOStatement
     *
     * @return PDOStatement
     */
    public function execute() {
        $query = sprintf(
            'SELECT %s FROM %s',
            $this->select,
            $this->from
        );

        $placeholders = array();

        if (!empty($this->where)) {
            $query .= ' WHERE ';

            $index = 0;
            foreach ($this->where as $column => $value) {
                if ($index > 0) {
                    $query .= ' AND ';
                }

                $query .= sprintf('`%s`', $column);

                if ($value === null) {
                    $query .= ' IS NULL';
                } else {
                    $placeholder = sprintf(':placeholder_%d', $index);

                    $query .= ' = ' . $placeholder;
                    $placeholders[$placeholder] = $value;
                }

                $index++;
            }
        }

        $pdo = new PDO($dsn, $user, $pass, $opt);
        $stmt = $pdo->prepare($query);
        $stmt->execute($placeholders);
        return $stmt;
    }
}

With this class you could, for example, do something like this:

class MyClass {
    public function getData($name, $club) {
        $query = new Query();
        $query->select('name')
              ->from('users')
              ->where(['quote' => '1']);

        $this->getQueryPart($query);
        $result = $query->execute();

        return $result->fetchAll(PDO::FETCH_ASSOC);
    }

    protected function getQueryPart($query) {
        $query->where_and('email', 'email@foobar.com')
              ->where_and('status', 1);
    }
}

See here for an example that doesn't actually execute the query, but prints the query and placeholders it would execute: https://3v4l.org/iKu2K

Note that this is a very basic example, just to give you an idea of how you could go about doing something like this. In a real-world scenario, you would probably want to expand this with the capability for OR-conditions as well as LIKE and != checks and maybe even grouping conditions so you could do something like WHERE a = 1 AND (b = 2 OR c = 5). Not to mention adding support for JOIN, ORDER BY, LIMIT and other fancy things.

Good answer! Just a note, though: The _prepare > bind > execute > fetch_ workflow - including validations and exception handling - is the task of a database adapter class. E.g the query builder is only responsible with constructing the sql statement. Here an example of [a DbAdapter class](https://stackoverflow.com/questions/46014772/return-multiple-response-data-in-one-response/46018999#46018999) of mine (the EDIT part), if it's of interest for you. — , Sep 17 '17 at 23:46

PDO prepared statement with prepared sub-queries

1 Answers1