In previous question I was asking about how to merge all subjects of all semesters to one record so each student should be one response and his data will be like this example:
<code class="json">[ { "userid": "1", - from users table "username": "joe", - from users table "subjectsid": "1", - first subject id for the student in this case the one for phy "subjectname": "bio", - current subject name "activepts": "652", - points of current month "totalpts": "717", - total points of all subjects for this student "sem1": "32", - total points of all subjects for this student of semester 1 "sem2": "0", - total points of all subjects for this student of semester 2 "sem3": "685", - total points of all subjects for this student of semester 3 } ]</code>
So I got one and only one answer but the answer didn't tell me how to do it just told me the reason why I need to use LEFT JOIN and explain to me what LEFT JOIN will do in my case, so I will explain again what I need to do then I will explain how to do this by code and some examples:
1- Each student will be one response
2- each response contains all of his subjects, each subject will be a row
3- First row will contain some static data for the student:
4- Rest of rows are same like what comes to me from the DB and no need to change something except filter the subjects to be only for this user
<code class="php">$sql = 'SELECT subjects.userid, users.name AS username, ( SELECT id FROM tbsubjects WHERE userid = subjects.userid ORDER BY id ASC LIMIT 1 ) AS subjectsid, ( SELECT name FROM tbsubjects WHERE userid = subjects.userid ORDER BY time DESC LIMIT 1 ) AS subjectname, ( SELECT IFNULL(SUM(points), 0) FROM tbsubjects WHERE userid = subjects.userid AND month = DATE_FORMAT(NOW(), "%c") ) AS activepts, IFNULL(SUM(subjects.points), 0) AS totalpts, ( SELECT IFNULL(SUM(points), 0) FROM tbsubjects WHERE userid = subjects.userid AND semester = 1 ) AS sem1, ( SELECT IFNULL(SUM(points), 0) FROM tbsubjects WHERE userid = subjects.userid AND semester = 2 ) AS sem2, ( SELECT IFNULL(SUM(points), 0) FROM tbsubjects WHERE userid = subjects.userid AND semester = 3 ) AS sem3 FROM tbsubjects AS subjects LEFT JOIN tbusers AS users ON users.id = subjects.userid WHERE subjects.userid = :userid'; $bindings = array( ':userid' => $userID, ); $users = $statement->fetchAll(PDO::FETCH_OBJ);</code>
Explain the code:
1- Fields in the SELECT statement, as you can see they are divided into 2 groups the first one at the top is static fields for the student, the rest will get the values from table tbsubjects
2- The LEFT JOIN function is responsible to get all (userid) from the table tbsubjects and merge them by the common key between them userid, which means now each user will be represented by at least one row contains his first subject ( ORDER BY id ASC ) then all of his other subjects, but they already merged in one row will be repeated in rest of rows as those static fields of the student
<code class="json">[ { "userid": "1", - from users table "username": "joe", - from users table "subjectsid": "1", - first subject id for the student in this case the one for phy "subjectname": "bio", - current subject name "activepts": "652", - points of current month "totalpts": "717", - total points of all subjects for this student "sem1": "32", - total points of all subjects for this student of semester 1 "sem2": "0", - total points of all subjects for this student of semester 2 "sem3": "685", - total points of all subjects for this student of semester 3 }, { "userid": "1", - from users table "username": "joe", - from users table "subjectsid": "1", - first subject id for the student in this case the one for phy "subjectname": "phy", - current subject name "activepts": "10", - points of current month "totalpts": "717", - total points of all subjects for this student "sem1": "32", - total points of all subjects for this student of semester 1 "sem2": "0", - total points of all subjects for this student of semester 2 "sem3": "685", - total points of all subjects for this student of semester 3 }, { "userid": "1", - from users table "username": "joe", - from users table "subjectsid": "1", - first subject id for the student in this case the one for phy "subjectname": "math", - current subject name "activepts": "33", - points of current month "totalpts": "717", - total points of all subjects for this student "sem1": "32", - total points of all subjects for this student of semester 1 "sem2": "0", - total points of all subjects for this student of semester 2 "sem3": "685", - total points of all subjects for this student of semester 3 } ]</code>
The above is the detailed content of Here are a few question-based titles that fit your provided article: * How to Merge Student Subjects into One Record with a LEFT JOIN and Subqueries? * Creating a Unified Student Data Structure with. For more information, please follow other related articles on the PHP Chinese website!