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 INSERT
s, 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
PHP Sort($Array) Returning 1 Instead of Sorted Array
How to Upload a File Using Jquery's $.Ajax Function with JSON and PHP
How to Get an Option Previously Set with Curl_Setopt()
Download Multiple Files in One Http Request
PHP Mail() Works from Command Line But Not Apache
Pdo Cannot Execute Queries While Other Unbuffered Queries Are Active
Curl Import Character Encoding Problem
Differencebetween $_Files["File"]["Type"] and End(Explode(".", $_Files["File"]["Name"]))
Php, Why Do You Escape My Quotes
Dates Not Casting After Upgrading to Laravel 7
How to Check If File Is Ascii or Binary in PHP
Read the Longest String from an Array in PHP 5.3
Get Number of Weekdays in a Given Month
Loop Through Wordpress Posts, and Wrap Each X Post in a Div
Is There a Call_User_Func() Equivalent to Create a New Class Instance
PHP - How to Get Shell Errors Echoed Out to Screen