获取 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 = [&nbsp; &nbsp; ['id' => 1],&nbsp; &nbsp; ['id' => 2],&nbsp; &nbsp; ['id' => 3],&nbsp; &nbsp; ['id' => 4],];并且您有一个具有以下架构的数据库表：（db-fiddle demo）CREATE TABLE `locations_rating` (&nbsp; `id` int(11) NOT NULL,&nbsp; `l_id` int(11) NOT NULL,&nbsp; `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,&nbsp; &nbsp; &nbsp; &nbsp;ROUND(AVG(COALESCE(stars, 0)), 1) avgFROM (&nbsp; (SELECT 1 AS l_id)&nbsp; UNION (SELECT 2)&nbsp; UNION (SELECT 3)&nbsp; UNION (SELECT 4)&nbsp;) AS defLEFT JOIN locations_rating AS loc ON def.l_id = loc.l_idGROUP 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,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ROUND(AVG(COALESCE(stars, 0)), 1) avg&nbsp; &nbsp; &nbsp; &nbsp; ($fabricatedRows) AS derived&nbsp; &nbsp; &nbsp; &nbsp; LEFT JOIN locations_rating as loc ON derived.l_id = loc.l_id&nbsp; &nbsp; &nbsp; &nbsp; GROUP BY def.l_id";$stmt = $pdo->prepare($sql);$stmt->execute($locationIds);var_export($stmt->fetchAll(PDO::FETCH_ASSOC));应该输出：（我测试了该技术在我的本地环境中是否成功）[&nbsp; &nbsp; ['l_id' => 1, 'avg' => 5.0],&nbsp; &nbsp; ['l_id' => 2, 'avg' => 3.5],&nbsp; &nbsp; ['l_id' => 3, 'avg' => 3.0],&nbsp; &nbsp; ['l_id' => 4, 'avg' => 0.0],]