Hey there, let's break down the issues in your current listproducts function and fix it up to meet your requirements—including support for pagination and front-end sorting with jQuery. Here's a step-by-step breakdown and revised code:

🔍 Identified Issues in the Original Code #

• Invalid Default Parameter Syntax: PHP doesn't allow function parameter default values to be dynamic expressions (like calling filter_var() directly in the parameter list). This will throw a syntax error immediately.
• SQL Injection Vulnerability: You're directly interpolating user input into your SQL query—even with filter_var, this isn't safe for SQL use. Always use prepared statements to avoid injection risks.
• Incomplete Subquery: Your subquery select cat_id f... is cut off; you need proper logic to fetch all child categories for a parent ID.
• Tight Coupling with Global Connection: Using global $conn makes your function dependent on a global variable, which hurts reusability and testing.
• No "All Categories" Support: Your code doesn't handle fetching products across every category.
• Missing Pagination & Sorting Hooks: There's no way to add LIMIT/OFFSET for pagination or ORDER BY for sorting, which you need for the jQuery filter functionality.

✅ Revised Function with Fixes & Enhancements #

function listproducts($conn, $categoryId = null, $page = 1, $sortBy = 'created_at', $sortDir = 'DESC') {
    // Sanitize input parameters
    $categoryId = $categoryId ? filter_var($categoryId, FILTER_SANITIZE_NUMBER_INT) : null;
    $page = max(1, filter_var($page, FILTER_SANITIZE_NUMBER_INT));
    
    // Whitelist allowed sort fields to prevent injection
    $allowedSortFields = ['price', 'created_at', 'product_name'];
    $sortBy = in_array($sortBy, $allowedSortFields) ? $sortBy : 'created_at';
    $sortDir = strtoupper($sortDir) === 'ASC' ? 'ASC' : 'DESC';
    
    // Pagination setup
    $itemsPerPage = 12; // Adjust to your desired per-page count
    $offset = ($page - 1) * $itemsPerPage;
    
    // Base SQL query
    $sql = "SELECT p.*, c.cat_name 
            FROM products p 
            LEFT JOIN categories c ON p.cat_id = c.cat_id";
    
    // Handle category filtering (including child categories)
    if ($categoryId) {
        // Fetch all child category IDs (including the parent itself)
        $categoryIds = [$categoryId];
        $getChildCatsStmt = $conn->prepare("SELECT cat_id FROM categories WHERE parent_id = ?");
        $getChildCatsStmt->bind_param("i", $categoryId);
        $getChildCatsStmt->execute();
        $childCatsResult = $getChildCatsStmt->get_result();
        
        while ($row = $childCatsResult->fetch_assoc()) {
            $categoryIds[] = $row['cat_id'];
            // Add recursive logic here if you have multi-level nested categories
        }
        $getChildCatsStmt->close();
        
        // Sanitize IDs and add to query
        $safeCategoryIds = implode(',', array_map('intval', $categoryIds));
        $sql .= " WHERE p.cat_id IN ($safeCategoryIds)";
    }
    
    // Add sorting
    $sql .= " ORDER BY $sortBy $sortDir";
    
    // Add pagination
    $sql .= " LIMIT ? OFFSET ?";
    
    // Prepare and execute main query
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("ii", $itemsPerPage, $offset);
    $stmt->execute();
    $productsResult = $stmt->get_result();
    
    // Fetch products into array
    $products = [];
    while ($product = $productsResult->fetch_assoc()) {
        $products[] = $product;
    }
    
    // Get total items count for pagination links
    $countSql = "SELECT COUNT(*) AS total FROM products";
    if ($categoryId) {
        $countSql .= " WHERE cat_id IN ($safeCategoryIds)";
    }
    $countResult = $conn->query($countSql);
    $totalItems = $countResult->fetch_assoc()['total'];
    $totalPages = ceil($totalItems / $itemsPerPage);
    
    // Clean up resources
    $stmt->close();
    
    // Return products + pagination metadata
    return [
        'products' => $products,
        'pagination' => [
            'currentPage' => $page,
            'totalPages' => $totalPages,
            'totalItems' => $totalItems
        ]
    ];
}

📋 Key Improvements Explained #

• Valid Input Handling: Moved sanitization inside the function (no more invalid default parameter syntax).
• SQL Injection Protection: Used prepared statements for all dynamic query parts, plus whitelisted sort fields to block malicious input.
• Child Category Support: Added logic to fetch all child categories for a parent ID (extend with recursion if you have deep nested categories).
• All Categories Support: If $categoryId is null, the query fetches every product across all categories.
• Pagination Ready: Includes $page parameter and calculates LIMIT/OFFSET, plus returns total items/pages to build pagination links.
• Sorting Support: Accepts $sortBy and $sortDir parameters, with safeguards to ensure only allowed fields are used.

🎯 Integrating with jQuery Sorting & Pagination #

To connect this function to your front-end filters, set up an AJAX endpoint and add jQuery handlers:

1. Front-end jQuery Code #

// Fetch products with applied filters
function loadProducts(categoryId = null, page = 1, sortBy = 'created_at', sortDir = 'DESC') {
    $.ajax({
        url: 'product-fetch.php',
        method: 'GET',
        data: {
            category: categoryId,
            page: page,
            sort_by: sortBy,
            sort_dir: sortDir
        },
        dataType: 'json',
        success: function(response) {
            // Render products
            let productHtml = '';
            response.products.forEach(product => {
                productHtml += `
                    <div class="product-card">
                        <h4>${product.product_name}</h4>
                        <p class="price">$${product.price}</p>
                        <p class="date">Added: ${new Date(product.created_at).toLocaleDateString()}</p>
                    </div>
                `;
            });
            $('#products-grid').html(productHtml);
            
            // Render pagination links
            let paginationHtml = '';
            for (let i = 1; i <= response.pagination.totalPages; i++) {
                const activeClass = i === response.pagination.currentPage ? 'active' : '';
                paginationHtml += `<a href="#" class="page-btn ${activeClass}" data-page="${i}">${i}</a>`;
            }
            $('#pagination-controls').html(paginationHtml);
            
            // Pagination click handler
            $('.page-btn').click(function(e) {
                e.preventDefault();
                loadProducts(categoryId, $(this).data('page'), sortBy, sortDir);
            });
        }
    });
}

// Initialize with default values
loadProducts();

// Sort button handlers
$('#sort-price-low').click(() => loadProducts(null, 1, 'price', 'ASC'));
$('#sort-price-high').click(() => loadProducts(null, 1, 'price', 'DESC'));
$('#sort-newest').click(() => loadProducts(null, 1, 'created_at', 'DESC'));
$('#sort-oldest').click(() => loadProducts(null, 1, 'created_at', 'ASC'));

2. PHP Endpoint (product-fetch.php) #

<?php
// Include your database connection file
require 'db-connection.php';

// Get request parameters
$category = isset($_GET['category']) ? $_GET['category'] : null;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$sortBy = isset($_GET['sort_by']) ? $_GET['sort_by'] : 'created_at';
$sortDir = isset($_GET['sort_dir']) ? $_GET['sort_dir'] : 'DESC';

// Call the product list function
$productData = listproducts($conn, $category, $page, $sortBy, $sortDir);

// Return JSON response
header('Content-Type: application/json');
echo json_encode($productData);

$conn->close();
?>

内容的提问来源于stack exchange，提问作者ahmed