如何使用 PHP 和 PDO 在一个响应中返回多个响应数据？

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

提供的代码尝试从数据库中检索多条记录并在单个响应中返回它们。但是，由于 fetchAll 的错误使用，无法合并结果并返回所需的输出。

以下代码提供了更正的解决方案：

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

在此解决方案中：

1. fetchAll 方法与 PDO::FETCH_OBJ 一起使用来检索对象数组，其中每个对象代表结果集中的一行。
2. 自定义 getInputParameterName() 和 getInputParameterDataType () 函数用于将输入参数正确绑定到准备好的语句。
3. IFNULL 函数用于处理total_points、sememer_1_points、sememer_2_points 和ememer_3_points 列中的空值，确保它们在以下情况下返回为零： null。

因此，此代码正确地从数据库中获取多行并将它们合并为单个响应，该响应与预期输出相匹配。

以上是如何使用 PHP 和 PDO 在一个响应中返回多个响应数据？的详细内容。更多信息请关注PHP中文网其他相关文章！