PHP Array to Postgres Array

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
}

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);

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)

How to pass a php array to a postgres function

UPDATE: I just noticed that you don't just need arrays, you need to use arrays of composite types. Ick. I've never needed to work with them, so I had to do a bit of checking.

It seems that the correct PostgreSQL syntax for an array of widgetallocationrequest would be:

'{"(8c2f3421-bac8-4cdd-87ce-45d55d152074,10.0000)","(6c2f7451-bac8-4cdd-87ce-45d55d152078,5.0000)"}'::widgetallocationrequest[]

See how each composite-type row is enclosed in "(col1,col2)" within the array {a,b,c} container?

Here's a PostgreSQL SQL example of how I created the value:

-- Create the array of composites from a VALUES() statement
--
SELECT array_agg(x::widgetallocationrequest)
FROM (VALUES
('8c2f3421-bac8-4cdd-87ce-45d55d152074',10.00),
('6c2f7451-bac8-4cdd-87ce-45d55d152078',5.00)
) x;

... and how I verified it was valid:

-- Unpack it back into a row-set of columns
SELECT * FROM unnest('{"(8c2f3421-bac8-4cdd-87ce-45d55d152074,10.0000)","(6c2f7451-bac8-4cdd-87ce-45d55d152078,5.0000)"}'::widgetallocationrequest[]);

Now, PHP's driver for Pg doesn't even support arrays, let alone arrays of composite types, so you're going to have to find someone else who wrote what you want or write it yourself. Writing a reliable parser will be "fun" and not a productive use of time.

Let's take another approach: produce a query that lets you call the function sanely by doing the conversion to a widgetallocationrequest[] inside PostgreSQL.

Here's a dummy function with the same arguments as your real one that we'll use as a call target:

CREATE OR REPLACE FUNCTION distributeWidgets(pid uuid, precipients widgetallocationrequest[]) RETURNS boolean AS $$
SELECT 't'::boolean;
$$ LANGUAGE 'sql';

You can see that it can be called with the array-of-composites syntax that's giving you so much trouble:

SELECT distributewidgets(null, '{"(8c2f3421-bac8-4cdd-87ce-45d55d152074,10.0000)","(6c2f7451-bac8-4cdd-87ce-45d55d152078,5.0000)"}');

... but ideally you want to avoid producing anything that horrible from PHP, and the driver is missing important features so it can't do it for you.

Instead, you can use a TEMPORARY table to produce the arguments, INSERT each argument row into the table with regular parameterized INSERTs, and then execute a query to execute the function.

BEGIN;

CREATE TEMPORARY TABLE dw_args ( id uuid, amount numeric(13,4) );

-- Use proper parameterized INSERTs from PHP, this is just an example
INSERT INTO dw_args(id,amount) VALUES ('8c2f3421-bac8-4cdd-87ce-45d55d152074',10.00);
INSERT INTO dw_args(id,amount) VALUES ('6c2f7451-bac8-4cdd-87ce-45d55d152078',5.00);

SELECT distributewidgets(null, array_agg(ROW(x.*)::widgetallocationrequest))
FROM dw_args x;

DROP TABLE dw_args;

COMMIT;

WARNING: The following is vulnerable to SQL injection if not handled very carefully. Use the above temp table approach if at all possible. Don't be bobby's next victim; read the PHP docs on SQL injection.

If for some reason it's absolutely necessary to run it all in one statement, you can instead produce a query with a VALUES set from PHP and convert that into a widgetallocationrequest[] using a PostgreSQL query. I demonstrated it above, but here's how to combine it with a call to distributeWidgets(...):

SELECT distributewidgets(null, array_agg(x::widgetallocationrequest)) 
FROM (VALUES
('8c2f3421-bac8-4cdd-87ce-45d55d152074',10.00),
('6c2f7451-bac8-4cdd-87ce-45d55d152078',5.00)
) x;

That's something you can build pretty easily in PHP using string manipulation, so long as you're really careful about SQL injection.

Please use the temporary table approach if at all possible.

See also PHP array to postgres array

How to use PHP array in Postgres select

Strings needs single quotes as I know. Concat the elements of array like this:

$data = ['a','b','c','d'];
$x = "'" . implode("','", $data) . "'";
var_dump($x);

Result:

'a','b','c','d'

Make a php ARRAY with a PostgreSQL ARRAY, array_to_json(), json_build_array()

Thanks to @04FS, I didn't have to code in json. & I didn't modify my function UserManager::getList();.

Let me show you :

$list = UserManager::getList();

foreach ($list as $elt) {
echo '<p>' . $elt->getLogin() . '</p>';
echo '<p>' . $elt->getPassword() . '</p>';

$roles=$elt->getRoles();
if(isset($roles) && !empty($roles) && $roles!=="{}"){
$roles = trim($roles, "{}");
$roles = explode (",", $roles);
echo '<p>Membership of :</p>';
echo '<ul>';
foreach ($roles as $role) {
echo '<li>'.$role.'</li>';
}
echo '</ul>';
}

$schemas=$elt->getSchemas();
if(isset($schemas) && !empty($schemas) && $schemas!=="{}"){
$schemas = trim($schemas, "{}");
$schemas = explode (",", $schemas);
echo '<p>Use schemas :</p>';
echo '<ul>';
foreach ($schemas as $schema) {
echo '<li>'.$schema.'</li>';
}
echo '</ul>';
}

}

Use php array as parameter for Postgres IN clause

Take a look at the Aura.Sql documentation for quote(), here: https://github.com/auraphp/Aura.Sql/blob/3.x/docs/getting-started.md#array-quoting

The SQL IN clause requires a comma-delimited list of values. The Aura.Sql quote() method appears to generate that for you.

You should consider using perform method.

https://github.com/auraphp/Aura.Sql/blob/3.x/docs/getting-started.md#the-perform-method

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



Leave a reply



Submit