I'm using CodeIgniter 3 and a PostgreSQL 9.4 database. Is there a way to get boolean values in PHP if the data type of the respective column in the database is a boolean?
I'm using CodeIgniters query builder class as follows:
$qry = $this->db->get_where('user', array('username' => mb_strtolower($username)));
return $qry->row();
All boolean values from the user table are represented by strings ('t' and 'f').
To complete Narf's answer, here a generic class to convert into native boolean php type pgsql returns. Beware this is not an optimal solution because it will convert every string "f" or "t" into a boolean, regardless the column in the Db is a boolean or not.
<?php
class PgSqlObject
{
public function __set($name, $value)
{
if ($value === 'f')
$value = false;
elseif ($value === 't')
$value = true;
$this->$name = $value;
}
public function __get($name)
{
if (isset($this->$name))
{
return $this->$name;
}
}
}
$this->db->query($sql)->custom_result_object('PgSqlObject');
there is a better solution: use PDO driver, e.g.:
$db['default']['dsn'] = 'pgsql:host=localhost;dbname=base';
$db['default']['username'] = 'username';
$db['default']['password'] = 'password';
$db['default']['dbdriver'] = 'pdo';
$db['default']['port'] = 5432;
then your results will be in the correct types
response in postgre driver (json_encode):
{
"created_at": "2017-10-20 12:20:49",
"created_by": "82027609253",
"updated_at": "2017-10-20 12:20:49",
"updated_by": "82027609253",
"deleted": "f",
"id": "184",
"codigo_ibge": "1501402",
"uf": "PA",
"nome": "Belém"
}
response in pdo driver (json_encode):
{
"created_at": "2017-10-20 12:20:49",
"created_by": 82027609253,
"updated_at": "2017-10-20 12:20:49",
"updated_by": 82027609253,
"deleted": false,
"id": 184,
"codigo_ibge": 1501402,
"uf": "PA",
"nome": "Belém"
}
No.
If you're using booleans while inserting or updating data, CodeIgniter will convert them, but it can't do that while fetching records because it has no knowledge of the data types it reads - it's a Query Builder, not an ORM.
What you can do is create a bunch of classes to represent your specific result sets and use custom_result_object() or custom_row_object() when fetching.
Obviously, the 't'/'f' to true/false conversion would have to be performed by these custom-made objects.
