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

return multiple response data in one response

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:

The main points for the whole operation:

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:

• userid: the id of the user in table users
• username: the name of the user in the table users
• subjectsid: the id of the first subject of this student
• subjectname: the name of the current subject for this row now
• activepts: the points of the current subject
• totalpts: the total points for this student in all subjects
• sem1, sem2, sem3: total points of this student in each semester

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

The code to do this operation:

<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

Example for output:

<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!