Displaying php mysqli query result with one to many relationship-CodePudding

Current:

Process	Skill
Process A	Skill_1
Process A	Skill_2
Process A	Skill_3
Process B	Skill_1
Process B	Skill_2

Expected Result:

Process	Skill
Process A	Skill_1, Skill_2, Skill_3
Process B	Skill_1, Skill_2

Sample SQL:

SELECT p.pid,
 p.process,
 p.product_id,
 p.sr_id,
 sr.pid,
 sr.process_id,
 sr.skill_req,
 sr.availability
 FROM p
 JOIN sr
 ON  p.pid= sr.pid
 WHERE p.product_id= '20'

Need help how to rearrange my one to many relationship table. Tried using array "$array= array($row['skill_req']) " but does not recognize the skill_req variable.

CodePudding user response：

You're looking for GROUP BY and GROUP_CONCAT

For your request, start with something like this and see where you can go from there:

SELECT p.pid,
 p.process,
 GROUP_CONCAT(sr.skill_req),
 FROM p
 JOIN sr
 ON  p.pid= sr.pid
 WHERE p.product_id= '20'
 GROUP BY p.process

Note that SQL will always give you a 2-dimensional array, never more, never less. This means that the cells that you visualize as a result of a query cannot contain an array. At best, you're going to have comma-separated values, which you can turn into an array on the PHP side.