Directly fetching a list from a column's data with MySQL

Question

Hello,

Let us suppose I have to deal with lots of grandmothers that have lots of cats. I have a table granny_cat :

granny_id | kitty_name
--------------------
1           Kizzy
1           Jimmy
1           Katty
2           Georges
2           Albert
3           Panther

and I want to retrieve a list of granny 1 cat's, i.e. to get something like (with php syntax)

array("Kizzy","Jimmy","Katty")

However, the query

SELECT kitty_name WHERE granny_id = 1

returns something like

array
(
   array('kitty_name' => "Kizzy"),
   array('kitty_name' => "Jimmy"),
   array('kitty_name' => "Katty")
)

what is quite logical, as I can fetch two, or more fields with a similar query. I can obviously map this array to get what I want, however, I wonder whether there is a (simple) way to get it directly from mysql, or not.

Thanks.

Answer 1

A wrapper library will typically handle this, e.g. ADODb has the GetCol() method:

$names=$db->GetCol('SELECT kitty_name WHERE granny_id = 1');

Answer 2

SELECT  GROUP_CONCAT(kitty_name) 
FROM    mytable
WHERE   granny_id = 1

will give you the comma-delimited list which you can explode into an array.

MySQL does not support native array datatype. In PostgreSQL you would be able to do the following:

SELECT  ARRAY
        (
        SELECT  kitty_name
        FROM    mytable
        WHERE   granny_id = 1
        ) AS kitties

, which would give you a native PHP array.