Getting boolean values from PostgreSQL

user_393888 0
Viewed 2261

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').

codeigniter boolean postgresql php
3 Answers

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"
}
user_92211470

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');
user_32536500
Related