在一个响应中返回多个响应数据

在您的 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';

在此查询中：

• FROM 子句现在以 tbsubjects AS subject 开头以获取主题数据。
• LEFT JOIN 子句用于连接 tbsubjects 表和 tbusers 表以获取相应的用户名。
• 子查询用于计算 subjectid、subjectname、activepts、totalpts每个科目记录的 、 sem1、sem2 和 sem3 值。

执行此查询后，您将获得包含多个科目记录的单个响应，每个记录都包含必要的学生信息和计算值。