CodeIgniter returns all fields as strings

Question

I have this query in CodeIgniter:

$query = $this->db->query("
      SELECT u.id
           , u.first_name, u.last_name
           , u.email_address
           , g.id AS group_id
           , g.name AS group_name
           , g.human_readable_name AS human_readable_group_name
        FROM users AS u
        JOIN groups AS g
          ON u.group_id = g.id
    ORDER BY u.last_name
");

return $query->result();

When I var_dump a row, I get this:

object(stdClass)#22 (4) {
  ["id"]=>
  string(2) "19"
  ["first_name"]=>
  string(9) "rightfold"
  // etc
}

id and group_id are both integers in PostgreSQL, but $query->result() returns them as strings. How can I tell CodeIgniter to return fields using the correct data types (texts as strings, integers as integers, timestamps as DateTime objects, etc…)?

I'm using CodeIgniter 2.1.4 on PHP 5.3.15.

Solution 1

A proper way to solve this issue is to set mysqli option in the Codeigniter driver. The below method is for who using mysqli as database driver.

Method 1:
Enable mysqlnd driver in your php extention. This will mostly solve your problem. If this not, then try method 2

Method 2 (May not work in all systems):

1. Open mysqli_driver.php file from system > database > drivers > mysqli_driver.php
2. Add below line to the code

Code:

public function db_connect($persistent = FALSE)
    ...
    $this->_mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10); // already exits
    $this->_mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE,TRUE); // add this line
    ...

After the code change, you get response like below

array (size=16)
    'auth_id' => int 1            
    'role' => string 'admin' (length=5)
    'permissions' => string 'all' (length=3)       
    'login_attempt' => int 0
    'active' => int 1        
    'mobile_verified' => int 1

Solution 2

This can be achieved using PDO driver. Then setting PDO::ATTR_EMULATE_PREPARES to false. This can be done in database.php. I believe using options array is undocumented feature.

Change application/config/database.php to use PDO and add PDO::ATTR_EMULATE_PREPARES to options:

    $db['default'] = array(
        'dsn'   => 'mysql:host=localhost;dbname={{my_db}};charset=utf8;',
        'username' => 'my_db_user',
        'password' => '123',
        'database' => 'my_db',
        'dbdriver' => 'pdo',
        'options' => [
            PDO::ATTR_EMULATE_PREPARES => false,
        ],
        ...
    );

Now you should have all the data returned from the DB with correct types.

Solution 3

If you don't want to change your system driver you can change your application/Core/MY_Controller.php.

Mixing Kalaivanan and Firze answers but using mysqli, not PDO.

Note: Decimals are still converted to string in pdo and also in mysqli, so use Double instead.

Create/change application/Core/MY_Controller.php as follows:

class MY_Controller extends CI_Controller
{ 
    public function __construct()
    {
        parent::__construct();        
        $this->db->conn_id->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
    }
}

WARNING: If you use BIT columns they won't work, they will always return zeros. Because a bug on driver. See: mysqlnd with MYSQLI_OPT_INT_AND_FLOAT_NATIVE fails to interpret bit columns.

Solution 4

This worked for me. You need to iterate through your dataset converting the array's variables if you want arbitrary casting. Add this function to your controller:

<?php 
/**
 * Runs a query and returns an array of array, with correct types (as Code
 * Igniter returns everything as string) 
 * @param string $sql
 * @return array Array of array, 1st index is the row number, 2nd is column name
 */
public function queryWithProperTypes($sql) {

  $query = $this->db->query($sql);
  $fields = $query->field_data();
  $result = $query->result_array();

  foreach ($result as $r => $row) {
    $c = 0;
    foreach ($row as $header => $value) {

      // fix variables types according to what is expected from
      // the database, as CodeIgniter get all as string.

      // $c = column index (starting from 0)
      // $r = row index (starting from 0)
      // $header = column name
      // $result[$r][$header] = that's the value to fix. Must
      //                        reference like this because settype
      //                        uses a pointer as param

      $field = $fields[$c];

      switch ($field->type) {

        case MYSQLI_TYPE_LONGLONG: // 8 = bigint
        case MYSQLI_TYPE_LONG: // 3 = int
        case MYSQLI_TYPE_TINY: // 1 = tinyint
        case MYSQLI_TYPE_SHORT: // 2 = smallint
        case MYSQLI_TYPE_INT24: // 9 = mediumint
        case MYSQLI_TYPE_YEAR: // 13 = year
          settype($result[$r][$header], 'integer');
          break;

        case MYSQLI_TYPE_DECIMAL: // 0 = decimal
        case MYSQLI_TYPE_NEWDECIMAL: // 246 = decimal
        case MYSQLI_TYPE_FLOAT: // 4 = float
        case MYSQLI_TYPE_DOUBLE: // 5 = double
          settype($result[$r][$header], 'float');
          break;

        case MYSQLI_TYPE_BIT: // 16 = bit
          settype($result[$r][$header], 'boolean');
          break;

      }

      $c = $c + 1;
    }
  }

  return $result;
}

Use it like:

$sql = "SELECT * FROM whatever_table";
$result = $this->queryWithProperTypes($sql);
var_dump($result); // check that all types are correctly cast

You might want to tweak it to support parameters, but that's the idea basically: $query->field_data() gives you the metadata for the dataset fields. With that you can "fix" the values returned by $query->result_array() using the settype function.

Keep in mind that it can be quite an overhead depending on the dataset size and you should only do it when the return is arbitrary. If you know the types beforehand it's better to cast them accordingly.

I tried PDO and it also returns all values as string.

See also:

• https://gist.github.com/paulera/370fb995988d983909e4a19769ee2f5d
• http://php.net/manual/en/function.settype.php
• https://www.codeigniter.com/user_guide/database/metadata.html#retrieve-field-metadata
• http://php.net/manual/en/mysqli.constants.php

View more solutions

Comments

[Admin]

The returned fields are still strings in PHP.

[Onder OZCAN]

What you mean that it returned as strings ? All sql output in php will be output string , It will fetch from db and gives you result as string. If you want to make a math. calculations, or something else you can use (int)$row->object_name, but in php data binding is not necessary.

[Admin]

PostgreSQL returns typed results which are not always strings. Also, other database interfaces—such as HDBC in Haskell and psycopg2 in Python—return fields with the correct types.

[Onder OZCAN]

I think it is not about with Database , Because also mySQL keeps integer string and enum data type and if you use it MySQL with other programming languages , you need also assign data binding instead of avoid from catch exception.

[Onder OZCAN]

Man! you are getting data from mySQL as a string. And you just saying me that it's mistake ... If you don't know how to php works never says a person about empty words. People here for help other users .

[Admin]

1) I'm not using MySQL. 2) PostgreSQL does not return strings.

[Onder OZCAN]

MySQL also does not return integer to string. But when you are fetching data from database with PHP, php casting whole data as string ( if you do not use extra data binding option as in Doctrine )

[Onder OZCAN]

Also you can check data type with var_dump($row->object_name);

[Onder OZCAN]

I also understood that helping ignored people in stackoverflow, gets minus , People also here that high reputation does not read the question completely !

[Admin]

I'm not using MySQL, as you can see in my question.

[bistoco]

Thanks!. This one works, even whent it's a little risky to edit framework code.

[TVH7]

This is the way better answer, maintaining some legacy code which runs CI and this is really helpful. Don't understand why the other answers are upvoted.

[mytharcher]

How about Postgresql?

[yeshansachithak]

$this->db->conn_id->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true); does not work on production.

[Márcio Antônio Slivak]

@yeshansachithak, Check if php and mysql server versions are the same or greater than your development environment.

[yeshansachithak]

Production Env = Server version: 5.6.49-cll-lve - MySQL Community Server (GPL), Development Env = Server version: 5.7.26 - MySQL Community Server (GPL), Both Env has PHP5.6.40

[Márcio Antônio Slivak]

It shoud work, php 5.6... already has the option (php.net/manual/pt_BR/mysqli.options.php). Check if your /config/database.php is using the correct driver on server: ... 'dbdriver' => 'mysqli' ... I don't know how to help you more. It is probably a configuration/enviroment problem either in php or mysql on your server.