PDO, MySQL and Nested Queries

Stefan - 29. Oktober 2008

PDO (PHP Data Objects) is regarded as state-of-the-art for database access in PHP. But as useful as an abstraction layer may be, the usage of PDO can really be painful. Why? In default mode, PDO uses unbuffered queries to access MySQL. This results in a somewhat strange behaviour: You cannot use two result statements at the same time on the same database connection. (see an example)

The official solution for this problem is to empty the result set of a previous query using fetchAll(). If you have to process every row in a table, this method copies the entire table into a PHP array. This is fine for small tables. But in a recent project, there’s one table which is 8 GB large. I surely don’t want to copy this table into my memory …

Thanks to Ilia, there is a solution for this: Just tell the underlying MySQL driver to use buffered queries.

$dbh->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

The downside of this approach is obvious: Our PDO-based database access is no longer portable as we now use a MySQL specific functionality. But as long as there is no other solutions for iterating over big tables and issuing a query for every row, I think this is the way to go.

Abgelegt in: PHP

2 Kommentare:

why not use mysqli directly?

The whole project uses PDO. I think it’s bad coding style to use several database access methods in one project. And it’s to much work to refactor the complete code to use mysqli.

Schreibe einen Kommentar
benötigt (wird nicht angezeigt)