PHP/MySQL统计含指定名称的数据库表数量及随机选表需求

Hey there! Let's break down how to solve your two core requirements using PHP and MySQL. I'll walk you through each step with practical, actionable code examples tailored to your setup.

1. Count Tables Named with "Puzzle" Prefix #

To get the exact number of tables starting with "Puzzle" (or lowercase "puzzle"—I’ll note case sensitivity below) in your puzzleGame database, we’ll query MySQL’s built-in INFORMATION_SCHEMA.TABLES system table. This is the most reliable way to fetch metadata about your database tables.

MySQL Query #

SELECT COUNT(*) AS puzzle_table_count
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'puzzleGame'
  AND TABLE_NAME LIKE 'puzzle%'; -- Use 'Puzzle%' if your tables use uppercase prefixes

Note: If your MySQL server runs on Windows (where lower_case_table_names is often set to 1), table names are case-insensitive, so 'puzzle%' and 'Puzzle%' will work interchangeably. On Linux/macOS with case-sensitive file systems, make sure the pattern matches your actual table name casing (your existing tables are puzzle1-puzzle4, so 'puzzle%' is correct here).

PHP Implementation #

Here’s how to execute this query and retrieve the count in PHP using PDO (the recommended database extension):

<?php
$host = 'your_db_host';
$dbname = 'puzzleGame';
$username = 'your_db_user';
$password = 'your_db_pass';

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

    // Execute count query
    $stmt = $pdo->query("SELECT COUNT(*) AS puzzle_table_count FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'puzzleGame' AND TABLE_NAME LIKE 'puzzle%'");
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    $totalPuzzleTables = $result['puzzle_table_count'];

    echo "Total Puzzle tables in database: " . $totalPuzzleTables;
} catch(PDOException $e) {
    die("Error counting tables: " . $e->getMessage());
}
?>

2. Random Table Selection with No Repeats (Until Reset) #

To implement a system where you randomly select a Puzzle table without repeats until all are used (then reset the pool), you’ll need to maintain two lists:

• Available tables: The pool of tables not yet selected in the current game cycle
• Used tables: Tables already selected (for reference, if needed)

Below are two common approaches, depending on whether you need session-specific or persistent cross-session state.

Option 1: Use PHP Sessions (Per-Session State) #

Perfect if each game session is isolated to a single admin/user. This keeps state stored in the user’s browser session.

<?php
session_start();
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Initialize available puzzles if the session doesn't have them yet
if (!isset($_SESSION['available_puzzles'])) {
    $stmt = $pdo->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'puzzleGame' AND TABLE_NAME LIKE 'puzzle%'");
    $_SESSION['available_puzzles'] = $stmt->fetchAll(PDO::FETCH_COLUMN);
    shuffle($_SESSION['available_puzzles']); // Pre-shuffle for random order
}

// Select a random puzzle from the available pool
if (!empty($_SESSION['available_puzzles'])) {
    $selectedTable = array_pop($_SESSION['available_puzzles']); // Remove from available pool
    $_SESSION['used_puzzles'][] = $selectedTable; // Optional: Track used tables
    echo "Selected Puzzle table: " . $selectedTable;
} else {
    // Reset the pool when all tables are used
    echo "All Puzzle tables have been used! Resetting pool...";
    unset($_SESSION['available_puzzles'], $_SESSION['used_puzzles']);
    // Optional: Redirect or re-run selection to get the first new table
}
?>

Option 2: Store State in globals Table (Persistent Cross-Session State) #

Use this if you need the selection state to persist across admin sessions or be shared between multiple users. We’ll use JSON fields in your existing globals table to store the lists.

First, update your globals table to add the required fields:

ALTER TABLE globals 
ADD COLUMN available_puzzles JSON,
ADD COLUMN used_puzzles JSON;

Then use this PHP code to manage the selection and state:

<?php
$pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8mb4", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Fetch current state from globals table
$stmt = $pdo->query("SELECT available_puzzles, used_puzzles FROM globals LIMIT 1");
$globalState = $stmt->fetch(PDO::FETCH_ASSOC);

$availablePuzzles = json_decode($globalState['available_puzzles'], true);
$usedPuzzles = json_decode($globalState['used_puzzles'], true) ?? [];

// Initialize available puzzles if empty
if (empty($availablePuzzles)) {
    $stmt = $pdo->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'puzzleGame' AND TABLE_NAME LIKE 'puzzle%'");
    $availablePuzzles = $stmt->fetchAll(PDO::FETCH_COLUMN);
    shuffle($availablePuzzles);
}

// Select a puzzle and update state
if (!empty($availablePuzzles)) {
    $selectedTable = array_pop($availablePuzzles);
    $usedPuzzles[] = $selectedTable;

    // Update globals table with new state
    $updateStmt = $pdo->prepare("UPDATE globals SET available_puzzles = ?, used_puzzles = ?");
    $updateStmt->execute([json_encode($availablePuzzles), json_encode($usedPuzzles)]);

    echo "Selected Puzzle table: " . $selectedTable;
} else {
    // Reset state when all tables are used
    echo "All Puzzle tables have been used! Resetting pool...";
    $stmt = $pdo->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'puzzleGame' AND TABLE_NAME LIKE 'puzzle%'");
    $newAvailablePool = $stmt->fetchAll(PDO::FETCH_COLUMN);
    shuffle($newAvailablePool);

    $updateStmt = $pdo->prepare("UPDATE globals SET available_puzzles = ?, used_puzzles = ?");
    $updateStmt->execute([json_encode($newAvailablePool), json_encode([])]);
}
?>

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