获取 foreach 循环内的所有行并回显数组结果？

Question

我试图获取 foreach 循环内的所有行，但它没有按预期工作。<?php foreach ($locations_loop as $row):    $lr_id  = $row["id"];    $stmtlr = $pdo->prepare("SELECT * FROM locations_rating WHERE l_id = {$lr_id}");    $stmtlr->execute();    $stlr_loop = $stmtlr->fetchAll(PDO::FETCH_ASSOC);    if (empty($stlr_loop)) {        $loc_rate[] = "0";    } else {        foreach($stlr_loop as $rowlr):            $loc_rate[] = $rowlr["stars"];        endforeach;    }        $rating_array = array_values($loc_rate);    $rating_avg   = array_sum($rating_array) / count($rating_array);?>      <?=round($rating_avg, 1);?>    <?php endforeach; ?>每次脚本运行时，$ rating_avg 都会输出其他内容。它在 foreach 循环之外工作得很好。我尝试连接两个表，但没有成功，因为它只输出一行。

Answer 1

我可能想得太离谱了，但这只是我想到的一种技术，它将确保所有位置 id 都会在结果集中收到平均值。

假设$locations_loop（包含数组类型数据的变量的一个糟糕的名称，说实话）具有以下数据：

$locations_loop = [

    ['id' => 1],

    ['id' => 2],

    ['id' => 3],

    ['id' => 4],

];

并且您有一个具有以下架构的数据库表：（db-fiddle demo）

CREATE TABLE `locations_rating` (

  `id` int(11) NOT NULL,

  `l_id` int(11) NOT NULL,

  `stars` int(11) NOT NULL DEFAULT 0

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `locations_rating` (`id`, `l_id`, `stars`) VALUES

(1, 3, 4),

(2, 2, 2),

(3, 1, 0),

(4, 2, 5),

(5, 3, 2),

(6, 1, 10);

id然后，您可以通过从值列创建一个“派生表” ，然后将数据库数据连接到其中，从而一次访问数据库即可获取所有数据。像这样的东西：

SELECT def.l_id,

       ROUND(AVG(COALESCE(stars, 0)), 1) avg

FROM (

  (SELECT 1 AS l_id)

  UNION (SELECT 2)

  UNION (SELECT 3)

  UNION (SELECT 4)

 ) AS def

LEFT JOIN locations_rating AS loc ON def.l_id = loc.l_id

GROUP BY def.l_id

要使用准备好的语句和绑定参数来执行此操作：

$locationIds = array_column($locations_loop, 'id');

$countIds = count($locationIds);

$fabricatedRows = implode(' UNION ', array_fill(0, $countIds, '(SELECT ? AS l_id)'));

$sql = "SELECT derived.l_id,

               ROUND(AVG(COALESCE(stars, 0)), 1) avg

        ($fabricatedRows) AS derived

        LEFT JOIN locations_rating as loc ON derived.l_id = loc.l_id

        GROUP BY def.l_id";

$stmt = $pdo->prepare($sql);

$stmt->execute($locationIds);

var_export($stmt->fetchAll(PDO::FETCH_ASSOC));

应该输出：（我测试了该技术在我的本地环境中是否成功）

[

    ['l_id' => 1, 'avg' => 5.0],

    ['l_id' => 2, 'avg' => 3.5],

    ['l_id' => 3, 'avg' => 3.0],

    ['l_id' => 4, 'avg' => 0.0],

]