Is Is Possible to Set a Default Pdo Fetch Mode

Is is possible to set a default PDO fetch mode?

$connection = new PDO($connection_string);
$connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

PDO set default fetch mode to FETCH_UNIQUE

$PDO->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_UNIQUE);

This does not work because, PDO::FETCH_UNIQUE is not a valid constant for PDO::ATTR_DEFAULT_FETCH_MODE. See here the valid fetch mode constants http://www.php.net/manual/en/pdostatement.fetch.php

It seems that there is no way to enable PDO:FETCH_UNIQUE using $PDO->setAttribute(). You should pass it explicitly in the call to fetchAll().

MySQL PDO - Setting Default Fetch Mode?

You can set the default fetch mode for the PDO object:

$DBH->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

This, of course, you do as soon as you've initialized your $DBH (PDO) object.

(For detailed documentation on this, see http://www.php.net/manual/de/pdo.setattribute.php)

In PHP, How do I set default PDO Fetch Class?

Another kind of hack would be to extend PDOStatement, override its fetch methods and let your PDO instance use that as the default statement class.

As an example I'll just demonstrate overriding fetch()¹ and leave fetchAll() and what have you up to you, if you wanna go this route:

class Db_Row
{
}

class PDOStatementWithClass
    extends PDOStatement
{
    private $fetch_class;

    // PHP complained when I tried to make this public
    protected function __construct( $fetch_class = 'StdClass' )
    {
        // internally set the fetch class for later use
        $this->fetch_class = $fetch_class;
    }

    // let $fetch_style default to PDO::FETCH_CLASS in stead of PDO::FETCH_BOTH
    public function fetch( $fetch_style = PDO::FETCH_CLASS, $cursor_orientation = PDO::FETCH_ORI_NEXT, $cursor_offset = 0 )
    {
        // make sure we're really dealing with the correct fetch style
        if( $fetch_style == PDO::FETCH_CLASS )
        {
            // then automatically set the fetch mode of this statement
            parent::setFetchMode( $fetch_style, $this->fetch_class );
        }

        // go ahead and fetch, we should be good now
        return parent::fetch( $fetch_style, $cursor_orientation, $cursor_offset );
    }
}

$db = new PDO( /* etc... */ );
// set default fetch mode to FETCH_CLASS
$db->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_CLASS );
// override what statement class to use, and provide constructor arguments (found out by trial and error)
$db->setAttribute( PDO::ATTR_STATEMENT_CLASS, array( 'PDOStatementWithClass', array( 'Db_Row' ) ) );

This has the added benefit that you'll only have to define your PDO::FETCH_CLASS once in your application, and not in every query.

---

^{1) I'm surprised PHP didn't complain about overriding the signature of the method by the way.}

Do I need to use PDO::FETCH_ASSOC if I set as default in DEFAULT_FETCH_MODE connection?

The default fetch style of fetch() and fetchAll() is PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH).

You're setting PDO::ATTR_DEFAULT_FETCH_MODE in your connection settings which overrides PDO::FETCH_BOTH.

So, basically you do not need to specify the fetch style in every single query. $user = $stmt->fetch(); or $user = $stmt->fetchAll(); should be enough in your case.

global setting of PDO's setFetchMode(PDO::FETCH_ASSOC)

Since 5.2

$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

PDO fetch mode - before/after query

Your example uses a simplification: after execution, but before you can use the records, you also need to fetch them. These are the fetch and fetchAll methods in the PDOStatement class.

But when using the foreach construct, you are using PDOStatement's Traversable interface, which calls the fetchAll method instead of you, at the start of the foreach loop.

So in both examples, fetchAll gets called after you set the fetch mode.

Edit:

That means that PDO retrieve the query result both as numeric and
assoc from the db server? Hmm...

PDO won't fetch the records from the DB in one or two way, it will always do it in the same way - the difference is just how it will present the results to you. If you specify a fetch mode, PDO will build the requested structure for you. If you use the default, or specify FETCH_BOTH, it will build you two different representation, but from the same result set. This parameter won't make in difference in the commands sent to the SQL server.

---

Related Topics