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

Question

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.

Quassnoi · Accepted Answer · 2009-08-18 17:59:17Z

1

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.

answered Aug 18, 2009 at 17:59

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user151229 Over a year ago

Thanks, even if I can't use the PostgreSQL solution, my curiosity is satisfied. The comma-separated list however is dangerous, what if some cat is named "Oh, you're a cute kitty !!" ?

pilcrow Over a year ago

@hlanak, good follow-up question. You'll need to handle your own field delimiters (which you can specify in GROUP_CONCAT via SEPARATOR) and escaping. You might HEX() encode the fields before catenation, for example.

Paul Dixon · Accepted Answer · 2009-08-18 17:57:12Z

0

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

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

answered Aug 18, 2009 at 17:57

Paul Dixon

302k54 gold badges315 silver badges349 bronze badges

1 Comment

user151229 Over a year ago

Well, yes, but I wanted to know if I can get it directly from mysql.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related