How can I return multiple response data in one response using PHP and PDO?

Return Multiple Response Data in One Response

The provided code attempts to retrieve multiple records from a database and return them in a single response. However, due to incorrect usage of fetchAll, it is not able to merge the results and return the desired output.

The following code provides a corrected solution:

<code class="php">try {
    $dbAdapter = new DbAdapter();

    $connection = $dbAdapter->connect();

    // User IDs to be fetched.
    $userIds = [1, 2];

    // The sql statement - it will be prepared.
    $sql = 'SELECT 
                users.id AS userid,
                users.name AS username,
                subjects.id AS subject_id,
                subjects.name AS subject_name,
                subjects.points AS active_points,
                IFNULL(SUM(subjects.points), 0) AS total_points,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM subjects 
                    WHERE 
                        userid = users.id AND semester = 1
                ) AS semester_1_points,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM subjects 
                    WHERE 
                        userid = users.id AND semester = 2
                ) AS semester_2_points,
                (
                    SELECT IFNULL(SUM(points), 0) 
                    FROM subjects 
                    WHERE 
                        userid = users.id AND semester = 3
                ) AS semester_3_points 
            FROM 
                tbsubjects AS subjects 
                LEFT JOIN tbusers AS users ON users.id = subjects.userid 
            WHERE subjects.userid IN (:userIds) 
            GROUP BY subjects.userid 
            ORDER BY subjects.time DESC';

    // The input parameters list for the prepared sql statement.
    $bindings = array(
        ':userIds' => $userIds,
    );

    // Prepare and validate the sql statement.
    $statement = $connection->prepare($sql);

    if (!$statement) {
        throw new UnexpectedValueException('The sql statement could not be prepared!');
    }

    // Bind the input parameters to the prepared statement.
    foreach ($bindings as $key => $value) {
        $bound = $statement->bindValue(
            getInputParameterName($key),
            $value,
            getInputParameterDataType($value)
        );

        if (!$bound) {
            throw new UnexpectedValueException('An input parameter can not be bound!');
        }
    }

    // Execute the prepared statement.
    $executed = $statement->execute();

    if (!$executed) {
        throw new UnexpectedValueException('The prepared statement could not be executed!');
    }

    // Fetch users list - array of objects.
    $users = $statement->fetchAll(PDO::FETCH_OBJ);

    if ($users === FALSE) {
        throw new UnexpectedValueException('Fetching users list failed!');
    }

    // Close connection.
    $connection = NULL;

    // Handle results.
    if (empty($users)) {
        $response->getBody()->write(
            '{
                "error": {
                    "message":"Invalid"
                }
            }'
        );
    } else {
        $response->getBody()->write(json_encode($users));
    }
} catch (PDOException $exc) {
    echo $exc->getMessage();
    // $logger->log($exc);
    exit();
} catch (Exception $exc) {
    echo $exc->getMessage();
    // $logger->log($exc);
    exit();
}</code>

In this solution:

1. The fetchAll method is used with PDO::FETCH_OBJ to retrieve an array of objects, where each object represents a row in the result set.
2. The custom getInputParameterName() and getInputParameterDataType() functions are used to correctly bind input parameters to the prepared statement.
3. The IFNULL function is used to handle null values in the total_points, semester_1_points, semester_2_points, and semester_3_points columns, ensuring that they are returned as zero if null.

As a result, this code correctly fetches multiple rows from the database and merges them into a single response, which matches the expected output.

The above is the detailed content of How can I return multiple response data in one response using PHP and PDO?. For more information, please follow other related articles on the PHP Chinese website!