我試圖獲取 foreach 循環(huán)內(nèi)的所有行，但它沒有按預(yù)期工作。<?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 都會輸出其他內(nèi)容。它在 foreach 循環(huán)之外工作得很好。我嘗試連接兩個表，但沒有成功，因為它只輸出一行。
1 回答
嗶嗶one
TA貢獻1854條經(jīng)驗 獲得超8個贊
我可能想得太離譜了，但這只是我想到的一種技術(shù)，它將確保所有位置 id 都會在結(jié)果集中收到平均值。
假設(shè)$locations_loop（包含數(shù)組類型數(shù)據(jù)的變量的一個糟糕的名稱，說實話）具有以下數(shù)據(jù)：
$locations_loop = [
['id' => 1],
['id' => 2],
['id' => 3],
['id' => 4],
];
并且您有一個具有以下架構(gòu)的數(shù)據(jù)庫表：（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然后，您可以通過從值列創(chuàng)建一個“派生表” ，然后將數(shù)據(jù)庫數(shù)據(jù)連接到其中，從而一次訪問數(shù)據(jù)庫即可獲取所有數(shù)據(jù)。像這樣的東西：
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
要使用準備好的語句和綁定參數(shù)來執(zhí)行此操作：
$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));
應(yīng)該輸出：（我測試了該技術(shù)在我的本地環(huán)境中是否成功）
[
['l_id' => 1, 'avg' => 5.0],
['l_id' => 2, 'avg' => 3.5],
['l_id' => 3, 'avg' => 3.0],
['l_id' => 4, 'avg' => 0.0],
]
- 1 回答
- 0 關(guān)注
- 103 瀏覽
添加回答
舉報
0/150
提交
取消