自制带IP信息的页面访问计数器报错：mysqli_num_rows参数异常

Hey there! That error is a super common pitfall when working with MySQLi—let's break down why it's happening and how to fix it quickly.

Why You're Seeing This Error #

The message Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given means your query didn't return a valid result set. Instead, it returned false, which tells us the query failed to execute properly. Common reasons for this include:

• A syntax error in your SQL query (typos in table/column names, missing quotes, etc.)
• A failed database connection
• Insufficient permissions to access the table/database
• Using mysqli_num_rows() on a non-SELECT query (like UPDATE/INSERT, which return a boolean count of affected rows, not a result set)

Step-by-Step Fixes #

1. First, Validate Your Database Connection

Before running any queries, make sure your connection to the database is actually working. Add this check right after your connection code:

// Connect to server and database
$conn = mysqli_connect($host, $username, $password, $db_name);

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

This will immediately tell you if there's an issue with your host, username, password, or database name.

2. Check Query Execution Before Using mysqli_num_rows()

Never assume your query worked—always verify the result before passing it to mysqli_num_rows(). Here's how to adjust your code for your IP counter:

// Get visitor's IP address
$user_ip = $_SERVER['REMOTE_ADDR'];

// Example SELECT query to check if IP exists
$query = "SELECT * FROM $tbl_name WHERE ip = '$user_ip'";
$result = mysqli_query($conn, $query);

// Check if the query returned a valid result
if ($result === false) {
    die("Query failed: " . mysqli_error($conn)); // This shows the exact SQL error
}

// Now safely use mysqli_num_rows()
$ip_exists = mysqli_num_rows($result) > 0;

if ($ip_exists) {
    // Update hit count for existing IP
    $update_query = "UPDATE $tbl_name2 SET hits = hits + 1 WHERE ip = '$user_ip'";
    mysqli_query($conn, $update_query);
} else {
    // Insert new IP into both tables
    $insert_ip = "INSERT INTO $tbl_name (ip) VALUES ('$user_ip')";
    $insert_hit = "INSERT INTO $tbl_name2 (ip, hits) VALUES ('$user_ip', 1)";
    mysqli_query($conn, $insert_ip);
    mysqli_query($conn, $insert_hit);
}

The mysqli_error($conn) call is crucial here—it will print the exact reason your query failed (like a typo in $tbl_name or a missing ip column), making debugging a breeze.

Bonus: Protect Against SQL Injection (Best Practice) #

While $_SERVER['REMOTE_ADDR'] is safe since it's controlled by the server, if you ever add user-inputted values to your queries, use prepared statements to avoid SQL injection. Here's a quick example for your SELECT query:

$stmt = mysqli_prepare($conn, "SELECT * FROM $tbl_name WHERE ip = ?");
mysqli_stmt_bind_param($stmt, "s", $user_ip);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

$ip_exists = mysqli_num_rows($result) > 0;

This is more secure and avoids issues with special characters in input.

Summary #

• Always validate your database connection first
• Check if your query returned a valid result before using mysqli_num_rows()
• Use mysqli_error($conn) to debug failed queries
• Use prepared statements for user-inputted data to stay secure

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