在一個回應中傳回多個回應資料

在您的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 ASsubject 開頭以獲取主題數據。
• LEFT JOIN 子句用於連接 tbsubjects 表和 tbusers 表以取得對應的使用者名稱。
• 子查詢用於計算 subjectid、subjectname、activepts、totalpts每個科目記錄的 、 sem1、sem2 和 sem3 值。

執行此查詢後，您將獲得包含多個科目記錄的單一回應，每個記錄都包含必要的學生資訊和計算值。