php mysql joining tables with concat

Question

Can anyone help me with this?
I'm querying from 2 tables, country_table and language_table linked by country_id.

country_table:

country_id    continent      country  
 100          asia            china
 101          asia            japan
 102          europe          UK
 103          europe          germany

language_table:

country_id    language_id     language
 100             01           mandarin  
 100             02           cantonese  
 102             03           english  
 102             04           french
 102             05           welsh

What I want to achieved is to display all the countries with or without the language. If it has language, it should be concatenated just like the sample output below.

continent    country    language  
asia          china      mandarin, cantonese  
asia          japan      ----
europe        UK         english, french, welsh
europe        germany    ----

what i did was i inserted another query(for the language) inside the while loop query for country list, i know its a bad idea, im seeking help in order to correct it. — swordfish
– swordfish, Commented Nov 29, 2012 at 23:30

Taryn · Accepted Answer · 2012-11-29 23:31:08Z

4

You can use the GROUP_CONCAT() function to do the following:

select c.continent,
  c.country,
  group_concat(case 
               when l.language is null 
               then '----' else l.language end order by l.language) language
from country_table c
left join language_table l
  on c.country_id = l.country_id
group by c.continent, c.country

See SQL Fiddle with Demo

answered Nov 29, 2012 at 23:31

Taryn

249k57 gold badges375 silver badges409 bronze badges

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

4 Comments

swordfish Over a year ago

hi, bluefeet, your answer is working, just a followup question, what would be the selector for the language? sample, $row['']?

Taryn Over a year ago

I am not familiar with that syntax. You might want to post another question to get other help.

swordfish Over a year ago

i mean, how do i echo the row for the language field?

Madbreaks Over a year ago

@swordfish $row['language']

bikedorkseattle · Accepted Answer · 2012-11-29 23:30:03Z

0

You need to do something like this.

SELECT ct.continent, ct.country, GROUP_CONCAT(lt.language)
FROM country_table ct
LEFT JOIN language_tabel lt USING(country_id)
GROUP BY ct.country

answered Nov 29, 2012 at 23:30

bikedorkseattle

9913 gold badges10 silver badges25 bronze badges

Collectives™ on Stack Overflow

php mysql joining tables with concat

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related