将 PostgreSQL 数组转换为 PHP 数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3068683/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert PostgreSQL array to PHP array
提问by EarthMind
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.
我在 PHP 中读取 Postgresql 数组时遇到问题。我尝试过explode()，但这会破坏字符串中包含逗号和str_getcsv() 的数组，但这也不好，因为PostgreSQL 没有引用日语字符串。
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'], '{}') ));
回答by michaelbn
If you have PostgreSQL 9.2 you can do something like this:
如果你有 PostgreSQL 9.2 你可以做这样的事情：
SELECT array_to_json(pg_array_result) AS new_name FROM tbl1;
The result will return the array as JSON
结果将数组作为 JSON 返回
Then on the php side issue:
然后在php方面的问题：
$array = json_decode($returned_field);
You can also convert back. Here are the JSON functionspage
您也可以转换回来。这是JSON 函数页面
回答by dmikam
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
PS：我知道这是一篇很老的帖子，但是我找不到 postgresql pre 9.2 的任何解决方案
回答by user2829228
Reliable function to parse PostgreSQL (one-dimensional) array literal into PHP array, using regular expressions:
使用正则表达式将 PostgreSQL（一维）数组文字解析为 PHP 数组的可靠函数：
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 \"\\ ,{0\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
// )
回答by Nicolai
If you can foresee what kind text data you can expect in this field, you can use array_to_stringfunction. It's available in 9.1
如果您可以预见在该字段中可以期望什么样的文本数据，您可以使用array_to_string函数。它在 9.1 中可用
E.g. I exactly know that my array field labeswill never have symbol '\n'. So I convert array labesinto string using function array_to_string
例如，我确切地知道我的数组字段labes永远不会有符号'\n'。所以我labes使用函数将数组转换为字符串array_to_string
SELECT
...
array_to_string( labels, chr(10) ) as labes
FROM
...
Now I can split this string using PHP function explode:
现在我可以使用 PHP 函数分割这个字符串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:
PHP：
$phpLabels = explode( $pgLabes, '<--###DELIMITER###-->' );
回答by user3733839
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" }
我尝试了 array_to_json 答案，但不幸的是这会导致未知的函数错误。在 postgres 9.2 数据库上使用 dbal 查询构建器->addSelect('array_agg(a.name) as account_name')，我得到了类似的字符串{ "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.
因此，如果有引号，我会将字符串设为有效的 json 字符串，然后使用内置的 parse json 函数。否则我使用爆炸。
$data = str_replace(array("\r\n", "\r", "\n"), "", trim($postgresArray,'{}'));
if (strpos($data, '"') === 0) {
$data = '[' . $data . ']';
$result = json_decode($data);
} else {
$result = explode(',', $data);
}
}
回答by koyae
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.
如果您可以控制访问数据库的查询，为什么不使用unnest()行而不是 Postgres 数组来获取结果呢？从那里，您可以本机获得一个 PHP 数组。
$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.
这避免了通过尝试自己转换数组的字符串表示而招致的开销和维护问题。
回答by TigerTiger
I can see you are using explode(',', trim($pgArray, '{}'));
我可以看到你正在使用 explode(',', trim($pgArray, '{}'));
But explodeis used to Split a string by string (and you are supplying it an array!!). something like ..
但是explode用于按字符串拆分字符串（并且您正在为其提供一个数组！！）。就像是 ..
$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)
或者不确定您是否可以指定逗号以外的分隔符？array_to_string（anyarray，文本）
