Fetching mutiple associated rows from mysql table

Question

I have a tables called goods, that looks like this.

id |  name   |   type       |    
1  |  honda  |   car        |  
2  |  bianci |   bike       |  
3  |  ferari |   car        |  
4  |  hurley |   motor bike |  
4  |  bar    |   motor bike |

I am trying to get an associative array from this table, whereby the index of the array ought to be the type and the value to be the name. The final result should look something like this.

array("car"=>"honda", "bike"=>"bianci", "car"=>"ferrari", "motor bike"=>"hurley");

I tried SELECT name FROM goods AS type WHERE type IN ('car', 'bike', 'motor bike') but still gives the result indexing type for the array.

The `type` in this instance is a column heading. Although it is possible to change the column heading label, it is not possible, as far as I am aware, to have a dynamic column heading label. You are able to achieve this outside SQL in php code. — Kami, Mar 31 '14 at 13:32
@Kami I did achieve is using foreach loop in php, but I was hopping there might be a way to do so in mysql. — iOi, Mar 31 '14 at 13:34
@mituw16 Added little clarification. This is not an error related question — iOi, Mar 31 '14 at 13:35
It is not possible using simple sql - perhaps something like a pivot table - http://stackoverflow.com/questions/2554826/dynamic-sql-to-generate-column-names? But that is likely to make sql more complicated then it needs to be. — Kami, Mar 31 '14 at 13:35
@Kami It seems very complicated. I've found a way to do this in PHP using loops, only this time with less code :). I will just leave this question open to see if someone comes up with something — iOi, Mar 31 '14 at 13:37
@Aioros It is actually possible, I have done it before. I just needed to refactor my project, and stopped by to see if there was a simpler way of doing this in mysql — iOi, Mar 31 '14 at 13:39
can you write/show you wished results ?edit your question and add it — echo_Me, Mar 31 '14 at 13:39
Take a look at PDO's [FETCH_GROUP](http://www.php.net/manual/en/pdostatement.fetchall.php). — Marcus Adams, Mar 31 '14 at 13:41
@MarcusAdams I know that one, unfortunately, I have PDO default to `PDO::FETCH_ASSOC`. I thought mysql was powerful enough to handle this by its own. — iOi, Mar 31 '14 at 13:44

Prix · Accepted Answer · 2014-03-31T14:36:23.673

Your query should look like this:

  SELECT GROUP_CONCAT(`name`) AS `brand`,
         `type` 
    FROM goods
   WHERE `type` IN ('car', 'bike', 'motor bike')
GROUP BY `type`

Where the result of the above query would be something like:

name            |   type
-------------------------------
honda, ferari   |   car
bianci          |   bike
hurley, bar     |   motor bike

And on your PHP would be something like this:

$result = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $result[$row['type']] = $row['brand'];
}
print_r($result);

Since you cannot have repeated keys on an array, by using GROUP BY to group the type's and GROUP_CONCAT to group the names into a single string we can have a result close to what you want:

array("car" => "honda, ferrari",
      "bike" => "bianci",
      "motor bike" => "hurley, bar"
     );

Another way to do this would be:

  SELECT `name`,
         `type` 
    FROM goods
   WHERE `type` IN ('car', 'bike', 'motor bike')

And on your PHP would be something like this:

$result = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
{
    $result[$row['type']][] = $row['name'];
}
print_r($result);

With this method you would have type as the key and an array as the values with all names that you can easily read with a foreach or any other loop:

array("car" => array("honda", "ferrari"),
      "bike" => array("bianci"),
      "motor bike" => array("hurley", "bar")
     );

So far this is the best approach. Only drawback is that I have to use `explode()` to get the car names for example. Likely there aren't any cars/bikes that have comma's in their name — iOi, Mar 31 '14 at 14:25
@iOi you can change the separator to something not a comma **but depending on how you will use the result of the MySQL query there might be better ways to read it so unless you can provide more information on how the result will be used I guess this is the closest to what you want.** — Prix, Mar 31 '14 at 14:31
@iOi I have also added a different way at the bottom that you would perhaps prefer since u wont have to explode the result. — Prix, Mar 31 '14 at 14:36
I think your second answer is better. I can't believe I was so close to that lol. Anyway, thanks. — iOi, Mar 31 '14 at 14:47

Rahul · Answer 2 · 2014-03-31T14:25:49.970

1

Based on your ask if you want to achieve through SQL then this what you can do, may be there can be a better way. So, in your PHP code you will somehow have to handle those null/empty values. No idea on PHP.

select 
isnull(car,'') as car,
isnull(bike,'') as bike,
isnull([motor bike],'') as 'motor_bike' 
from
(
SELECT 
case when name in ('honda','ferari') then name end as car, 
case when name = 'bianci' then name end as bike,
case when name in ('bar','hurley') then name end as 'motor bike'
FROM goods 
) tab

(OR) direct way as per comment

SELECT 
case when type = 'car' then name end as car, 
case when type = 'bike' then name end as bike,
case when type = 'motor bike' then name end as 'motor bike'
FROM goods

Which will result in

enter image description here

edited Mar 31 '14 at 14:25

answered Mar 31 '14 at 14:06

Rahul

76,197
13
71
125

Can't you do it directly instead? `CASE WHEN type = 'car' THEN name END AS car,`? – Prix Mar 31 '14 at 14:21
Yes, sure we can. I went by another way. As it being said, there exist more than one way to solve a problem. – Rahul Mar 31 '14 at 14:23
This approach is good but not convenient. It needs the names to be explicitly written inside the statement. – iOi Mar 31 '14 at 14:23
In the example I gave above, specially the part: `IN ('car', 'bike', 'motor bike')` is actually a variable. That is: `IN ('$implodeGoods')`. I eased it up for example purpose here. So, now if I used your answer it would be a little complicated as the names are scatted apart in lines as opposed to Prix's answer, where they are joined together. So, replacing them with one variable i.e `$implodeGoods` would be far more convenient imho. Thanks for the effort @ all. – iOi Mar 31 '14 at 14:33

Fetching mutiple associated rows from mysql table

2 Answers2