$stmt = $pdo->prepare("
SELECT a.record_id, a.entry_title, a.related_thumbnail,
a.related_text, r.weight
FROM archive_relations r
JOIN archive_records a ON a.record_id = r.related_record_id
WHERE r.record_id = :rid
");
$stmt->execute([':rid' => $recordId]);
$directRows = $stmt->fetchAll(PDO::FETCH_ASSOC);
$tagIdsStmt = $pdo->prepare("
SELECT at.tag_id
FROM archive_tags at
WHERE at.archive_id = :rid
");
$tagIdsStmt->execute([':rid' => $recordId]);
$myTagIds = $tagIdsStmt->fetchAll(PDO::FETCH_COLUMN);
$fallbackRows = [];
if (!empty($myTagIds)) {
// get other archives that share these tags
$inPlaceholders = implode(',', array_fill(0, count($myTagIds), '?'));
$sql = "
SELECT DISTINCT a.record_id, a.entry_title, a.related_thumbnail,
a.related_text
FROM archive_tags at
JOIN archive_records a ON at.archive_id = a.record_id
WHERE at.tag_id IN ($inPlaceholders)
AND a.record_id <> ? -- exclude the current record
";
$tagStmt = $pdo->prepare($sql);
$params = array_merge($myTagIds, [$recordId]);
$tagStmt->execute($params);
$fallbackRows = $tagStmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($fallbackRows as &$fb) {
$fb['weight'] = 50;
}
}
$allRows = array_merge($directRows, $fallbackRows);
$tempMap = [];
$finalRows = [];
foreach ($allRows as $row) {
$rid = $row['record_id'];
if (!isset($tempMap[$rid]) || $row['weight'] > $tempMap[$rid]['weight']) {
$tempMap[$rid] = $row;
}
}
$finalRows = array_values($tempMap);
usort($finalRows, function($a, $b){
return $b['weight'] <=> $a['weight']
?: strcmp($a['entry_title'], $b['entry_title']);
});
$finalRows = array_slice($finalRows, 0, 6);
?>