Reading boolean correctly from Postgres by PHP

10
votes

The main problem of this thread is moved to here about boolean datatype in PHP / Postgres.

The problem is the conversion of t and f to true and false, since Postgres stores true and false as such.

How can you use the variable a_moderator in SESSION?

I fetch the value of the variable a_moderator by

#1 code of how I get the variable

    $result = pg_prepare($dbconn, "moderator_check_query", 
        "SELECT a_moderator 
        FROM users
        WHERE email = $1;"
    );
    $a_moderator = pg_execute($dbconn, "moderator_check_query", array($_SESSION['login']['email']));

    $rows = pg_fetch_all ( $a_moderator );
  
    foreach ( $rows as $row ) {
       $_SESSION['login']['a_moderator'] = $row['a_moderator'];
    }

I use it unsuccessfully by

#2 code of how I use the variable unsuccessfully

if ( $_SESSION['login']['a_moderator'] == 't' ) {
   // do this
}

I also ran unsuccessufully the values such as true in the place of t. The variable in the SESSION has the value f such that

#3 Output which tells me he value of the varibale

Array ( [login] => Array ( 
   [passhash_md5] => dd2f85814c35fd465c30b1472f5d3af8 
   [email] => [email protected] 
   [logged_in] => 1 [user_id] => 13 
   [username] => oeauoeh 
   [a_moderator] => t ) 
)
4
phppostgresql
you're still not properly fetching the data from with pg_fetch_array. It needs the PGSQL_ASSOC flag. - davethegr8
davethegr8: You suggest me that it is best to use pg_fetch_all and then simply foreach to read the data. - Léo Léopold Hertz 준영
davethegr8: I fixed the problem with pg_fethch_array. I have still the same problem. - Léo Léopold Hertz 준영

4 Answers

14
votes

Select boolean field from postgre as ::int and in php cast to bool.

 "SELECT a_moderator::int 
        FROM users
        WHERE email = $1;"

$isModerator = (bool)$row['a_moderator'];

2
votes

This isn't a direct answer to the question, but here's an example demonstrating that pg_*() functions do in fact return the postgres boolean true value as the PHP string 't':

[example]$ cat scratch.php 
<?php
//connect to the database...
require_once 'db_connect.php';

//query
$rows = pg_fetch_all(pg_query('SELECT TRUE::bool AS true'));
//dump returned array, and test with type-safe identity comparator
var_dump($rows, $rows[0]['true'] === 't');

[example]$ php scratch.php 
array(1) {
  [0]=>
  array(1) {
    ["true"]=>
    string(1) "t"
  }
}
bool(true)
[example]$
1
votes

I wrote a function in postgres to export boolean to PHP readable boolean:

You just use it this way:

SELECT php_bool(columna) from
table

Here is the function:

CREATE OR REPLACE FUNCTION php_bool(boolean)
RETURNS numeric LANGUAGE plpgsql

AS
$BODY$

BEGIN

  IF $1 = true THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END
$BODY$
-2
votes

Try:

if ( $_SESSION['login']['a_moderator'] ) {
  // do this
}

It is a boolean, not a string.