// 1) Direct references from archive_relations
$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);
// 2) Tag-based fallback
// Let's find all tag_ids for this record, then find other archives with those tag_ids.
$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), '?'));
// We want archives that share at least one of these tag_ids
$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);
// We'll give these a weight=50
foreach ($fallbackRows as &$fb) {
$fb['weight'] = 50;
}
}
// 3) Merge directRows + fallbackRows
$allRows = array_merge($directRows, $fallbackRows);
// 4) Remove duplicates (if an archive is both directly referenced and shares tags)
$tempMap = [];
$finalRows = [];
foreach ($allRows as $row) {
$rid = $row['record_id'];
// If not seen yet or if this row's weight is higher
if (!isset($tempMap[$rid]) || $row['weight'] > $tempMap[$rid]['weight']) {
$tempMap[$rid] = $row;
}
}
$finalRows = array_values($tempMap);
// 5) Sort by weight DESC, then maybe secondary by entry_title
usort($finalRows, function($a, $b){
return $b['weight'] <=> $a['weight']
?: strcmp($a['entry_title'], $b['entry_title']);
});
// 6) For the final display, limit to e.g. 6 items:
$finalRows = array_slice($finalRows, 0, 6);
// 7) Output them in your single column
?>