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