Brad Brad - 1 year ago 60
MySQL Question

PHP Loop with Multiple Nested SQL Query

I have two tables and I wish to display all the contents but grouping together the contents by a certain field - the job_id field:

The two tables:

  • job_experience

job_id (Primary Key)



  • job_skills

skill_id (Primary Key)



This should produce by matching both tables job_ids:

Job Name 1

Skill 1

Skill 2

Job Name 2

Skill 1

I currently have the working loop for the Experience table, but I'm unable to determine how to also include the job_skills and group it together via job_id. I attempted a nested loop with a SQL query focused on matching the job_ids, but this did not work.


$experience_sql =mysql_query("select e.job_id, e.job_name,from job_experience e where e.user_id='$user_id_session'", $connection);

while ($row = mysql_fetch_assoc($experience_sql)) {
<textarea cols="50" rows="10">
echo $row['job_name']." "."\n";


Answer Source

You can use the following query to get all skills in relation to jobs as a comma separated list:

SELECT e.job_id, e.job_name, GROUP_CONCAT(s.job_skills)
FROM job_experience e
LEFT JOIN job_skills s ON e.job_id = s.job_id
GROUP BY e.job_id