CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string
Good evening!
I am calling this stored function -
CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROM words_games
WHERE gid = in_gid;
$func$ LANGUAGE sql STABLE;
by a PHP script -
$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values = $row['values'];
}
And then print the variable type -
error_log('letters: ' . gettype($letters));
and it is a "string" (instead of inspected array) with the following
content:
[02-Mar-2017 21:28:33 Europe/Berlin] letters: string
[02-Mar-2017 21:28:33 Europe/Berlin] letters:
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,К,А,Й,Т,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Ь,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Е,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
How to handle this please?
I was hoping to fetch a PHP array and process it with "foreach" in my
PHP-script.
Thank you
Alex
On 03/02/2017 12:45 PM, Alexander Farber wrote:
Good evening!
I am calling this stored function -
CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROM words_games
WHERE gid = in_gid;$func$ LANGUAGE sql STABLE;
I am not seeing where you assign the results of your SELECT to the OUT
parameters in the RETURNS TABLE. Nor do I see anything that turns the
results into an array for those OUT parameters of array type.
by a PHP script -
$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values = $row['values'];
}And then print the variable type -
error_log('letters: ' . gettype($letters));
and it is a "string" (instead of inspected array) with the following
content:[02-Mar-2017 21:28:33 Europe/Berlin] letters: string
[02-Mar-2017 21:28:33 Europe/Berlin] letters:
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,К,А,Й,Т,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Ь,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Е,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}How to handle this please?
I was hoping to fetch a PHP array and process it with "foreach" in my
PHP-script.Thank you
Alex
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Mar 2, 2017 at 2:12 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
On 03/02/2017 12:45 PM, Alexander Farber wrote:
Good evening!
I am calling this stored function -
CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROM words_games
WHERE gid = in_gid;$func$ LANGUAGE sql STABLE;
I am not seeing where you assign the results of your SELECT to the OUT
parameters in the RETURNS TABLE. Nor do I see anything that turns the
results into an array for those OUT parameters of array type.
The function isn't the problem here - its in SQL language. Explicit
assignment is needed in pl/pgsql.
by a PHP script -
$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values = $row['values'];
}And then print the variable type -
error_log('letters: ' . gettype($letters));
and it is a "string" (instead of inspected array) with the following
content:
Proper usage of PHP PDO is the concern - and outside my personal experience.
David J.
Adrian, but the stored function works, I am just not happy that the results
are casted to strings by PHP... and wonder hpw to fix or workaround this.
Show quoted text
Yes, as David notices it is SQL function and not pg/PlSQL (you have
probably misread this).
I wonder what to do with the string in PHP, how to convert it to an
(2-dimensional) array.
Alexander Farber schrieb am 02.03.2017 um 21:45:
I am calling this stored function -
CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROM words_games
WHERE gid = in_gid;$func$ LANGUAGE sql STABLE;
by a PHP script -
$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values = $row['values'];
}
What exactly is the query in SQL_GET_BOARD?
How exactly are you calling that function?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 03/02/2017 01:30 PM, Alexander Farber wrote:
Adrian, but the stored function works, I am just not happy that the
results are casted to strings by PHP... and wonder hpw to fix or
workaround this.
So what is the result when you run the function in psql?
Also if I am following correctly should this:
$bid = $row['bid'];
$letters = $row['letters'];
$values = $row['values']
not be?:
$bid = $row['out_bid'];
$letters = $row['out_letters'];
$values = $row['out_values']
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Alex,
On Thu, 2017-03-02 at 21:45 +0100, Alexander Farber wrote:
Good evening!
I am calling this stored function -
CREATE OR REPLACE FUNCTION words_get_board(
in_gid integer
) RETURNS TABLE (
out_bid integer,
out_letters varchar[15][15],
out_values integer[15][15]
) AS
$func$
SELECT
bid,
letters,
values
FROM words_games
WHERE gid = in_gid;$func$ LANGUAGE sql STABLE;
by a PHP script -
$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values = $row['values'];
}
Don't use PDO but assuming you want to loop through a result set,
shouldn't this line:-
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
be
while ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
Also you need to test if the result set is empty or not.
I don't have an "easy" way to convert Postgres arrays into PHP style
arrays. What I ended up doing was to use the array_to_string function
in the select statement using the underscore character as the delimiter
in the string, and then converting the string to a PHP array via an
explode. However, I've only had to do this with single level arrays.
Don't know if this helps as you haven't supplied the table definition
of words_games.
Cheers,
Rob
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Good morning,
it looks that I failed to provide sufficient information in the first mail,
sorry.
Here again my problem - here is my PHP script:
const SQL_GET_BOARD = '
SELECT
out_bid AS bid,
out_letters AS letters,
out_values AS values
FROM words_get_board(?)
';
try {
$dbh = pgsqlConnect();
$sth = $dbh->prepare(SQL_GET_BOARD);
$sth->execute(array($gid));
if ($row = $sth->fetch(PDO::FETCH_ASSOC)) {
$bid = $row['bid'];
$letters = $row['letters'];
$values = $row['values'];
}
} catch (PDOException $ex) {
exit('Database problem: ' . $ex);
}
function pgsqlConnect() {
$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_CASE => PDO::CASE_LOWER);
return new PDO(sprintf('pgsql:host=%s;port=%u;dbname=%s', DBHOST,
DBPORT, DBNAME), DBUSER, DBPASS, $options);
}
Here I run it at psql 9.6.2:
words=> SELECT
out_bid AS bid,
out_letters AS letters,
out_values AS values
FROM words_get_board(1)
;
bid |
letters
|
values
-----+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------
1 |
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NUL
L,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,К,О,Р,О,Б,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,Р,Е,Я,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,У,
NULL,NULL,П,Э,Р,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,С,П,И,Л,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,О,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL,М,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Б,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,В,А,Н,Н,А,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL}} |
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NUL
L},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,2,1,2,1,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,2,1,3,NULL,NULL,NULL,NULL,NULL,NULL},{
NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,NULL,2,10,2,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,2,2,1,2,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,NULL,NULL,NULL,NULL,NULL},{NULL,N
ULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,2,1,1,1,1,NULL,NULL},{NULL,NU
LL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
(1 row)
And I get similar output when running the PHP script:
[02-Mar-2017 21:28:33 Europe/Berlin] letters:
{{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,К,А,Й,Т,NULL,N
ULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,П,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Ь,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,Е,NULL,
NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL},{NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL}}
But my problem is that the type of the PHP variable $letters is "string"
and not a 2-dimensional array:
[02-Mar-2017 21:40:31 Europe/Berlin] PHP Warning: Invalid argument
supplied for foreach() in /var/www/html/slova.de/words/board.php on line 64
And my question is how to workaround it?
I am thinking of converting the PostgreSQL array to a JSON-encoded string
in the stored function, would that work? Or is there a better way?
Regards
Alex
And here is the table definition:
words=> \d words_games;
Table "public.words_games"
Column | Type | Modifiers
----------+--------------------------+-----------------------------------------------------------
gid | integer | not null default
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | not null
finished | timestamp with time zone |
player1 | integer | not null
player2 | integer |
played1 | timestamp with time zone |
played2 | timestamp with time zone |
score1 | integer | not null
score2 | integer | not null
hand1 | character varying[] | not null
hand2 | character varying[] | not null
pile | character varying[] | not null
letters | character varying[] | not null
values | integer[] | not null
bid | integer | not null
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
Check constraints:
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_notes" CONSTRAINT "words_notes_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE