Re: 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(dot)klaver(at)aklaver(dot)com

On Thu, Mar 2, 2017 at 2:12 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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.

>
>

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?

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(dot)klaver(at)aklaver(dot)com

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

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