Why a query on PostgreSQL (on PHP) return an array of strings instead of an assoc array?

Question

This is a very common operation but I do not know why I have an array of string back.

$res=$queryHelper->ExecuteQuery("
        SELECT (food.id,stores.name) 
        FROM food, stores 
        WHERE food.id_stores=stores.id");

QueryHelper Class

    public function ExecuteQuery($queryRec,$executeRec=array())
    {
        $sql = $this->db->prepare($queryRec);
        $sql->execute($executeRec);
        $this->res=$sql->fetchAll();
        return $this->res;
    }

TABLE FOOD

id| name  | id_stores  
0 | PASTA | 0  
1 | FISH  | 0

TABLE STORES

id  |name  
0   | MARKET0  
1   | MARKET1

$res is

Array ( [0] => Array ( [row] => (0,MARKET0) ) [1] => Array ( [row] => (1,MARKET0) ) )

but I expect

Array ( [0] => Array ( [0] => (0), [1] => ["MARKET0"] ) [1] => Array ([0] => (1), [1] => ["MARKET0"] ) )

or something like this.
If there is only one table in the query all is fine.

mu is too short · Accepted Answer · 2015-05-01 21:45:06Z

The parentheses in your SELECT:

SELECT (food.id,stores.name)

are actually constructing a ROW type so the rows that come out of your query each have a single column which contains a ROW with two values. You can think of your query producing an array of rows and you're getting something that looks like this:

[
    [ [ food.id, stores.name ] ],
    ...
]

when you're expecting:

[
    [ food.id, stores.name ],
    ...
]

The solution is to toss out the parentheses:

SELECT food.id,stores.name
FROM food, stores 
WHERE food.id_stores=stores.id

This behavior might be a little strange but it is documented:

4.2.13. Row Constructors

A row constructor is an expression that builds a row value (also called a composite value) using values for its member fields. A row constructor consists of the key word ROW, a left parenthesis, zero or more expressions (separated by commas) for the row field values, and finally a right parenthesis. For example:
SELECT ROW(1,2.5,'this is a test');
The key word ROW is optional when there is more than one expression in the list.

Emphasis mine. You have two expressions in your list so (food.id, stores.name) and food.id, stores.name are different things even though (food.id) and food.id are equivalent (but row(food.id) would be different).

Collectives™ on Stack Overflow

Why a query on PostgreSQL (on PHP) return an array of strings instead of an assoc array?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related