Обсуждение: [GENERAL] CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string

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

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.klaver@aklaver.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