如何使用 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中文網其他相關文章！