403WebShell

403Webshell
Server IP : 51.79.149.130 / Your IP : 216.73.216.210
Web Server : LiteSpeed
System : Linux linux.firevps.net 5.4.0-216-generic #236-Ubuntu SMP Fri Apr 11 19:53:21 UTC 2025 x86_64
User : digit4868 ( 1088)
PHP Version : 8.2.30
Disable Function : NONE
MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON
Directory : /home/digitallibrary.astgd.com/public_html/includes/
Upload File :
[ Back ]
Current File : /home/digitallibrary.astgd.com/public_html/includes/config.php
<?php
// Database configuration
// Database configuration
$db_host = 'localhost';
$db_user = 'digi_libraryuser';
$db_pass = 'valid1000!';
$db_name = 'digi_library';

// Create connection
$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Set charset to utf8mb4
$conn->set_charset("utf8mb4");

// Session start
session_start();

// Function to check if user is logged in
function isLoggedIn() {
    return isset($_SESSION['user_id']);
}

// Function to check if user is admin
function isAdmin() {
    return isset($_SESSION['user_role']) && $_SESSION['user_role'] == 'admin';
}

// Function to check if user is editor
function isEditor() {
    return isset($_SESSION['user_role']) && $_SESSION['user_role'] == 'editor';
}

// Function to sanitize input data
function sanitize($data) {
    global $conn;
    return $conn->real_escape_string(trim($data));
}

// Function to redirect to another page
function redirect($url) {
    header("Location: $url");
    exit();
}

// Function to display error message
function showError($message) {
    return "<div class='alert alert-danger'>$message</div>";
}

// Function to display success message
function showSuccess($message) {
    return "<div class='alert alert-success'>$message</div>";
}

// Function to get all books
// function getAllBooks($limit = null, $offset = 0) {
//     global $conn;
    
//     $sql = "SELECT b.*, a.name as author_name, c.name as category_name 
//             FROM books b 
//             JOIN authors a ON b.author_id = a.id 
//             JOIN categories c ON b.category_id = c.id 
//             ORDER BY b.created_at DESC";
    
//     if ($limit !== null) {
//         $sql .= " LIMIT $offset, $limit";
//     }
    
//     $result = $conn->query($sql);
//     $books = [];
    
//     if ($result->num_rows > 0) {
//         while ($row = $result->fetch_assoc()) {
//             $books[] = $row;
//         }
//     }
    
//     return $books;
// }
function getAllBooks() {
    global $conn;
    $sql = "SELECT b.*, c.name AS category_name, a.name AS author_name,
                   COALESCE(vc.view_count, 0) AS views
            FROM books b
            JOIN categories c ON b.category_id = c.id
            JOIN authors a ON b.author_id = a.id
            LEFT JOIN (
                SELECT book_id, COUNT(*) as view_count
                FROM book_views
                GROUP BY book_id
            ) vc ON b.id = vc.book_id
            ORDER BY b.title ASC";
    $result = $conn->query($sql);
    return $result->fetch_all(MYSQLI_ASSOC);
}

function getFeaturedBooks($limit = 4) {
    global $conn; // Your database connection
    $limit = (int)$limit; // Ensure $limit is an integer

    // SQL query to select only books where 'is_featured' is true (1)
    // It orders them by 'featured_order' first, then by creation date as a fallback.
    // It also fetches author name, category name, and view count for display consistency.
    $sql = "SELECT 
                b.id, 
                b.title, 
                b.cover_image, 
                b.is_featured,
                b.featured_order,
                a.name AS author_name, 
                c.name AS category_name, 
                COALESCE(vc.view_count, 0) AS view_count 
            FROM books b
            JOIN authors a ON b.author_id = a.id
            JOIN categories c ON b.category_id = c.id
            LEFT JOIN (
                SELECT book_id, COUNT(*) AS view_count
                FROM book_views
                GROUP BY book_id
            ) vc ON b.id = vc.book_id
            WHERE b.is_featured = 1
            ORDER BY b.featured_order ASC, b.created_at DESC
            LIMIT ?";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        // Log error if statement preparation fails
        error_log("Database Error: Failed to prepare statement for getFeaturedBooks. MySQL Error: " . $conn->error);
        return []; // Return an empty array on failure
    }

    $stmt->bind_param("i", $limit); // Bind the integer limit
    $stmt->execute();
    $result = $stmt->get_result();

    $books = []; // Initialize an empty array to store books
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $books[] = $row; // Add each fetched book to the array
        }
    }
    $stmt->close(); // Close the statement

    return $books; // Return the array of featured books
}

// Function to get book by ID
function getBookById($id) {
    global $conn;
    
    $id = (int)$id;
    $sql = "SELECT b.*, a.name as author_name, c.name as category_name 
            FROM books b 
            JOIN authors a ON b.author_id = a.id 
            JOIN categories c ON b.category_id = c.id 
            WHERE b.id = $id";
    
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
        return $result->fetch_assoc();
    }
    
    return null;
}

// Function to get books by category
// function getBooksByCategory($category_id, $limit = null) {
//     global $conn;
    
//     $category_id = (int)$category_id;
//     $sql = "SELECT b.*, a.name as author_name, c.name as category_name 
//             FROM books b 
//             JOIN authors a ON b.author_id = a.id 
//             JOIN categories c ON b.category_id = c.id 
//             WHERE b.category_id = $category_id 
//             ORDER BY b.created_at DESC";
    
//     if ($limit !== null) {
//         $sql .= " LIMIT $limit";
//     }
    
//     $result = $conn->query($sql);
//     $books = [];
    
//     if ($result->num_rows > 0) {
//         while ($row = $result->fetch_assoc()) {
//             $books[] = $row;
//         }
//     }
    
//     return $books;
// }
function getBooksByCategory($category_id) {
    global $conn;
    $stmt = $conn->prepare("SELECT b.*, c.name AS category_name, a.name AS author_name,
                                   COALESCE(vc.view_count, 0) AS views
                            FROM books b
                            JOIN categories c ON b.category_id = c.id
                            JOIN authors a ON b.author_id = a.id
                            LEFT JOIN (
                                SELECT book_id, COUNT(*) as view_count
                                FROM book_views
                                GROUP BY book_id
                            ) vc ON b.id = vc.book_id
                            WHERE b.category_id = ?
                            ORDER BY b.title ASC");
    $stmt->bind_param("i", $category_id);
    $stmt->execute();
    $result = $stmt->get_result();
    return $result->fetch_all(MYSQLI_ASSOC);
}

// Function to get books by author
// function getBooksByAuthor($author_id, $limit = null) {
//     global $conn;
    
//     $author_id = (int)$author_id;
//     $sql = "SELECT b.*, a.name as author_name, c.name as category_name 
//             FROM books b 
//             JOIN authors a ON b.author_id = a.id 
//             JOIN categories c ON b.category_id = c.id 
//             WHERE b.author_id = $author_id 
//             ORDER BY b.created_at DESC";
    
//     if ($limit !== null) {
//         $sql .= " LIMIT $limit";
//     }
    
//     $result = $conn->query($sql);
//     $books = [];
    
//     if ($result->num_rows > 0) {
//         while ($row = $result->fetch_assoc()) {
//             $books[] = $row;
//         }
//     }
    
//     return $books;
// }
function getBooksByAuthor($author_id) {
    global $conn;
    $stmt = $conn->prepare("SELECT b.*, c.name AS category_name, a.name AS author_name,
                                   COALESCE(vc.view_count, 0) AS views
                            FROM books b
                            JOIN categories c ON b.category_id = c.id
                            JOIN authors a ON b.author_id = a.id
                            LEFT JOIN (
                                SELECT book_id, COUNT(*) as view_count
                                FROM book_views
                                GROUP BY book_id
                            ) vc ON b.id = vc.book_id
                            WHERE b.author_id = ?
                            ORDER BY b.title ASC");
    $stmt->bind_param("i", $author_id);
    $stmt->execute();
    $result = $stmt->get_result();
    return $result->fetch_all(MYSQLI_ASSOC);
}

// Function to search books
function searchBooks($query) {
    global $conn;
    
    $query = sanitize($query);
    $sql = "SELECT b.*, a.name as author_name, c.name as category_name 
            FROM books b 
            JOIN authors a ON b.author_id = a.id 
            JOIN categories c ON b.category_id = c.id 
            WHERE b.title LIKE '%$query%' 
            OR a.name LIKE '%$query%' 
            OR b.description LIKE '%$query%' 
            ORDER BY b.created_at DESC";
    
    $result = $conn->query($sql);
    $books = [];
    
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $books[] = $row;
        }
    }
    
    return $books;
}

// Function to get all categories
function getAllCategories() {
    global $conn;
    
    $sql = "SELECT c.*, COUNT(b.id) as book_count 
            FROM categories c 
            LEFT JOIN books b ON c.id = b.category_id 
            GROUP BY c.id 
            ORDER BY c.name";
    
    $result = $conn->query($sql);
    $categories = [];
    
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $categories[] = $row;
        }
    }
    
    return $categories;
}

// Function to get all authors
function getAllAuthors() {
    global $conn;
    
    $sql = "SELECT a.*, COUNT(b.id) as book_count 
        FROM authors a 
        LEFT JOIN books b ON a.id = b.author_id 
        GROUP BY a.id 
        ORDER BY RAND()";
    
    $result = $conn->query($sql);
    $authors = [];
    
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $authors[] = $row;
        }
    }
    
    return $authors;
}

// Function to get author by ID
function getAuthorById($id) {
    global $conn;
    
    $id = (int)$id;
    $sql = "SELECT * FROM authors WHERE id = $id";
    
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
        return $result->fetch_assoc();
    }
    
    return null;
}

// Function to get category by ID
function getCategoryById($id) {
    global $conn;
    
    $id = (int)$id;
    $sql = "SELECT * FROM categories WHERE id = $id";
    
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
        return $result->fetch_assoc();
    }
    
    return null;
}

// Function to get slideshow images
function getSlideshow() {
    global $conn;
    
    $sql = "SELECT * FROM slideshow_images WHERE active = 1 ORDER BY display_order";
    
    $result = $conn->query($sql);
    $slides = [];
    
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $slides[] = $row;
        }
    }
    
    return $slides;
}

// Function to get page by slug
function getPageBySlug($slug) {
    global $conn;
    
    $slug = sanitize($slug);
    $sql = "SELECT * FROM pages WHERE slug = '$slug' AND status = 'published'";
    
    $result = $conn->query($sql);
    
    if ($result->num_rows > 0) {
        return $result->fetch_assoc();
    }
    
    return null;
}

// Function to record book view
function recordBookView($book_id) {
    global $conn;
    
    $book_id = (int)$book_id;
    $ip = sanitize($_SERVER['REMOTE_ADDR']); // Sanitize IP
    $user_agent = sanitize($_SERVER['HTTP_USER_AGENT']); // Sanitize User Agent
    
    // Using prepared statement for security
    $stmt = $conn->prepare("INSERT INTO book_views (book_id, ip_address, user_agent) VALUES (?, ?, ?)");
    if ($stmt) {
        $stmt->bind_param("iss", $book_id, $ip, $user_agent);
        $stmt->execute();
        $stmt->close();
    } else {
        error_log("Failed to prepare statement for recordBookView: " . $conn->error);
    }
}

// Function to get book view count
// function getBookViewCount($book_id) {
//     global $conn;
    
//     $book_id = (int)$book_id;
//     $sql = "SELECT COUNT(*) as view_count FROM book_views WHERE book_id = $book_id";
    
//     $result = $conn->query($sql);
    
//     if ($result->num_rows > 0) {
//         $row = $result->fetch_assoc();
//         return $row['view_count'];
//     }
    
//     return 0;
// }

// Function to get book view count (Your existing function - ensure it's working correctly)
function getBookViewCount($book_id) {
    global $conn;
    
    $book_id = (int)$book_id;
    // Using prepared statement
    $stmt = $conn->prepare("SELECT COUNT(*) as view_count FROM book_views WHERE book_id = ?");
    if ($stmt) {
        $stmt->bind_param("i", $book_id);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            $stmt->close();
            return $row['view_count'];
        }
        $stmt->close();
    } else {
        error_log("Failed to prepare statement for getBookViewCount: " . $conn->error);
    }
    return 0;
}

/**
 * Gets a list of 10 random books selected from the top 50 most viewed books,
 * with the final selection cached for 1 hour.
 * Uses the book_views table to determine popularity.
 *
 * @return array An array of 10 book records, or an empty array on failure.
 */
function getPopularAndRandomlyOpenedBooksCachedHourly() {
    global $conn;

    $cache_file = __DIR__ . '/../cache/popular_opened_books_cache.json';
    $cache_duration = 600; // 10 minutes

    // Try to get from cache (no changes here)
    if (file_exists($cache_file) && (time() - filemtime($cache_file) < $cache_duration)) {
        $cached_data = file_get_contents($cache_file);
        if ($cached_data) {
            $books = json_decode($cached_data, true);
            if (is_array($books) && !empty($books)) {
                return $books;
            }
        }
    }

    $top_n_viewed = 50;
    $select_n_random = 10;
    $minimum_views = 5;

    // SQL to get top N viewed books, ensuring they have a minimum number of views
    $sql = "SELECT b.id, b.title, b.cover_image, a.name as author_name, c.name as category_name, COUNT(bv.book_id) as view_count
            FROM books b
            JOIN authors a ON b.author_id = a.id
            JOIN categories c ON b.category_id = c.id
            LEFT JOIN book_views bv ON b.id = bv.book_id
            GROUP BY b.id, b.title, b.cover_image, a.name, c.name
            HAVING COUNT(bv.book_id) > ?
            ORDER BY view_count DESC
            LIMIT ?";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        error_log("Failed to prepare statement for popular books (cached hourly with min views): " . $conn->error);
        return [];
    }

    // Bind the minimum views AND the limit
    $stmt->bind_param("ii", $minimum_views, $top_n_viewed); // "i" for integer
    $stmt->execute();
    $result = $stmt->get_result();

    $popular_books_pool = [];
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $popular_books_pool[] = $row;
        }
    }
    $stmt->close();

    // The rest of the function (caching, shuffle, slice) remains the same
    if (empty($popular_books_pool)) {
        if (file_exists($cache_file)) {
            unlink($cache_file);
        }
        return [];
    }

    $selected_books = [];
    if (count($popular_books_pool) <= $select_n_random) {
        shuffle($popular_books_pool);
        $selected_books = $popular_books_pool;
    } else {
        shuffle($popular_books_pool);
        $selected_books = array_slice($popular_books_pool, 0, $select_n_random);
    }

    if (!empty($selected_books)) {
        if (!is_dir(dirname($cache_file))) {
            if (!mkdir(dirname($cache_file), 0755, true) && !is_dir(dirname($cache_file))) {
                 error_log('Failed to create cache directory: ' . dirname($cache_file));
                 return $selected_books;
            }
        }
        if (file_put_contents($cache_file, json_encode($selected_books)) === false) {
            error_log('Failed to write to cache file: ' . $cache_file);
        }
    } else {
        if (file_exists($cache_file)) {
            unlink($cache_file);
        }
    }
    return $selected_books;
}

function getMostPopularBooks($limit = 12) {
    global $conn;
    $limit = (int)$limit; // Ensure $limit is an integer

    // SQL query to select books, join with authors, categories, and count views.
    // Orders by the total view count in descending order.
    $sql = "SELECT 
                b.id, 
                b.title, 
                b.cover_image, 
                a.name AS author_name, 
                c.name AS category_name, 
                COUNT(bv.book_id) AS view_count
            FROM books b
            JOIN authors a ON b.author_id = a.id
            JOIN categories c ON b.category_id = c.id
            LEFT JOIN book_views bv ON b.id = bv.book_id
            GROUP BY b.id, b.title, b.cover_image, a.name, c.name
            ORDER BY view_count DESC, b.title ASC
            LIMIT ?";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        // Log error if statement preparation fails
        error_log("Database Error: Failed to prepare statement for getMostPopularBooks. MySQL Error: " . $conn->error);
        return []; // Return an empty array on failure
    }

    $stmt->bind_param("i", $limit); // Bind the integer limit
    $stmt->execute();
    $result = $stmt->get_result();

    $books = []; // Initialize an empty array to store books
    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $books[] = $row; // Add each fetched book to the array
        }
    }
    $stmt->close(); // Close the statement

    return $books; // Return the array of most popular books
}

// Function to get all videos
function getAllVideos($limit = null, $offset = 0) {
    global $conn;
    
    $sql = "SELECT v.* "; // Add , vc.name as category_name if you implement video categories
    $sql .= "FROM videos v ";
    // if ($video_categories_implemented) {
    //    $sql .= "LEFT JOIN video_categories vc ON v.category_id = vc.id ";
    // }
    $sql .= "ORDER BY v.created_at DESC";
    
    if ($limit !== null) {
        // Ensure offset and limit are integers
        $offset = (int)$offset;
        $limit = (int)$limit;
        $sql .= " LIMIT ?, ?";
        $stmt = $conn->prepare($sql);
        $stmt->bind_param("ii", $offset, $limit);
    } else {
        $stmt = $conn->prepare($sql);
    }
    
    if (!$stmt) {
        error_log("Get all videos statement prep failed: " . $conn->error);
        return [];
    }

    $stmt->execute();
    $result = $stmt->get_result();
    $videos = [];
    
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $videos[] = $row;
        }
    }
    $stmt->close();
    return $videos;
}

// getPopularCategories
function getPopularCategories($limit = 10) {
    global $conn;
    $limit = (int)$limit;

    $popularCategories = [];
    // IMPORTANT: Adjust path to cache file relative to config.php (which is in 'includes/')
    $cache_file_popular_categories = __DIR__ . '/../cache/popular_categories_cache.json';
    $cache_lifetime = 3600; // Cache for 1 hour

    // Check if cache file exists and is still valid
    if (file_exists($cache_file_popular_categories) && (time() - filemtime($cache_file_popular_categories) < $cache_lifetime)) {
        // Cache is valid, load data from cache
        $popularCategories = json_decode(file_get_contents($cache_file_popular_categories), true);
    } else {
        // Cache is expired or doesn't exist, fetch from database
        $sql = "SELECT c.id, c.name, COUNT(b.id) AS book_count
                FROM categories c
                LEFT JOIN books b ON c.id = b.category_id
                WHERE c.is_popular = 1
                GROUP BY c.id, c.name -- Group by all selected non-aggregate columns
                ORDER BY c.popular_order ASC, c.name ASC
                LIMIT ?";

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            error_log("Database Error: Failed to prepare statement for getPopularCategories. MySQL Error: " . $conn->error);
            return [];
        }

        $stmt->bind_param("i", $limit);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $popularCategories[] = $row;
            }
        }
        $stmt->close();

        // Save fetched data to cache
        // Ensure the cache directory exists before writing
        $cache_dir = dirname($cache_file_popular_categories);
        if (!is_dir($cache_dir)) {
            mkdir($cache_dir, 0777, true); // Create directory if it doesn't exist
        }
        file_put_contents($cache_file_popular_categories, json_encode($popularCategories));
    }

    return $popularCategories;
}

// function to get popular authors
function getPopularAuthors($limit = 10) {
    global $conn;
    $limit = (int)$limit; // Ensure $limit is an integer

    $popularAuthors = [];
    // IMPORTANT: Adjust path to cache file relative to config.php (which is in 'includes/')
    $cache_file_popular_authors = __DIR__ . '/../cache/popular_authors_cache.json';
    $cache_lifetime = 3600; // Cache for 1 hour

    // Check if cache file exists and is still valid
    if (file_exists($cache_file_popular_authors) && (time() - filemtime($cache_file_popular_authors) < $cache_lifetime)) {
        // Cache is valid, load data from cache
        $popularAuthors = json_decode(file_get_contents($cache_file_popular_authors), true);
    } else {
        // Cache is expired or doesn't exist, fetch from database
        $sql = "SELECT a.id, a.name, a.image, COUNT(b.id) AS book_count
                FROM authors a
                LEFT JOIN books b ON a.id = b.author_id
                WHERE a.is_popular = 1
                GROUP BY a.id, a.name, a.image -- Group by all selected non-aggregate columns
                ORDER BY a.popular_order ASC, a.name ASC
                LIMIT ?";

        $stmt = $conn->prepare($sql);
        if (!$stmt) {
            error_log("Database Error: Failed to prepare statement for getPopularAuthors. MySQL Error: " . $conn->error);
            return []; // Return an empty array on failure
        }

        $stmt->bind_param("i", $limit);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $popularAuthors[] = $row;
            }
        }
        $stmt->close();

        // Save fetched data to cache
        // Ensure the cache directory exists before writing
        $cache_dir = dirname($cache_file_popular_authors);
        if (!is_dir($cache_dir)) {
            mkdir($cache_dir, 0777, true); // Create directory if it doesn't exist
        }
        file_put_contents($cache_file_popular_authors, json_encode($popularAuthors));
    }

    return $popularAuthors;
}

// Function to get video by ID (for a potential video-detail.php page)
function getVideoById($id) {
    global $conn;
    
    $id = (int)$id;
    $sql = "SELECT v.* "; // Add , vc.name as category_name if you implement video categories
    $sql .= "FROM videos v ";
    // if ($video_categories_implemented) {
    //    $sql .= "LEFT JOIN video_categories vc ON v.category_id = vc.id ";
    // }
    $sql .= "WHERE v.id = ?";
    
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        error_log("Get video by ID statement prep failed: " . $conn->error);
        return [];
    }
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    $stmt->close();
    
    if ($result->num_rows > 0) {
        return $result->fetch_assoc();
    }
    
    return null;
}

// Optional: Function to record video view (similar to recordBookView)
function recordVideoView($video_id) {
    global $conn;
    
    $video_id = (int)$video_id;
    // You might want to create a separate 'video_views' table
    // For now, let's assume updating a 'view_count' column in the 'videos' table
    // This is simpler but less detailed than a separate views table.
    
    $stmt = $conn->prepare("UPDATE videos SET view_count = view_count + 1 WHERE id = ?");
    if ($stmt) {
        $stmt->bind_param("i", $video_id);
        $stmt->execute();
        $stmt->close();
    } else {
        error_log("Failed to prepare statement for recordVideoView: " . $conn->error);
    }
}

function countAllVideos()
{
    global $conn; // Use the database connection from config.php
    $sql = "SELECT COUNT(*) as total FROM videos";
    $result = mysqli_query($conn, $sql);
    if ($result && $row = mysqli_fetch_assoc($result)) {
        return (int)$row['total'];
    }
    return 0;
}

// Function to get all active gallery events, optionally with active image count and cover image
function getAllGalleryEvents($limit = null, $offset = 0) {
    global $conn;

    $sql = "SELECT 
                ge.*, 
                COUNT(DISTINCT gi_count.id) as image_count,
                ci.file_name as cover_file_name,      -- Cover image's file_name from gallery_images
                ci.file_path as cover_file_path,      -- Cover image's file_path from gallery_images
                ci.alt_text as cover_alt_text         -- Cover image's alt_text from gallery_images
            FROM 
                gallery_events ge
            LEFT JOIN 
                gallery_images gi_count ON ge.id = gi_count.event_id AND gi_count.is_active = 1 -- For counting images in the event
            LEFT JOIN 
                gallery_images ci ON ge.cover_image_id = ci.id AND ci.is_active = 1          -- For fetching the specific cover image
            WHERE 
                ge.is_active = 1
            GROUP BY 
                ge.id
            ORDER BY 
                ge.display_order ASC, ge.event_date DESC, ge.name ASC";

    $params = [];
    $types = "";

    if ($limit !== null) {
        $sql .= " LIMIT ?, ?";
        $params[] = (int)$offset;
        $params[] = (int)$limit;
        $types .= "ii";
    }

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        error_log("Get all gallery events statement prep failed: " . $conn->error);
        return [];
    }

    if (!empty($types)) {
        $stmt->bind_param($types, ...$params);
    }

    $stmt->execute();
    $result = $stmt->get_result();
    $events = [];
    if ($result && $result->num_rows > 0) { // Added check for $result
        while ($row = $result->fetch_assoc()) {
            $events[] = $row;
        }
    }
    $stmt->close();
    return $events;
}

// Function to count all active gallery events (for pagination)
function countAllGalleryEvents() {
    global $conn;
    $sql = "SELECT COUNT(*) as total FROM gallery_events WHERE is_active = 1";
    $result = $conn->query($sql);
    $row = $result->fetch_assoc();
    return $row ? (int)$row['total'] : 0;
}
Youez - 2016 - github.com/yon3zu
LinuXploit