Query to display column data row group by data value in JSON from php

Question

I want a page that displaying data client. Here's my page that I want:

----------------------------------
name    | skill A    | skill B
----------------------------------
John    | PHP        | MySQL, Javascript
Doe     | Javascript | MySQL
Richard | PHP        | Javascript
----------------------------------

and here are my MySQL tables:

client table:

-----------------------------------------
id_client  | name_client  | email_client
-----------------------------------------
1          | John         | [email protected]
2          | Doe          | [email protected]
3          | Richard      | [email protected]
------------------------------------------

skill table

-----------------------------
id_skill   | name_skill
-----------------------------
1          | PHP
2          | MySQL
3          | Javascript
-----------------------------

client_skill

---------------------------------
skill   | client   | status
---------------------------------
1       | 1        | A
2       | 1        | B
3       | 2        | A
2       | 2        | B
1       | 3        | A
3       | 3        | B
---------------------------------

From these I want to diplaying data that join three tables group by id_client and each client has skill that group by value of skill client that A and B.

So I want to display client skill A and client Skill B in one row. What query should use and how to echoing in JSON so I can display it in angularjs?

check · Accepted Answer · 2015-03-03 02:37:25Z

1

this is your Query.

SELECT a.name_client,
MAX(CASE WHEN b.status = 'A' THEN c.name_skill END) 'SKILL A',
MAX(CASE WHEN b.status = 'B' THEN c.name_skill END) 'SKILL B'
FROM client a
LEFT JOIN client_skill b ON b.client = a.id_client
LEFT JOIN skill c ON c.id_skill = b.skill
GROUP BY a.id_client
ORDER BY id_client

answered Mar 3, 2015 at 2:37

check

5594 silver badges12 bronze badges

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

2 Comments

Agung Prabowo Over a year ago

thank you, GREAT answer. I have tried this but only display one data from skill A and one data from skill B, now I want to display more than one data from skill A and from skill B, for example, client 1 has Skill A : PHP, Javascript and Skill B: MySQL and separated by commas when echoing in angularjs.

check Over a year ago

use GROUP_CONCAT function, just replace MAX function with GROUP_CONCAT

Collectives™ on Stack Overflow

Query to display column data row group by data value in JSON from php

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related