prepare("SELECT id FROM categories WHERE slug = ?");
$catStmt->bind_param("s", $categorySlug);
$catStmt->execute();
$catResult = $catStmt->get_result();
if ($catResult->num_rows > 0) {
$catData = $catResult->fetch_assoc();
$categoryId = $catData['id'];
$where[] = "tp.category_id = ?";
$params[] = $categoryId;
$types .= "i";
}
}
/* ========= DESTINATION (SLUG BASED) ========= */
if (!empty($_GET['destination'])) {
$where[] = "d.slug = ?";
$params[] = $_GET['destination'];
$types .= "s";
}
/* ========= SEARCH ========= */
if (!empty($_GET['search'])) {
$where[] = "(tp.title LIKE ? OR tp.overview LIKE ?)";
$searchValue = "%" . $_GET['search'] . "%";
$params[] = $searchValue;
$params[] = $searchValue;
$types .= "ss";
}
/* ========= DURATION (varchar) ========= */
if (!empty($_GET['duration'])) {
$range = explode("-", $_GET['duration']);
if (count($range) == 2) {
$min = (int)$range[0];
$max = (int)$range[1];
$where[] = "CAST(SUBSTRING_INDEX(tp.duration, ' ', 1) AS UNSIGNED) BETWEEN ? AND ?";
$params[] = $min;
$params[] = $max;
$types .= "ii";
}
}
/* ========= PRICE RANGE ========= */
if (!empty($_GET['price'])) {
$priceParts = explode("-", $_GET['price']);
if (count($priceParts) == 2) {
$min = (float)$priceParts[0];
$max = (float)$priceParts[1];
$where[] = "tp.price_after BETWEEN ? AND ?";
$params[] = $min;
$params[] = $max;
$types .= "dd";
}
}
/* ========= WHERE BUILD ========= */
$whereSQL = "";
if (!empty($where)) {
$whereSQL = "WHERE " . implode(" AND ", $where);
}
/* ===========================
SORTING
=========================== */
$sort = $_GET['sort'] ?? '';
$orderBy = "tp.id DESC";
switch ($sort) {
case 'price_low_high':
$orderBy = "tp.price_after ASC";
break;
case 'price_high_low':
$orderBy = "tp.price_after DESC";
break;
case 'duration_short_long':
$orderBy = "tp.duration ASC";
break;
case 'duration_long_short':
$orderBy = "tp.duration DESC";
break;
case 'popular':
$orderBy = "tp.is_popular DESC";
break;
case 'newest':
$orderBy = "tp.id DESC";
break;
}
/* ===========================
PAGINATION
=========================== */
$limit = 9;
$page = isset($_GET['page']) && $_GET['page'] > 0 ? (int)$_GET['page'] : 1;
$offset = ($page - 1) * $limit;
/* ===========================
TOTAL COUNT
=========================== */
$countQuery = "
SELECT COUNT(*) as total
FROM tour_packages tp
JOIN destinations d ON tp.destination_id = d.id
JOIN categories c ON tp.category_id = c.id
$whereSQL
";
$stmt = $conn->prepare($countQuery);
if (!empty($params)) {
$stmt->bind_param($types, ...$params);
}
$stmt->execute();
$countResult = $stmt->get_result()->fetch_assoc();
$totalPackages = $countResult['total'];
$totalPages = ceil($totalPackages / $limit);
/* ===========================
FETCH DATA
=========================== */
$query = "
SELECT tp.*,
d.name AS destination_name,
c.category_name
FROM tour_packages tp
JOIN destinations d ON tp.destination_id = d.id
JOIN categories c ON tp.category_id = c.id
$whereSQL
ORDER BY $orderBy
LIMIT ? OFFSET ?
";
$stmt = $conn->prepare($query);
/* Add limit & offset */
$finalParams = $params;
$finalTypes = $types . "ii";
$finalParams[] = $limit;
$finalParams[] = $offset;
$stmt->bind_param($finalTypes, ...$finalParams);
$stmt->execute();
$result = $stmt->get_result();
/* ===========================
FETCH CATEGORIES
=========================== */
$categories = $conn->query("
SELECT * FROM categories
ORDER BY category_name ASC
");
?>
<?= $settings['site_title'] ?>
![<?= htmlspecialchars($row['title']); ?>](admin/uploads/packages/<?= $row['main_image']; ?>)