FETCH PDO used incorrectly?

Databases
38

While mysql itself returns the correct data types, the php mysql drivers return strings. And since PDO sits on top of the mysql drivers then it returns everything as strings as well.

This is a well know problem. http://stackoverflow.com/questions/3157831/how-can-i-determine-the-column-type-with-pdo

You can demo this quite easily:

CREATE TABLE types 
(
  id INT AUTO_INCREMENT NOT NULL, 
  strx    VARCHAR(255)  NOT NULL,
  intx    INTEGER,
  boolx   BOOLEAN  DEFAULT false,
  decx    DECIMAL(5,2),
  floatx  FLOAT,
  PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

INSERT INTO types (id,strx,intx,boolx,decx,floatx) VALUES
 (1,'ONE',42,true,5.22,3.14159),
 (2,'TWO',42,0,   5.22,3.14159);

public function getPdoConn()
{
  $conn = new \PDO('mysql:dbname=tests;host=localhost','tests','tests');
  $conn->setAttribute(\PDO::ATTR_ERRMODE,\PDO::ERRMODE_EXCEPTION);
  $conn->setAttribute(\PDO::ATTR_DEFAULT_FETCH_MODE,\PDO::FETCH_ASSOC);
  return $conn;
}
public function testPdoTypes()
{
  $conn = $this->getPdoConn();
  $rows = $conn->query('SELECT * FROM types;')->fetchAll();
  foreach($rows as $row)
  {
    foreach($row as $col => $value)
    {
      echo sprintf("Col %s %s %s\n",$col,$value,gettype($value));
    }
  }
  $this->assertTrue($rows[0]['boolx'] === 1);
  $this->assertTrue($rows[1]['boolx'] === 0);
}

The output looks like:
Col id 2 string
Col strx TWO string
Col intx 42 string
Col boolx 0 string
Col decx 5.22 string
Col floatx 3.14159 string

Very sad. You only real choice is to type cast the columns. I did just look through the manual again hoping to find a simple solution but did not find any.

Fortunately there are pdo wrappers which can do these type conversions for you. In particular Doctrine 2’s Dbal connection objects works well: http://doctrine-dbal.readthedocs.org/en/latest/reference/types.html. You can even define and plugin your own types.

The same test but with dbal:

public function getDbalConn()
{
  $dbUrl = 'mysql://tests:tests@localhost/tests';

  $config = new \Doctrine\DBAL\Configuration();
  $connParams = 
  [
    'url' => $dbUrl,
    'driverOptions' => [\PDO::ATTR_EMULATE_PREPARES => false],
  ];
  $conn = \Doctrine\DBAL\DriverManager::getConnection($connParams, $config);
  return $conn;
}
public function testDbalTypes()
{
  $conn = $this->getDbalConn();
  $rows = $conn->executeQuery('SELECT * FROM types;')->fetchAll();
  foreach($rows as $row)
  {
    foreach($row as $col => $value)
    {
      echo sprintf("Col %s %s %s\n",$col,$value,gettype($value));
    }
  }
  $this->assertTrue($rows[0]['boolx'] === 1);
  $this->assertTrue($rows[1]['boolx'] === 0);
}
Col id 2 integer
Col strx TWO string
Col intx 42 integer
Col boolx 0 integer
Col decx 5.22 string
Col floatx 3.1415901184082 double

Much better and the tests now pass. No need to do anything special.

Yes the boolean is still an integer. It’s really a mysql issue. You could actually map using dbal but that is extra work and I seldom bother.

And the double has some extra digits added. Not really sure why but never bothered to do the research.

1 Like