prepare("SELECT COUNT(*) FROM blog_posts bp LEFT JOIN blog_categories bc ON bp.category_id = bc.id $where");
$countStmt->execute($params);
$totalPosts = $countStmt->fetchColumn();
$totalPages = max(1, ceil($totalPosts / $perPage));
// Fetch posts
$stmt = $db->prepare("
SELECT bp.*, bc.name as category_name, bc.slug as category_slug, au.full_name as author_name
FROM blog_posts bp
LEFT JOIN blog_categories bc ON bp.category_id = bc.id
LEFT JOIN admin_users au ON bp.author_id = au.id
$where
ORDER BY bp.is_featured DESC, bp.published_at DESC
LIMIT $perPage OFFSET $offset
");
$stmt->execute($params);
$posts = $stmt->fetchAll();
// Featured post (first featured or most recent)
$featuredPost = null;
if ($page === 1 && !$search && !$categorySlug && !$tagSlug) {
$featuredPost = $posts[0] ?? null;
if ($featuredPost) {
$posts = array_slice($posts, 1);
}
}
// Categories
$categories = $db->query("
SELECT bc.*, COUNT(bp.id) as post_count
FROM blog_categories bc
LEFT JOIN blog_posts bp ON bc.id = bp.category_id AND bp.status='published'
GROUP BY bc.id HAVING post_count > 0 ORDER BY post_count DESC
")->fetchAll();
// Popular tags
$popularTags = $db->query("
SELECT bt.name, bt.slug, COUNT(pt.post_id) as cnt
FROM blog_tags bt
JOIN blog_post_tags pt ON bt.id = pt.tag_id
JOIN blog_posts bp ON pt.post_id = bp.id AND bp.status='published'
GROUP BY bt.id ORDER BY cnt DESC LIMIT 15
")->fetchAll();
// Active filter label
$filterLabel = '';
if ($categorySlug) {
foreach ($categories as $c) { if ($c['slug'] === $categorySlug) $filterLabel = $c['name']; }
}
if ($tagSlug) {
$filterLabel = 'Tag: ' . $tagSlug;
}
include __DIR__ . '/includes/header.php';
?>
Fantom Capital Blog
Expert insights on logbook loans, landlord loans, car financing, and smart money management in Kenya.