FETCH PDO used incorrectly?

as usual for a DB result. As mentioned in the Manual for bindColumn(), PDO does not know what database type a value is (unless explicitly told). it’s very much like form data, PHP receives them as string because the original data type is not preserved on transmission.

I guess that confirms the StackOverflow thread. Thanks.

Still unsure of how to translate bindValue into my code.

$findSub=$this->pdo->prepare("SELECT * FROM Subscribers WHERE activationKey=:activationKey");
$findSub->execute(array(":activationKey" => $activationKey));

would translate to

$findSub=$this->pdo->prepare("SELECT * FROM Subscribers WHERE activationKey=:activationKey");
$findSub->bindValue(":activationKey", $activationKey); // assuming string type
#$findSub->bindValue(":activationKey", $activationKey, PDO::PARAM_INT); // assuming integer type
$findSub->execute();

but that wouldn’t change anything about the result data types.

So why would I do this then?

Purely asking as a noob. If this won’t allow me to read this as an integer, why bother with these PARAMs?

I can easily read my database subscribe column as a string. I want it as an integer. I can read the results if I do ==, but not ===. To read it as ===, it needs to be wrapped around quotes.

you should always specify, which data type your input is. from your original code the only assumption about $activationKey is that it’s a string, even if your DB says it must be an integer. so it’s a major convenience for someone reading your code to have that bit of information.

if you want the output data type to be set, look into the already mentioned bindColumn() method.

I have. I’ve had issues finding an example I can translate into my own code.

quick example for bindColumn():

try {

$pdo = new PDO("sqlite::memory:");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("
    CREATE TABLE `animals`
    (
        `id`   INTEGER PRIMARY KEY,
        `name` TEXT NOT NULL,
        `type` TEXT NOT NULL
    )"
);

$setup = $pdo->prepare("
    INSERT INTO
        `animals` (`name`, `type`)
    VALUES
        (:name, :type)"
);
$setup->bindParam("name", $name);
$setup->bindParam("type", $species);

$animals = array(
    "pussy"  => "cat",
    "tweety" => "bird",
    "waldi"  => "dog",
    "tom"    => "cat",
    "jerry"  => "mouse",
    "idefix" => "dog"
);

foreach ($animals as $name => $species)
{
    $setup->execute();
}
// end table setup

$stmt = $pdo->prepare('SELECT id, name FROM animals WHERE type = ?');
$stmt->bindValue(1, 'cat', PDO::PARAM_STR);
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->bindColumn('id', $id, PDO::PARAM_INT);

foreach ($stmt as $row) 
{
    var_dump($id, $row);
}

} catch (Exception $e) {
    echo $e->getLine(), ": ", $e->getMessage();
}

output:

int(1)
array(2) {
    ["id"] => string(1) "1"
    ["name"] => string(5) "pussy"
}
int(4)
array(2) {
    ["id"] => string(1) "4"
    ["name"] => string(3) "tom"
}

Eh. Still too complicated of an example. Strays too far off of what my code looks like.

I Just switched my data to “no” and “yes”. Easier…

then what about using $subDetails["confirmed"] === '1' ?

I mentioned above that I’ve done that in my previous testing. If I’m just going to treat it has a string, then why not make something more easily readable, like “no” and “yes”?

because MySQL’s “booleans” are 1 and 0.

consider the following:

SELECT COUNT(*) FROM Subscribers WHERE activationKey=:activationKey AND confirmed = TRUE

this returns you either 0 or 1 directly. and then you can throw a boolean cast on it:

$isConfirmed = (bool) $stmt->fetchColumn();

Yeah that was a question earlier up, whether casting was the best way to go. Mitt and I got no answer to this.

So should I be casting?! Or binding?

In your case I would do casting, since there are only two possible results.

(assuming you mean result binding, not input binding)

Ok that works then. Thanks.

PS. doing $stmt->bindColumn('confirmed', $isConfirmed, PDO::PARAM_BOOL) would return a boolean.

[quote=“RyanReese, post:20, topic:191596, full:true”]Can you at least confirm whether MySQL always returns results in varchar instead of the data type of the column (e.g. integer?)[/quote]hell no

mysql returns integers for integers, dates for dates, strings for strings, blobs for blobs, et cetera

2 Likes

Thank you. Dormilich confirmed this was a PHP PDO issue above:

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

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.