将MySQL分类查询逻辑封装为可复用PHP函数的技术问询

Got it! Your current code has a lot of repetitive logic—every time you add a new category, you have to copy-paste the same query and HTML-building steps. Plus, directly embedding the category name into your SQL string creates a SQL injection risk. Let's fix that by wrapping everything into a clean, reusable function that's both efficient and secure.

Step-by-Step Refactored Solution #

1. The Reusable Function #

This function takes a category name and your database connection, runs the safe query, and returns the pre-built HTML string:

function getCategoryFeed(string $category, mysqli $connection): string {
    $feedHtml = '';
    
    // Use a prepared statement to avoid SQL injection
    $sql = "SELECT id, title, description FROM feeds WHERE category = ? ORDER BY id DESC LIMIT 5";
    $stmt = mysqli_prepare($connection, $sql);
    
    if (!$stmt) {
        die(mysqli_error($connection));
    }
    
    // Bind the category parameter (the 's' means we're passing a string)
    mysqli_stmt_bind_param($stmt, 's', $category);
    mysqli_stmt_execute($stmt);
    
    // Bind result columns to variables
    mysqli_stmt_bind_result($stmt, $fid, $title, $description);
    
    // Loop through results and build HTML
    while (mysqli_stmt_fetch($stmt)) {
        // Escape output to prevent XSS attacks (critical for user-generated content)
        $safeId = htmlspecialchars($fid, ENT_QUOTES);
        $safeTitle = htmlspecialchars($title, ENT_QUOTES);
        $safeDescription = htmlspecialchars($description, ENT_QUOTES);
        
        // Build the HTML elements (no need for < entities now that we escape properly)
        $feedHtml .= "<h2><b><a href='detail/{$safeId}/{$safeTitle}' title='{$safeTitle}'>{$safeTitle}</a></b></h2>";
        $feedHtml .= "<a href='detail/{$safeId}/{$safeTitle}' title='{$safeTitle}'>{$safeDescription}</a><br/>";
    }
    
    // Clean up resources
    mysqli_stmt_close($stmt);
    
    return $feedHtml;
}

2. How to Use the Function #

Now you can get feeds for any category with just one line of code—no more copy-pasting:

// Get Sport category feed
$sportFeed = getCategoryFeed('Sport', $myConnection);

// Get Cars category feed
$carsFeed = getCategoryFeed('Cars', $myConnection);

// Get Books category feed
$booksFeed = getCategoryFeed('Books', $myConnection);

Key Improvements Explained #

• Reusability: Define the logic once, call it for any category. Adding a new category takes 1 line of code instead of 15+.
• Security:
  • Prepared statements eliminate SQL injection risks (no more directly concatenating user-provided values into SQL).
  • htmlspecialchars() escapes output to prevent XSS attacks—critical if your feed titles/descriptions come from user input.
• Cleaner Code: All repetitive logic lives in one place, making your code easier to read, maintain, and debug.
• Type Hints: The function uses type hints for parameters and return values, so your IDE can catch errors early and you know exactly what to pass/expect.

Quick Note for Fuzzy Matches #

If you originally used LIKE instead of = for partial matches (e.g., category LIKE '%Sport%'), you can adjust the SQL to:

$sql = "SELECT id, title, description FROM feeds WHERE category LIKE ? ORDER BY id DESC LIMIT 5";

Then pass the wildcard with the category when calling the function:

$sportFeed = getCategoryFeed('%Sport%', $myConnection);

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