query("SELECT COUNT(*) FROM companies");
$totalCompanies = $totalStmt->fetchColumn();
$totalPages = ceil($totalCompanies / $limit);
$searchTerm = trim($_GET['search'] ?? '');
if ($searchTerm !== '') {
// Search query
$stmt = $pdo->prepare("
SELECT c.id, c.name, c.website, c.image, cat.name AS category,
COUNT(r.id) AS review_count,
ROUND(AVG(r.rating),1) AS avg_rating,
MAX(r.rating) AS highest_rating
FROM companies c
LEFT JOIN categories cat ON c.category_id = cat.id
LEFT JOIN reviews r ON c.id = r.company_id
WHERE c.name LIKE :search
GROUP BY c.id
ORDER BY c.name
");
$stmt->execute(['search' => '%' . $searchTerm . '%']);
$companies = $stmt->fetchAll();
$totalCompanies = count($companies); // For display in hero
$totalPages = 1; // Disable pagination for search
} else {
// Original fetch (with pagination)
$stmt = $pdo->prepare("
SELECT c.id, c.name, c.website, c.image, cat.name AS category,
COUNT(r.id) AS review_count,
ROUND(AVG(r.rating),1) AS avg_rating,
MAX(r.rating) AS highest_rating
FROM companies c
LEFT JOIN categories cat ON c.category_id = cat.id
LEFT JOIN reviews r ON c.id = r.company_id
GROUP BY c.id
ORDER BY c.name
LIMIT ? OFFSET ?
");
$stmt->bindValue(1, $limit, PDO::PARAM_INT);
$stmt->bindValue(2, $offset, PDO::PARAM_INT);
$stmt->execute();
$companies = $stmt->fetchAll();
}
// Fetch companies
$stmt = $pdo->prepare("
SELECT c.id, c.name, c.website, c.image, cat.name AS category,
COUNT(r.id) AS review_count,
ROUND(AVG(r.rating),1) AS avg_rating,
MAX(r.rating) AS highest_rating
FROM companies c
LEFT JOIN categories cat ON c.category_id = cat.id
LEFT JOIN reviews r ON c.id = r.company_id
GROUP BY c.id
ORDER BY c.name
LIMIT ? OFFSET ?
");
$stmt->bindValue(1, $limit, PDO::PARAM_INT);
$stmt->bindValue(2, $offset, PDO::PARAM_INT);
$stmt->execute();
$companies = $stmt->fetchAll();
// Latest reviews
$latestReviews = $pdo->query("
SELECT r.comment, r.rating, u.username, c.name AS company_name
FROM reviews r
JOIN users u ON r.user_id = u.id
JOIN companies c ON r.company_id = c.id
ORDER BY r.id DESC
LIMIT 5
")->fetchAll();
// Top rated companies
$topCompanies = $pdo->query("
SELECT c.id, c.name, c.image, ROUND(AVG(r.rating),1) AS avg_rating
FROM companies c
JOIN reviews r ON c.id = r.company_id
GROUP BY c.id
HAVING COUNT(r.id) >= 1
ORDER BY avg_rating DESC
LIMIT 5
")->fetchAll();
// Featured companies
$featuredCompanies = $pdo->query("
SELECT id, name, image, website
FROM companies
ORDER BY RAND()
LIMIT 3
")->fetchAll();
// Latest blog posts (limit 5 for sidebar)
$latestBlogs = $pdo->query("
SELECT id, title
FROM blogs
ORDER BY created_at DESC
LIMIT 5
")->fetchAll();
// Blog news block (limit 4)
$blogNews = $pdo->query("
SELECT id, title, content, image
FROM blogs
ORDER BY created_at DESC
LIMIT 4
")->fetchAll();
?>
Trusted Reviews of Crypto, Forex & Blockchain Companies | ReviewStream 2025