Accessing psql-array directly in PHP

Question

I am querying a psql database through PHP to get an array of boolean values where indexes are userids, e.g. userconfirm bool[]. When I do something like:

$query  = "select userconfirm from calendar where id = '%s'";
$result = $this->db->query($query, 2);
var_dump($result['userconfirm']);

I get:

array(1) { [0]=> array(1) { ["userconfirm"]=> string(20) "[256:258]={t,NULL,t}" } }

Which is to say I get a string where the array should be. Ok, parsing this could probably be done, if I studied up on PHP, but this feels wrong. Surely there's a way to get the array directly?

Main question is: How do I get this array to PHP directly?

Secondary question: Is this a suitable format for keeping track of user attendance? Is there a better way?

A quick glance through the TOC of the documentation doesn't seem to bring up anything, so you may be stuck with parsing the string... However, an alternative method for recording user attendance might be (using made-up column names, adjust to fit whatever you're doing) with columns of user_id, date, and attended (the last being boolean). This form takes up more storage, but is easier to query. — user554546
– user554546, Commented Feb 7, 2012 at 0:29
@JackManey You mean create a new table? I've been toying around with that idea in my frustration. Perhaps that is the best solution, as my skills with complex queries is not the best. — TLP
– TLP, Commented Feb 7, 2012 at 0:36
@JackManey I think I will go with your idea. If you'll post it as an answer, I'll accept it. — TLP
– TLP, Commented Feb 7, 2012 at 4:46

Joshua Burns · Accepted Answer · 2013-03-05 14:57:29Z

PHP's native PostgreSQL driver does not support advanced data-types. In fact, it doesn't even properly support integers, booleans or anything else! (cite: As stated here under "Return Values", all values are returned as strings, NOT their correct data type: http://www.php.net/manual/en/function.pg-fetch-assoc.php)

To help battle this problem, you may want to check out PHPG, a PHP library designed to specifically transform all returned PostgreSQL values to native PHP data-types. Supports arrays for any data-type, Hstores, Geometric data-types, and more:

Numeric / Integers

If you're a numeric[] or integer[] data type which does NOT have the possibility of containing NULL values, then you can simply remove the leading and trailing curly brackets, and explode the string into an array using the comma delimiter: https://github.com/JDBurnZ/PHPG

<?php
$pg_intarr = '{1,2,3,4,5}';
$vals = substr($pg_intarr, 1, -1); // Remove curly brackets
$vals = explode(',',$vals); // Returns: array(1,2,3,4,5)

Strings

However, the problem becomes much more complex when dealing with string-based data types such as character[], character varying[] or text[] and here is why: If the value of an array contains a space or some other special character, then PostgreSQL returns that particular value encapsulated in double quotes. If the value is a single word with no special characters, then that value is returned with no quotes whatsoever.

Here is an example character varying[] value returned from PostgreSQL:

{val1,"val 2",val3,"val-4","val,4"}

The challenge here is:

Can't explicitly explode on "," because not all strings are double-quoted.
Can't explicitly explode on , because a value may contain a comma such as val,4

The only method I was able to derive after MONTHS of encountering this problem over and over again was to take the string provided by PostgreSQL, and perform a subsequent query to UNNEST the array into a result set, which is then read and transformed into a native PHP array:

$grab_vals = pg_query("SELECT UNNEST('" . pg_escape_string('{val1,"val 2",val3,"val-4","val,4"}') . "') AS value");
$grab_vals = pg_fetch_all($grab_vals);
$array_vals = array();
foreach($grab_vals as $val) {
  $array_vals[] = $grab_vals['value'];
}

Booleans

In regards to boolean[] values, you could most likely take the same approach as suggested for integers. However because you also anticipate NULL values, we have the added task of mapping the string value NULL to PHP's native NULL data-type:

function pgBool2Php($string) {
  if($string == 't') {
    return True;
  } else if($string == 'f') {
    return False;
  } else if($string == 'NULL') {
    return Null;
  } else {
    raise new Exception('Mal-formed PostgreSQL Boolean encountered. Expecting value of "t", "f" or "NULL", encountered "' . $string . '"');
  }
}

$pg_intarr = '{t,NULL,t,f}';
$vals = substr($pg_intarr, 1, -1); // Remove curly brackets
$vals = explode(',',$vals); // Returns: array('t','NULL','t','f')

$vals = array_map('pgBool2Php', $vals); // Returns: array(True, Null, True, False)

Thank you for your answer. I decided to go with a solution based on simpler data types, though. I suspect this is somewhat of an XY-problem.
No problem. The goal wasn't exactly to change or "win" the accepted answer, but to emphasize the overall underlying issue in detail. For whoever comes across this thread, I hope my answer shines a brighter light on the underlying issue. Eg: The lack of properly supported PostgreSQL data-types within PHP's native PostgreSQL driver.

user554546 · Accepted Answer · 2012-02-07 05:42:19Z

1

(Re-posted from a comment by request)

Glancing at the documentation, it doesn't appear as though there's a way to directly interpolate a PostgreSQL array to an array in PHP. However, if the position of your vector represents a user id, you might be better off restructuring your data to columns with user_id and attended (the latter of which should be a boolean datatype).

answered Feb 7, 2012 at 5:42

user554546

Comments

David Chan · Accepted Answer · 2012-02-16 23:13:44Z

0

heres my pg_parse function on github

answered Feb 16, 2012 at 23:13

David Chan

7,5222 gold badges30 silver badges50 bronze badges

Collectives™ on Stack Overflow

Accessing psql-array directly in PHP

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related