使用PHP实现跨post与pcgames表的分类查询结果展示

Got it, let's break this down into actionable steps. We need to check if the game GET parameter is set to action, then pull all matching entries from both the post and pcgames tables, and finally display them. Here's a complete, secure implementation:

Step 1: Database Connection & Parameter Check #

First, we'll set up a PDO database connection (it's more secure and flexible than mysqli) and validate the incoming GET parameter to avoid invalid requests and SQL injection.

<?php
// Database configuration
$host = 'localhost';
$dbname = 'your_database_name';
$username = 'your_db_username';
$password = 'your_db_password';

try {
    // Initialize PDO connection
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Check if the 'game' GET parameter exists and equals 'action'
    if (isset($_GET['game']) && $_GET['game'] === 'action') {
        // SQL query to fetch action category entries from both tables
        $sql = "
            SELECT id, category, game FROM post WHERE category = :category
            UNION ALL
            SELECT id, category, game FROM pcgames WHERE category = :category
        ";

        // Prepare and execute the query with a parameter to prevent SQL injection
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':category' => 'action']);

        // Fetch all results as associative arrays
        $results = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Display the results
        if (!empty($results)) {
            echo "<h2>Action Category Games</h2>";
            echo "<ul>";
            foreach ($results as $row) {
                echo "<li>ID: {$row['id']} | Category: {$row['category']} | Game: {$row['game']}</li>";
            }
            echo "</ul>";
        } else {
            echo "<p>No action category games found.</p>";
        }
    } else {
        echo "<p>Invalid request. Please set the 'game' parameter to 'action'.</p>";
    }
} catch (PDOException $e) {
    die("Database error: " . $e->getMessage());
}
?>

Key Notes: #

• UNION ALL vs UNION: We use UNION ALL here because it's faster (it doesn't waste resources removing duplicate entries). If you want to exclude exact duplicates (e.g., identical game entries across both tables), swap it out for UNION.
• SQL Injection Protection: Using prepared statements with parameter binding ensures user input can't manipulate the SQL query—this is non-negotiable for secure code.
• Error Handling: The try-catch block catches database connection or query errors and displays a human-readable message instead of a cryptic server error.
• Charset: We set charset=utf8mb4 to support all Unicode characters (including emojis or special game titles if needed).

How to Test: #

Run the script with the URL parameter like this: your_script.php?game=action — you'll see all action category games from both tables listed neatly.

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