Convert Postgresql Array to PHP Array

Convert PostgreSQL array to PHP array

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

Converting PHP array to PostgreSQL array and back

So, the main issue here is that json format uses Key-Value pairs, and the inferred indexes don't seem to work. Secondly, using json_decoded([string], TRUE) with the TRUE flag, gives you an associative array, instead of an object.

Try running all of this for a good look at what you have:

<?php 

//$libraries = array('0'=>'LIBRARY_01', '1'=>'LIBRARY_02', '2'=>'LIBRARY_03');    //this will not work

$libraries = array('one'=>'LIBRARY_01', 'two'=>'LIBRARY_02', 'three'=>'LIBRARY_03');
var_dump($libraries);
echo '<br>';
$encoded = json_encode($libraries);
echo $encoded;
echo '<br>';
$encoded = str_replace('[', '{', $encoded);
$encoded = str_replace(']', '}', $encoded);
echo 'String: '.$encoded;

echo '<br>';
echo '<br>';

$stdObj = json_decode($encoded);
echo 'var_dump: ';
var_dump($stdObj);
echo '<br>';
echo 'json_encode: '.json_encode($stdObj);
echo '<br>';
foreach($stdObj as $element){
    echo $element;
    echo '<br>';
}
echo '<br>';

$array = json_decode($encoded, TRUE);
echo 'var_dump associative: ';
var_dump($array);
echo '<br>';
echo 'array[one]: '.$array[one];
echo '<br>';

echo '<br>';
echo '<br>';

echo 'json_encode: '.json_encode($array);
echo '<br>';

?>

However, if you're just using json_encode() as a way to turn a one-dimensional array into a simple string list of comma-separated values, then you might use this quick and dirty method:

$libraries = array('LIBRARY_01', 'LIBRARY_02', 'LIBRARY_03');
$encoded = json_encode($libraries);
$encoded = str_replace('[', '{', $encoded);
$encoded = str_replace(']', '}', $encoded);

//in and out of database

$decodedStr = str_replace('{"', '', $encoded);
$decodedStr = str_replace('"}', '', $decodedStr);

$delimiter = '","';
$libraries = explode($delimiter, $decodedStr);
var_dump($libraries);

Point is, json_decode() is not the exact mirror reversal of json_encode().

Using implode() and explode() are the better choice for your situation:

$libraries = array('LIBRARY_01', 'LIBRARY_02', 'LIBRARY_03');
$commaSeparatedString = implode(',', $libraries);
$string = '{'.$commaSeparatedString.'}';

//in and out of database with a comma-separated list in curly brackets

$stringFromDatabase = str_replace('{"', '', $stringFromDatabase);
$stringFromDatabase = str_replace('"}', '', $stringFromDatabase);
$array = explode(',', $stringFromDatabase);

PHP array to postgres array

Here's a simple function for converting a PHP array to PG array.

function to_pg_array($set) {
    settype($set, 'array'); // can be called with a scalar or array
    $result = array();
    foreach ($set as $t) {
        if (is_array($t)) {
            $result[] = to_pg_array($t);
        } else {
            $t = str_replace('"', '\\"', $t); // escape double quote
            if (! is_numeric($t)) // quote only non-numeric values
                $t = '"' . $t . '"';
            $result[] = $t;
        }
    }
    return '{' . implode(",", $result) . '}'; // format
}

how to convert array from postgres db to array variable in laravel?

Take a look here.

You can simplify this code if your postgres query returns an array "[8,11]" instead of a "sort-of" JSON "{8,11}"

<?php
$values = array_column($your_data, 'order_itemset');
$values = array_map(function ($v) {
    preg_match("/^\{(.+)\}$/", $v, $matches);
    if ($matches[1]) {
        return explode(',', $matches[1]);
    }
    return null;
}, $values);
print_r($values);

How to pass a php array to a postgres query with any condition

postgres array looks like '{list}' :

t=# select array['a','b','c'];
  array
---------
 {a,b,c}
(1 row)

so you need to get rid of double quotes, otherwise postgres understands literals as identities.

Eg with $pg_array_listO = str_replace('"', '\\"',to_pg_array($listO)) or smth smarter - sorry - I'm not good in php

additionally modify ANY(ARRAY[$3]) to ANY('$3'::text[]), cos array[] or '{}'::text[] would be accepted

update
based on

 //print_r($pg_array_list_organisms);
    //{"A","B","C"}

I expect this to work:

$result = pg_query_params($conn, "SELECT count(cp.id)
FROM cp, act, a, t
WHERE t.tid = a.tid AND
a.aid = act.aid AND
act.m = cp.m AND
t.n = $1 AND
act.st = $2 AND
t.o LIKE ANY($3)", array($t1, $a2, str_replace('"', '',to_pg_array($listO))));

mind I changed quotes and SQL and str_replace for $3 variable

a working example of this approach:

t=# select 'a' like any('{a,b,c}'::text[]);
 ?column?
----------
 t
(1 row)

Accessing psql-array directly in PHP

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

PostgreSQL: Arrays Data Type with PHP

Your creation statement has an error. This is correct:

CREATE TABLE sal_emp (
    a            text ARRAY,
    b            text ARRAY,
    c            text ARRAY
);

If you were to insert manually you would do:

insert into sal_emp (a, b, c) values 
('{"some text"}', '{"some more", "a bit more"}', '{"even more"}');

or

insert into sal_emp (a, b, c) values 
(array ['some text'], array['some more', 'a bit more'], array['even more']);

So... I can't test it now but it would be something like this:

$a = "array['" . implode("'],['", $a) . "']";
...
$a = array('a' => $a, 'b' => $b, 'c' => $c);
pg_insert($dbconn, 'table', $a);

---

Related Topics