Getting SQL result as nested array in PHP

Question

I've a ('courses') table that has a HABTM relationship with ('instructors') table through another table...

I want to get the data of an instructor with all related courses in one query..

Currently, I have the following SQL:

SELECT *
FROM `instructors` AS `instructor`
LEFT JOIN `courses` AS `course`
  ON `course`.`id` IN (
    SELECT `course_id`
    FROM `course_instructors`
    WHERE `course_instructors`.`instructor_id` = `instructor`.`id`
    )
WHERE `instructor`.`id` = 1

This SQL does what it should be doing, the only "problem" I have is that I get multiple rows for each joined rows.

My question is:
Can I get the result I want in one query? Or do I have to manipulate the data in PHP?

I'm using PHP and MySQL.

(Has and belongs to many/many-to-many) relation...

Ibrahim AshShohail
– Ibrahim AshShohail

2011-04-05 12:48:33 +00:00
Commented Apr 5, 2011 at 12:48 — Ibrahim AshShohail
– Ibrahim AshShohail, Commented Apr 5, 2011 at 12:48

Oswald · Accepted Answer · 2011-04-05 12:37:31Z

1

Each record of a query result set has the same format: same number of fields, same fields, same order of fields. You cannot change that.

answered Apr 5, 2011 at 12:37

Oswald

31.8k3 gold badges45 silver badges72 bronze badges

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

Comments

Itay Moav -Malimovka · Accepted Answer · 2011-04-05 12:42:54Z

1

SELECT *
FROM instructors AS instructor
LEFT JOIN
     course_instructors
ON
     instructor.id= course_instructors.instructor_id
LEFT JOIN
      courses
ON
       course_instructors.course_id =  course.id
WHERE instructor.id = 1

This assumes the PK of course_instructors is (instructor_id,course_id)

Explanation of query:

First join + WHERE make sure you get the relevant instructor
Second join matches ALL the entries from the course_instructor table that belongs to this instructor. If none found, will return one row with NULL in all fields
Last join matches all relevant courses from the entries found from course_instructor If none would will return one record with NULL in all fields.

Again: important to use the right constraints to avoid duplicate data.

edited Apr 5, 2011 at 12:42

answered Apr 5, 2011 at 12:35

Itay Moav -Malimovka

53.8k68 gold badges198 silver badges292 bronze badges

1 Comment

Ibrahim AshShohail Over a year ago

I can not see a difference in the result between my query and yours... I'm still getting more than one row for the instructor I'm querying...

Ali · Accepted Answer · 2011-04-05 12:34:46Z

0

That's the nature of relational databases. You need to get the instructor first and then get the related courses. That's how I would do it and that's how I've been doing it. I'm not sure if there is a "hack" to it.

answered Apr 5, 2011 at 12:34

Ali

3,6863 gold badges27 silver badges32 bronze badges

1 Comment

Ibrahim AshShohail Over a year ago

This is what I was doing too... I'm just want to know if there is a way to do it with SQL.

Collectives™ on Stack Overflow

Getting SQL result as nested array in PHP

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related