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.

-1: This is the expected behavior. What is the **error**, that was caused by this form of returned data? — tereško, Aug 05 '13 at 22:53
@tereško that's not a valid reason for a downvote, for god's sake! If anything, it's PHP being again. — Bartek Banachewicz, Aug 05 '13 at 22:54
@rightfold Did you got any solution. Kindly update as answer.. facing same issues — Alizain Prasla, Mar 27 '14 at 13:47

score 14 · Answer 1 · edited Nov 24 '18 at 09:08

14

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):

Open mysqli_driver.php file from system > database > drivers > mysqli_driver.php
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

edited Nov 24 '18 at 09:08

bistoco

241
1
9

answered Jun 16 '18 at 07:32

Kalaivanan

459
2
8
17

1

Thanks!. This one works, even whent it's a little risky to edit framework code. – bistoco Nov 24 '18 at 07:18
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. – TVH7 Jan 03 '19 at 00:50
How about Postgresql? – mytharcher Jul 26 '19 at 05:30

Firze · Answer 2 · 2020-07-17T08:01:46.317

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.

Márcio Antônio Slivak · Answer 3 · 2020-06-27T13:28:17.973

5

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.

edited Jun 27 '20 at 13:28

answered Jan 02 '20 at 14:08

Márcio Antônio Slivak

501
8
9

`$this->db->conn_id->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);` does not work on production. – yeshansachithak Aug 17 '20 at 14:45
@yeshansachithak, Check if php and mysql server versions are the same or greater than your development environment. – Márcio Antônio Slivak Aug 17 '20 at 19:30
`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` – yeshansachithak Aug 18 '20 at 06:55
1

It shoud work, php 5.6... already has the option (https://www.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. – Márcio Antônio Slivak Aug 18 '20 at 22:09

score 2 · Answer 4 · answered Feb 24 '17 at 17:30

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.

1 : PHP mysql_fetch_object, all properties are string?

2 : Get MySQL Query Results as Their Native Data Type?

edited Jun 20 '20 at 09:12

Community

1
1

answered Aug 06 '13 at 09:42

Dino Babu

5,814
3
24
33

I'm not using MySQL, as you can see in my question. – Aug 06 '13 at 11:35

score 0 · Answer 7 · answered Nov 04 '13 at 04:11

My reading of the CodeIgnitor documentation (see http://ellislab.com/codeigniter/user-guide/database/fields.html) is that you'd want to loop through the field data and then make your own decision as to what to do with the data.

My guess is that to do this seamlessly you'd probably want to subclass their database and/or query classes, so that you could make them do what you want.

This doesn't strike me as a small amount of work, though it might not be too bad to get started.

score 0 · Answer 8 · answered Jul 29 '14 at 13:31

This is return sql object 

$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();

This query return multidimensional array as result
$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_array();

This query return single row as result
$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->row_array();

Onder OZCAN · Answer 9 · 2013-08-05T22:41:34.313

-2

If you want to get this query as object,

$query->row();
return $query;

In your view file

foreach($row as $query)
print $row->object_name;

PS: PHP always gives you output as "String". If you want to cast it in any data type, example string to integer , use (int) $value

edited Aug 05 '13 at 22:41

answered Aug 05 '13 at 22:14

Onder OZCAN

1,556
1
16
36

The returned _fields_ are still strings in PHP. – Aug 05 '13 at 22:15
1

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. – Onder OZCAN Aug 05 '13 at 22:17
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. – Aug 05 '13 at 22:19
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 Aug 05 '13 at 22:25
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 . – Onder OZCAN Aug 05 '13 at 22:29
5

1) I'm not using MySQL. 2) PostgreSQL _does not_ return strings. – Aug 05 '13 at 22:30
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 Aug 05 '13 at 22:34
Also you can check data type with var_dump($row->object_name); – Onder OZCAN Aug 05 '13 at 22:43
I also understood that helping ignored people in stackoverflow, gets minus , People also here that high reputation does not read the question completely ! – Onder OZCAN Aug 06 '13 at 06:33

score -3 · Answer 10 · answered Jun 04 '15 at 07:04

try this, Its helpful for me.

$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 ");

 // use return $query->result_array();
 // use like this

  $data = $query->result_array();
  foreach ($data as $key => $value) {
      $data['group_id'] = intval($value['group_id']);
      $data['dateField'] = date_create($value['dateField']);
    }

   var_dump($data);
   //or return $data

CodeIgniter returns all fields as strings

10 Answers10

1 : PHP mysql_fetch_object, all properties are string?

2 : Get MySQL Query Results as Their Native Data Type?