两段PHP脚本出现Fatal Error，请求协助排查修复

Let's break down the issues in both scripts and get them working properly. I'll walk through the key problems first, then share corrected code with better error handling and security.

1. getscores.php Issues & Fixes #

Key Problems: #

• Mixed MySQL Extensions: You’re using a mix of deprecated mysql_* functions (like mysql_query(), mysql_close()) and modern mysqli_* functions—these can’t work together, which is why the script fails outright.
• No Connection Error Checks: You don’t verify if the database connection succeeded before running queries. If credentials are wrong or the DB is down, the script crashes silently.
• Unused Variable: $tbl_name is defined but never used in your SQL query, creating unnecessary confusion.
• Messy Output: The loop appends a | after every entry, leaving an extra separator at the end of your response.
• No Query Error Handling: If the SQL query fails (e.g., table doesn’t exist, permissions issue), you won’t get any feedback to debug.

Corrected getscores.php Code: #

<?php
header('Access-Control-Allow-Origin: *');

$host = "localhost";
$username = ""; // Fill in your actual DB username
$password = ""; // Fill in your actual DB password
$db_name = ""; // Fill in your actual DB name
$tbl_name = "scores"; // Use your table name here

// Connect to database with explicit error checking
$link = mysqli_connect($host, $username, $password, $db_name);
if (!$link) {
    die("Database connection failed: " . mysqli_connect_error());
}

// Run query with error handling
$sql = "SELECT name, score FROM $tbl_name ORDER BY score DESC LIMIT 10";
$result = mysqli_query($link, $sql);

if (!$result) {
    die("Query failed: " . mysqli_error($link));
}

// Collect results to avoid trailing | separator
$scores = [];
while ($row = mysqli_fetch_assoc($result)) {
    $scores[] = $row['name'] . "|" . $row['score'];
}

// Output clean, formatted results
echo implode("|", $scores);

// Close database connection
mysqli_close($link);
?>

2. savescores.php Issues & Fixes #

Key Problems: #

• Mixed MySQL Extensions: You’re using mysql_real_escape_string() (a deprecated function) alongside mysqli_connect()—they’re incompatible.
• Unnecessary ID Field: If your id column is set to auto-increment (which it should be for primary keys), passing an empty value in the INSERT query can cause errors.
• Weak Security: Manual input sanitization is error-prone; prepared statements are a far more reliable defense against SQL injection.
• No Connection Error Checks: No verification that the database connection succeeded before proceeding.
• Redundant DB Name in SQL: Since you already selected the database when connecting, you don’t need to prepend $db. to the table name.

Corrected savescores.php Code (with Prepared Statements): #

<?php
header('Access-Control-Allow-Origin: *'); // Add CORS header for frontend access

$host = "localhost";
$dbu = ""; // Fill in your actual DB username
$dbp = ""; // Fill in your actual DB password
$db = ""; // Fill in your actual DB name
$tbl_name = "scores";

// Connect to database with error checking
$dblink = mysqli_connect($host, $dbu, $dbp, $db);
if (!$dblink) {
    die("Database connection failed: " . mysqli_connect_error());
}

if (isset($_GET['name']) && isset($_GET['score'])) {
    // Sanitize input and enforce data types
    $name = strip_tags(trim($_GET['name']));
    $score = (int) $_GET['score']; // Cast score to integer to ensure valid numeric input

    // Use prepared statement to prevent SQL injection
    $stmt = mysqli_prepare($dblink, "INSERT INTO $tbl_name (name, score) VALUES (?, ?)");
    mysqli_stmt_bind_param($stmt, "si", $name, $score);

    if (mysqli_stmt_execute($stmt)) {
        echo 'Your score was saved. Congrats!';
    } else {
        echo 'There was a problem saving your score. Error: ' . mysqli_stmt_error($stmt);
    }

    mysqli_stmt_close($stmt);
} else {
    echo 'Your name or score wasn\'t passed in the request. Make sure you add ?name=NAME_HERE&score=1337 to the URL.';
}

// Close database connection
mysqli_close($dblink);
?>

Quick Additional Tips: #

• Fill in Credentials: Don’t forget to add your actual database username, password, and database name in both scripts.
• Auto-Increment ID: Double-check that your scores table’s id column is set to AUTO_INCREMENT (this is standard for primary keys).
• Debugging: For temporary debugging, add error_reporting(E_ALL); ini_set('display_errors', 1); at the top of each script to see PHP errors.

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