PHP数据库查询无数据时，如何输出指定提示信息？

Hey there! Let's get your code sorted out to meet your requirement—returning the proper message when no data is found, while making sure everything is secure and follows modern PHP practices.

First, let's go over the issues in your current code snippet:

• You're using the deprecated mysql_* functions—these were removed in PHP 7.0, so we'll switch to mysqli_* (MySQL Improved) which is supported and safer.
• Your logic order is off: you're encoding the results to JSON before checking if there's any data, which would lead to mixed output (JSON plus your error string) if no rows are found.
• There's a SQL injection risk since you're directly concatenating $id into your query without escaping it.
• The error message is incomplete (echo "dat..." instead of the full required string).

Here's the revised code that fixes all these issues and meets your requirement:

// Assuming you have an existing mysqli connection (replace with your actual connection details)
$conn = mysqli_connect("localhost", "username", "password", "database_name");

// Check if connection failed
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Use prepared statement to prevent SQL injection
$stmt = mysqli_prepare($conn, "SELECT * FROM geosekolah JOIN kecamatan USING (id_kecamatan) JOIN kelurahan USING (id_kelurahan) WHERE id_sekolah = ?");
mysqli_stmt_bind_param($stmt, "i", $id); // "i" means $id is an integer; use "s" if it's a string
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

$posts = array();
if (mysqli_num_rows($result) > 0) {
    while ($post = mysqli_fetch_assoc($result)) {
        $posts[] = $post;
    }
    // Output JSON if data exists
    echo json_encode(array('results' => $posts));
} else {
    // Output the specified message if no data found
    echo "data not available or already deleted";
}

// Clean up resources
mysqli_stmt_close($stmt);
mysqli_close($conn);

Key Improvements Explained: #

• Prepared Statements: By using mysqli_prepare and binding parameters, we eliminate SQL injection risks—this is critical for keeping your application secure.
• Modern MySQL Extension: mysqli_* is the supported replacement for the old mysql_* functions, ensuring your code works on newer PHP versions.
• Logical Flow: We first check if there are rows returned, then either output the JSON results or the error message—no messy mixed output issues.
• Proper Cleanup: We close the statement and connection to avoid unnecessary resource leaks.

If you prefer using PDO (another popular, flexible database extension), here's an alternative version:

// PDO Connection
$conn = new PDO("mysql:host=localhost;dbname=database_name", "username", "password");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $conn->prepare("SELECT * FROM geosekolah JOIN kecamatan USING (id_kecamatan) JOIN kelurahan USING (id_kelurahan) WHERE id_sekolah = ?");
$stmt->execute([$id]);

$posts = $stmt->fetchAll(PDO::FETCH_ASSOC);

if (!empty($posts)) {
    echo json_encode(array('results' => $posts));
} else {
    echo "data not available or already deleted";
}

PDO is a great choice if you ever need to switch to a different database system later, as it's database-agnostic.

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