CODEWITHSUNDEEP
phpmysql
user7057468
user7057468

Reputation: 15

Get student details and other relationships in one query and store in one object in mySQL + PHP

I have 3 tables:

1. Students
- StudentID
- StudentName
- etc...

2. Students_Classes
- StudentID
- ClassID

3. Classes
- ClassID
- ClassName
- etc...

Currently, I have two queries to store a Student details and his classes by this way

First, I run this query and return result to PHP object I call $Student:

select StudentID, StudentName, etc..., group_concat(DISTINCT stu_to_cla.ClassID) as currentStudentClassesID from Students LEFT JOIN (Students_Classes as stu_cla, Student.StudentID = stu_cla.StudentID) where StudentID = 1

Second, I use $ClassesID = explode(',' , $Student['currentStudentClassesID'] to an array and pass it to second query:

select(ClassID, ClassName, etc... from (Classes) where ClassesID IN ($ClassesID)

Now I have two objects, one stores student information and another with his classes information.

My question is can I improve it with only one query that return both his information and his classes and how can I use it properly in PHP ?

UPDATE

Now I have another situation when I have several relationship like: Students - Classes, Student - Dorm, Student - States...

So can I get all of one student information about everything and store it in ONLY one object in PHP ? How should I build the the most effective and light-weight mySQL query? Use one big query to get everything at once or build the student object first and then build other relationship objects after that ?

Upvotes: 0

Views: 171

Answers (1)

Strawberry
Strawberry

Reputation: 33935

SELECT s.StudentID
     , s.StudentName
     , c.ClassID
     , c.classname
  from Students s 
  JOIN `Students-Classes`  sc
    ON sc.StudentID = s.StudentID
  JOIN classes c
    ON c.classid = sc.classid
 where s.StudentID = 1;

Upvotes: 1

Related Questions