• 10
name

A PHP Error was encountered

Severity: Notice

Message: Undefined index: userid

Filename: views/question.php

Line Number: 191

Backtrace:

File: /home/prodcxja/public_html/questions/application/views/question.php
Line: 191
Function: _error_handler

File: /home/prodcxja/public_html/questions/application/controllers/Questions.php
Line: 433
Function: view

File: /home/prodcxja/public_html/questions/index.php
Line: 315
Function: require_once

name Punditsdkoslkdosdkoskdo

Convert PostgreSQL array to PHP array

I have trouble reading Postgresql arrays in PHP. I have tried explode(), but this breaks arrays containing commas in strings, and str_getcsv() but it's also no good as PostgreSQL doesn't quote the Japanese strings.

Not working:

explode(',', trim($pgArray['key'], '{}'));
str_getcsv( trim($pgArray['key'], '{}') );

Example:

// print_r() on PostgreSQL returned data: Array ( [strings] => {???, "some string without a comma", "a string, with a comma"} )

// Output: Array ( [0] => ??? [1] => "some string without a comma" [2] => "a string [3] => with a comma" ) 
explode(',', trim($pgArray['strings'], '{}'));

// Output: Array ( [0] => [1] => some string without a comma [2] => a string, with a comma ) 
print_r(str_getcsv( trim($pgArray['strings'], '{}') ));

If you have PostgreSQL 9.2 you can do something like this:

SELECT array_to_json(pg_array_result) AS new_name FROM tbl1;

The result will return the array as JSON

Then on the php side issue:

$array = json_decode($returned_field);

You can also convert back. Here are the JSON functions page

  • 80
Reply Report

As neither of these solutions work with multidimentional arrays, so I offer here my recursive solution that works with arrays of any complexity:

function pg_array_parse($s, $start = 0, &$end = null)
{
    if (empty($s) || $s[0] != '{') return null;
    $return = array();
    $string = false;
    $quote='';
    $len = strlen($s);
    $v = '';
    for ($i = $start + 1; $i < $len; $i++) {
        $ch = $s[$i];

        if (!$string && $ch == '}') {
            if ($v !== '' || !empty($return)) {
                $return[] = $v;
            }
            $end = $i;
            break;
        } elseif (!$string && $ch == '{') {
            $v = pg_array_parse($s, $i, $i);
        } elseif (!$string && $ch == ','){
            $return[] = $v;
            $v = '';
        } elseif (!$string && ($ch == '"' || $ch == "'")) {
            $string = true;
            $quote = $ch;
        } elseif ($string && $ch == $quote && $s[$i - 1] == "\\") {
            $v = substr($v, 0, -1) . $ch;
        } elseif ($string && $ch == $quote && $s[$i - 1] != "\\") {
            $string = false;
        } else {
            $v .= $ch;
        }
    }

    return $return;
}

I haven't tested it too much, but looks like it works. Here you have my tests with results:

var_export(pg_array_parse('{1,2,3,4,5}'));echo "\n";
/*
array (
  0 => '1',
  1 => '2',
  2 => '3',
  3 => '4',
  4 => '5',
)
*/
var_export(pg_array_parse('{{1,2},{3,4},{5}}'));echo "\n";
/*
array (
  0 => 
  array (
    0 => '1',
    1 => '2',
  ),
  1 => 
  array (
    0 => '3',
    1 => '4',
  ),
  2 => 
  array (
    0 => '5',
  ),
)
*/
var_export(pg_array_parse('{dfasdf,"qw,,e{q\"we",\'qrer\'}'));echo "\n";
/*
array (
  0 => 'dfasdf',
  1 => 'qw,,e{q"we',
  2 => 'qrer',
)
*/
var_export(pg_array_parse('{,}'));echo "\n";
/*
array (
  0 => '',
  1 => '',
)
*/
var_export(pg_array_parse('{}'));echo "\n";
/*
array (
)
*/
var_export(pg_array_parse(null));echo "\n";
// NULL
var_export(pg_array_parse(''));echo "\n";
// NULL

P.S.: I know this is a very old post, but I couldn't find any solution for postgresql pre 9.2

  • 12
Reply Report

Reliable function to parse PostgreSQL (one-dimensional) array literal into PHP array, using regular expressions:

function pg_array_parse($literal)
{
    if ($literal == '') return;
    preg_match_all('/(?<=^\{|,)(([^,"{]*)|\s*"((?:[^"\\\\]|\\\\(?:.|[0-9]+|x[0-9a-f]+))*)"\s*)(,|(?<!^\{)(?=\}$))/i', $literal, $matches, PREG_SET_ORDER);
    $values = [];
    foreach ($matches as $match) {
        $values[] = $match[3] != '' ? stripcslashes($match[3]) : (strtolower($match[2]) == 'null' ? null : $match[2]);
    }
    return $values;
}

print_r(pg_array_parse('{blah,blah blah,123,,"blah \\"\\\\ ,{\100\x40\t\da?\?",NULL}'));
// Array
// (
//     [0] => blah
//     [1] => blah blah
//     [2] => 123
//     [3] =>
//     [4] => blah "\ ,{@@ da??
//     [5] =>
// )

var_dump(pg_array_parse('{,}'));
// array(2) {
//   [0] =>
//   string(0) ""
//   [1] =>
//   string(0) ""
// }

print_r(pg_array_parse('{}'));
var_dump(pg_array_parse(null));
var_dump(pg_array_parse(''));
// Array
// (
// )
// NULL
// NULL

print_r(pg_array_parse('{???, "some string without a comma", "a string, with a comma"}'));
// Array
// (
//     [0] => ???
//     [1] => some string without a comma
//     [2] => a string, with a comma
// )
  • 4
Reply Report

If you can foresee what kind text data you can expect in this field, you can use array_to_string function. It's available in 9.1

E.g. I exactly know that my array field labes will never have symbol '\n'. So I convert array labes into string using function array_to_string

SELECT 
  ...
  array_to_string( labels, chr(10) ) as labes
FROM
  ...

Now I can split this string using PHP function explode:

$phpLabels = explode( $pgLabes, "\n" );

You can use any sequence of characters to separate elements of array.

SQL:

SELECT
  array_to_string( labels, '<--###DELIMITER###-->' ) as labes

PHP:

$phpLabels = explode( $pgLabes, '<--###DELIMITER###-->' );
  • 1
Reply Report

I tried the array_to_json answer, but unfortunalety this results in an unknown function error. Using the dbal query builder on a postgres 9.2 database with something like ->addSelect('array_agg(a.name) as account_name'), I got as result a string like { "name 1", "name 2", "name 3" }

There are only quotes around the array parts if they contain special characters like whitespace or punctuation.

So if there are quotes, I make the string a valid json string and then use the build-in parse json function. Otherwise I use explode.

$data = str_replace(array("\r\n", "\r", "\n"), "", trim($postgresArray,'{}'));
if (strpos($data, '"') === 0) {
    $data = '[' . $data . ']';
    $result = json_decode($data);
} else {
    $result = explode(',', $data);

}

  • 1
Reply Report

If you have control of the query that's hitting the database, why don't you just use unnest() to get the results as rows instead of Postgres-arrays? From there, you can natively get a PHP-array.

$result = pg_query('SELECT unnest(myArrayColumn) FROM someTable;');
if ( $result === false ) {
    throw new Exception("Something went wrong.");
}
$array = pg_fetch_all($result);

This sidesteps the overhead and maintenance-issues you'd incur by trying to convert the array's string-representation yourself.

  • 1
Reply Report

I can see you are using explode(',', trim($pgArray, '{}'));

But explode is used to Split a string by string (and you are supplying it an array!!). something like ..

$string = "A string, with, commas";
$arr = explode(',', $string);

What are you trying to do with array? if you want to concatenate have a look on implode

OR not sure if it is possible for you to specify the delimiter other than a comma? array_to_string(anyarray, text)

  • 0
Reply Report
    • Sorry, the code wasn't very clear in my post. I've modified it so that second function argument is a string value and not an array. Note that PostgreSQL's array_to_string() isn't a solution to this case because it removes NULL and empty values which makes it impossible for me to iterate over arrays and link values from one array to their associated values in another array.
      • 1
    • To avoid NULL problems, aproximate them to PHP "". Use array_to_string(anyarray, '","'), that is, SELECT '{"'|| array_to_string(anyarray, '","') || '"}' ... for PHP receive as string JSON $s and $newarray = json_decode($s);

Warm tip !!!

This article is reproduced from Stack Exchange / Stack Overflow, please click

Trending Tags

Related Questions