How to Return Multiple Response Data in One Response in PHP?

Return Multiple Response Data in One Response

Solution

To merge the data into a single response and return the expected output, use the following approach:

1. Rename the userId variable (from $userId to $userid1) to demonstrate that you can provide more users if needed. Then, extend the WHERE clause in the SQL statement and the bindings array accordingly.
2. Modify the SQL statement to calculate the necessary fields:

<code class="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 = :userid1 
            GROUP BY subjects.userid 
            ORDER BY subjects.time DESC;</code>

3. Fetch the users' list as an array of objects using the fetchAll method.
4. Handle the results:

If the users' list is empty, return an error message in JSON format:

<code class="php">$response->getBody()->write(
    '{
        "error": {
            "message":"Invalid"
        }
    }'
);</code>

Otherwise, return the JSON-encoded list of users.

Usage

Assuming your subjects table has the same structure as in your initial description, you can use the following script to fetch and format the required data:

<code class="php"><?php

$userid1 = 1; // Replace this with the actual user ID

// SQL statement
$sql = 'SELECT ... FROM ... WHERE subjects.userid = :userid1 GROUP BY ... ORDER BY ...';

// Bindings
$bindings = array(
    ':userid1' => $userid1
);

// Fetch users
$users = $dbAdapter->fetchAll($sql, $bindings);

// Handle results
if (empty($users)) {
    // Handle empty users list
} else {
    // Handle users list
}</code>

Note:

The script assumes you have a DbAdapter class that provides methods for database interactions. This class should handle connection establishment, statement preparation, and data retrieval.

The above is the detailed content of How to Return Multiple Response Data in One Response in PHP?. For more information, please follow other related articles on the PHP Chinese website!