17

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.

10 Answers10

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

  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
bistoco
  • 241
  • 1
  • 9
Kalaivanan
  • 459
  • 2
  • 8
  • 17
7

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.

Firze
  • 3,939
  • 6
  • 48
  • 61
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.

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
2

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:

Paulo Amaral
  • 747
  • 1
  • 5
  • 24
1

PDO returns values with the correct data types. Use it instead of CodeIgniter's database library.

0

No you can't get it with datatypes.

This is not related to codeignter.

MySQL returns everything as strings, except NULL.

1 : PHP mysql_fetch_object, all properties are string?

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

Community
  • 1
  • 1
Dino Babu
  • 5,814
  • 3
  • 24
  • 33
0

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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
0
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();
prash.patil
  • 687
  • 8
  • 7
-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

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
-3

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
Devidas Kadam
  • 944
  • 9
  • 19