How to Fetch Multiple Records and Their Related Data in a Single MySQL Query?

Returning Multiple Response Data in One Response

In your PHP code, you have a query that fetches multiple rows of data from a database table. However, you are getting multiple responses, and you want to merge them into a single response with multiple records.

To achieve this, you need to modify your query to perform a left join on the necessary tables. Instead of fetching student data, you will fetch subject data and include the relevant student information using left joins. This will allow you to include related data from multiple tables in a single response.

Here's an example of the updated query:

<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 GROUP BY subjects.userid ORDER BY subjects.time DESC';</code>

In this query:

• The FROM clause now starts with tbsubjects AS subjects to fetch subject data.
• The LEFT JOIN clauses are used to join the tbsubjects table with the tbusers table to fetch the corresponding username.
• The subqueries are used to calculate the subjectsid, subjectname, activepts, totalpts, sem1, sem2, and sem3 values for each subject record.

After executing this query, you will get a single response containing multiple subject records, each with the necessary student information and calculated values.

The above is the detailed content of How to Fetch Multiple Records and Their Related Data in a Single MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!