使用PDO修改预准备语句的查询字符串

Question

I'm extending the PDO class to add functions that manipulate the querystring of a prepared statement. For example make a query searchable or add pagination.

For example:

$documents_query = $DB->prepare( "SELECT id, title, file_name, datetime_added
                                    FROM documents
                                    ORDER BY datetime_added DESC" );

$documents_query->paginate( $page_number, RESULTS_PER_PAGE );

The question is how to modify the querystring (which is readonly) and save it, so it can get executed later on?

This is an example of how my extended PDOStatement class looks like:

class CustomStatement extends PDOStatement
{
    public function paginate( $current_page, $max_results )
    {
        // Add SQL_CALC_FOUND_ROWS so we can count the total amount of results
        $select_index = stripos( $this->queryString, 'SELECT' );

        $statement = substr_replace( $this->queryString, 'SELECT SQL_CALC_FOUND_ROWS', $select_index, 6 );

        // Add LIMIT to the end of the query
        $start_limit = ( $current_page - 1 ) * $max_results;

        $statement = $statement . ' LIMIT ' . $start_limit . ', ' . $max_results;

        // What to do here?
        return $this->prepare( $statement );
    }
}

Answer 1

You know, I generally find that extending PDOStatement is not your best option. At a minimum, subclassing PDOStatement means you need to subclass PDO. It gets messy. There also isn't really terribly much benefit in doing that -- you can't modify the original query, PDOStatement can't really be modified.

It is better, in my experience to write a wrapper around PDO which will allow you to manipulate the contents. This will let you manipulate the strings before they get to the point where you want to paginate. This also happens to be the way that most frameworks will use PDO.