CODEWITHSUNDEEP
phpmysqlarraysinner-joinassociative
caiovisk
caiovisk

Reputation: 3809

MySQL JOIN associative column with multiples row values in array

Table ORDEM

ord_id | name
1      -  'A'
2      -  'B'
3      -  'C'
4      -  'D'

Table ORCAMENTO

orc_id | type
  10   -  'AA'
  20   -  'BB'
  30   -  'CC'
  40   -  'DD'

Table ORDEM_ORCAMENTO

id | ord_id | orc_id  
1  -   1    -   10
2  -   1    -   20
3  -   2    -   30
4  -   2    -   40

MySQL Query:

SELECT o.*, oo.orc_id 
FROM ordem AS o
        INNER JOIN ordem_orcamento AS oo ON oo.ord_id = o.ord_id
ORDER BY o.ord_id ASC

Results Got:

1 - 'A' - 10
1 - 'A' - 20
2 - 'B' - 30
2 - 'B' - 40

Results Needed:

1 - 'A' - 10,20
2 - 'B' - 20,30

I want to put all id's from ORCAMENTO table that belongs to ORDEM table into an array...

I've tried many ways to figure it out but I haven't success...

Upvotes: 1

Views: 1160

Answers (2)

tommy
tommy

Reputation: 66

You can build an array with php too, by looping the result

$data = array();
foreach ($result as $res) {
    $data[$res->ord_id][] = $res->orc_id
}

//output: foreach ($data as $orderitem) echo implode(",",$orderitem);

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32392

GROUP_CONCAT allows you to select a group into a comma separated string

select o.ord_id, o.name, group_concat(oo.orc_id)
from ordem o
join ordem_orcamento oo on o.ord_id = oo.ord_id
group by o.ord_id, o.name
order by o.ord_id asc

Upvotes: 3

Related Questions