PDO Fetch data returns array of string

Question

I am trying to get data from a MySQL database with PDO but unfortunately PDO returns the result as an array of strings. I want to keep the native MySQL data types in the result array.

I have tried setting PDO::ATTR_DEFAULT_FETCH_MODE to both PDO::FETCH_ASSOC AND PDO::FETCH_OBJ but it was still returning INT data as string.

Here is the result of dump:

array (size=1)
  0 => 
    object(stdClass)[27]
      public 'id' => string '3' (length=1)
      public 'avatar' => string '' (length=0)
      public 'fullName' => string 'Mikheil Janiashvili' (length=19)
      public 'email' => string '[email protected]' (length=17)
      public 'phone' => string '23 3537 20 03544' (length=12)
      public 'educationGE' => string '' (length=0)
      public 'educationEN' => string '' (length=0)
      public 'educationRU' => string '' (length=0)
      public 'experienceGE' => string '' (length=0)
      public 'experienceEN' => string '' (length=0)
      public 'experienceRU' => string '' (length=0)
      public 'descriptionGE' => string '' (length=0)
      public 'descriptionEN' => string '' (length=0)
      public 'descriptionRU' => string '' (length=0)

There are a couple of things that may affect this. One, emulated prepares I think might cause this, so turn that off, the second is that it really depends on the PDO drivers being used. Since you don't say what DBMS you're using, nor what version of the lib / driver is being used (see phpinfo) there's not much else to say. — Jonnix
– Jonnix, Commented Dec 21, 2015 at 14:32
I am using php 5.5.12 disabled emulated prepares and everything is fine now. — Mikheil Janiashvili
– Mikheil Janiashvili, Commented Dec 21, 2015 at 14:51
@miken32 thanks for showing good example it will help other people to find answer faster, that's the exact solution. — Mikheil Janiashvili
– Mikheil Janiashvili, Commented Dec 21, 2015 at 20:55

Your Common Sense · Accepted Answer · 2023-10-07 09:17:10Z

7

When you instantiate your PDO object, you need to tell it to use MySQL's native prepared queries:

$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

Assuming you're using PHP >= 5.3 you will be using the mysqlnd library, which can return proper data types from prepared queries.

Example:

$ php -a
Interactive shell

php > $db = PDO("mysql:host=localhost;dbname=test", "test", "");
php > $res = $db->query("SELECT 1 as num, PI()");
php > var_dump($res->fetch(PDO::FETCH_ASSOC));
array(2) {
  ["num"] => string(1) "1"
  ["PI()"] => string(8) "3.141593"
}

php > $db = PDO("mysql:host=localhost;dbname=test", "test", "", [PDO::ATTR_EMULATE_PREPARES=>false]);
php > $res = $db->query("SELECT 1 as num, PI()");
php > var_dump($res->fetch(PDO::FETCH_ASSOC));
array(2) {
  ["num"] => int(1)
  ["PI()"] => float(3.141593)
}
php >

edited Oct 7, 2023 at 9:17

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

answered Dec 21, 2015 at 20:36

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PDO Fetch data returns array of string

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related