Is it possible to return an array from a MySQL Table?

Question

I have 2 basic tables -- a main table containing master ID records and a related table containing a variable number of value references as shown below.

TABLE A: Main (one)

+----+-------------+
| ID | description |
+----+-------------+
| 1  | Arnold      |
+----+-------------+
| 3  | Baker       |
+----+-------------+
| 7  | Charlie     |
+----+-------------+

TABLE B: Related (Many)

+----+-------+
| ID | value |
+----+-------+
| 1  | 1     |
+----+-------+
| 1  | 2     |
+----+-------+
| 3  | 3     |
+----+-------+
| 3  | 2     |
+----+-------+
| 3  | 1     |
+----+-------+
| 7  | 2     |
+----+-------+

After joining Table A with Table B, is it possible to return the columns in such a way that the value column from Table B returns as an array?

I am looking to return something like the result set below (based on example above) where the 2nd column can be handled as an array value rather than a string value:

{
 [0] => 1
 [1] => array(1,2)
}
{
 [0] => 3
 [1] => array(3,2,1)
}
{
 [0] => 7
 [1] => array(2)
}

Here is my Query Statement to get the columns as string values:

SELECT tablea.*, tableb.value
FROM tablea
LEFT JOIN tableb ON tableb.ID = tablea.ID

From pure MySQL, no. If you're using a programming language (ie. PHP) then yes. — Kermit
– Kermit, Commented Feb 24, 2014 at 17:48
You'd have to "pack" the array into, say, a string (for storage), and unpack it after MySQL returns it. Something like "2 2 1" (use implode and explode in PHP). — Phil Perry
– Phil Perry, Commented Feb 24, 2014 at 17:52
So you mean the I will need to return the table b value as a string delimited by commas (for example) and then use PHP to do the conversion? That was what I was thinking would need to happen, but I wanted to ask if anyone had done it right through the query statement in mysql. Thanks for clarifying and answering @FreshPrinceOfSO — H. Ferrence
– H. Ferrence, Commented Feb 24, 2014 at 17:53

Mike Brant · Accepted Answer · 2014-02-24 18:10:31Z

1

Typically this is done by building up the multi-dimensional array when reading the result set with your application logic. Here is some pseudo-code:

array = [];
while (row = {some DB row retrieval method}) {
    array[row['id']][] = row['value'];
}

If PHP is your language:

$array = array();
while ($row = {some DB row retrieval method}) {
    $array[$row['id']][] = $row['value'];
}

edited Feb 24, 2014 at 18:10

answered Feb 24, 2014 at 18:05

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

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

10 Comments

H. Ferrence Over a year ago

Thanks MikeBrant. Now I just need to get the right join query to collect the many rows in Table B and convert them into a single column for my result set.

Kermit Over a year ago

@H.Ferrence You should break your question apart and update it.

Mike Brant Over a year ago

@H.Ferrence That is simply SELECT a.id, b.value FROM a INNER JOIN b on a.id = b.id.

H. Ferrence Over a year ago

Thanks @MikeBrant -- I'l give that a try.

H. Ferrence Over a year ago

I am not entirely sure what you mean @FreshPrinceOfSO

|

Collectives™ on Stack Overflow

Is it possible to return an array from a MySQL Table?

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related