CodeIgniter returns all fields as strings

Question

I have this request 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 have a var_dump string, I get the following:

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

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

I am using CodeIgniter 2.1.4 on PHP 5.3.15.

Answer 1

It worked for me. You need to iterate through a dataset that converts array variables if you want arbitrary casting. Add this function to the 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 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 as:

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

You can configure it to support parameters, but the idea is basically: $query->field_data() gives you metadata for the fields in the dataset. With this, you can "fix" the values ​​returned by $query->result_array() using the settype function.

Keep in mind that this can be quite overhead depending on the size of the dataset, and you should only do this when the return is arbitrary. If you know the types in advance, it is better to classify them.

I tried PDO and it also returns all the values ​​as a 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

Answer 2

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

Answer 3

No, you cannot get it using data types.

This is not code related.

MySQL returns everything as strings except NULL.

1: PHP mysql_fetch_object, are all properties a string?

2: Get MySQL query results as their own data type?

Answer 4

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

My guess is that in order to do this easily, you probably want to subclass your databases and / or query classes so that you can get them to do what you want.

This does not affect me as a small amount of work, although for starters it may not be so bad.

Answer 5

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

Answer 6

I struggled with this for a long time. And I could not find a working solution that was not too hacky. This is what I came up with. Works inside all controllers, but can be extended to all CI classes in a similar way.

• Change application / config / database.php to use PDO:

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

• Create the MY_Controller class to extend the CI_Controller application / core / MY_Controller.php :

   class MY_Controller extends CI_Controller { public function __construct() { parent::__construct(); $this->db->conn_id->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); } } 

• Using the extended class in controllers. Change this in all your controllers (example user controller):

Change this:

class Users extend CI _Controller {

into it →

class Users extends MY _Controller {

You should now have all the data returned from the database with the correct types.

Answer 7

If you want to receive this request as an object,

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

In your view file

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

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

Answer 8

try it, its useful 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