Diagnosing and Fixing mysqli_num_rows() Parameter Errors in PHP: From Boolean to mysqli_result Conversion

Error Phenomenon and Background Analysis

In PHP and MySQL integrated development, developers frequently encounter the error message 'mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given'. This error typically occurs when a database query execution fails, causing mysqli_query function to return a boolean false instead of the expected mysqli_result object. When this false value is passed to mysqli_num_rows function, it triggers a type error warning.

Case Study Detailed Analysis

Consider this typical scenario: a developer attempts to integrate HTML Purifier to filter user-submitted data but encounters the aforementioned error during database operations. The core issue appears at line 22's conditional check:

if (mysqli_num_rows($dbc) == 0) {
    // Perform insert operation
}

The $dbc variable here should contain the SELECT query's result set, but actually contains a boolean false. This indicates that the preceding SQL query execution failed.

Root Cause Diagnosis

Through careful analysis of the original code's SQL query, we identify a critical structural issue:

SELECT users.*, profile.*
FROM users 
INNER JOIN contact_info ON contact_info.user_id = users.user_id 
WHERE users.user_id=3

This query contains an obvious logical flaw: it references profile.* in the SELECT clause, but doesn't include the profile table in either the FROM clause or JOIN conditions. This incomplete table reference causes SQL syntax errors, making mysqli_query return false instead of a valid result set.

Comprehensive Solution

To completely resolve this issue, improvements are needed at multiple levels:

1. SQL Query Repair

First, correct the logical errors in the SQL query. Based on the database table structure, the correct query should be:

SELECT users.*, profile.*
FROM users 
INNER JOIN contact_info ON contact_info.user_id = users.user_id
INNER JOIN profile ON profile.user_id = users.user_id
WHERE users.user_id = 3

Or if only users table data is needed:

SELECT users.*
FROM users 
INNER JOIN contact_info ON contact_info.user_id = users.user_id
WHERE users.user_id = 3

2. Robust Error Handling Mechanism

Before calling mysqli_num_rows, always verify whether the query executed successfully:

$dbc = mysqli_query($mysqli, $sql);
if ($dbc === false) {
    // Query execution failed, handle error
    echo "Query error: " . mysqli_error($mysqli);
    return;
}

// Only check row count when query succeeds
if (mysqli_num_rows($dbc) == 0) {
    // Perform insert operation
}

3. Improved Conditional Logic

Incorporating suggestions from Answer 2, a safer conditional check approach can be adopted:

if (!$dbc || mysqli_num_rows($dbc) == 0) {
    // Handle query failure or no results scenario
}

This approach first checks if the query failed (!$dbc), then checks the result set row count only if the query succeeded.

Best Practice Recommendations

Database Connection Management

In the original code, database connections are repeatedly created, leading to resource waste and potential performance issues. Database connections should be extracted to functions or class constructors:

function getDBConnection() {
    static $connection = null;
    if ($connection === null) {
        $connection = mysqli_connect("localhost", "root", "", "sitename");
        if (!$connection) {
            die("Database connection failed: " . mysqli_connect_error());
        }
    }
    return $connection;
}

Parameterized Queries and Security Protection

Although the code uses HTML Purifier for input filtering, SQL injection risks still exist. Prepared statements are recommended:

$stmt = $mysqli->prepare("INSERT INTO profile (user_id, about_me, interests) VALUES (?, ?, ?)");
$stmt->bind_param("iss", $user_id, $about_me, $interests);
$stmt->execute();

Complete Error Handling Process

Establish a layered error handling mechanism:

try {
    // Database connection
    $mysqli = mysqli_connect("localhost", "root", "", "sitename");
    if (!$mysqli) {
        throw new Exception("Database connection failed: " . mysqli_connect_error());
    }
    
    // Execute query
    $dbc = mysqli_query($mysqli, $sql);
    if ($dbc === false) {
        throw new Exception("Query execution failed: " . mysqli_error($mysqli));
    }
    
    // Process results
    if (mysqli_num_rows($dbc) == 0) {
        // Insert logic
    }
    
} catch (Exception $e) {
    error_log($e->getMessage());
    // User-friendly error message
    echo "Operation failed, please try again later";
}

Related Technical Extensions

HTML Purifier Integration Optimization

When integrating HTML Purifier, further configuration optimization is possible:

$config = HTMLPurifier_Config::createDefault();
$config->set('Core.Encoding', 'UTF-8');
$config->set('HTML.Doctype', 'XHTML 1.0 Strict');
$config->set('HTML.Allowed', 'p,br,strong,em'); // Limit allowed HTML tags
$purifier = new HTMLPurifier($config);

Performance Considerations

For frequent database operations, consider using connection pools and query caching. Also, avoid executing database queries within loops and prefer batch operations when possible.

Conclusion

The core issue behind mysqli_num_rows() parameter errors lies in the failure to properly handle database query execution failures. Through systematic error diagnosis, SQL query optimization, and comprehensive error handling mechanisms, this problem can be completely resolved. Developers should develop the habit of verifying query execution status before calling any database result set functions, which not only avoids similar type errors but also enhances code robustness and maintainability.

In practical development, adopting object-oriented database operation approaches using PDO or ORM tools is recommended, as they provide more complete error handling mechanisms and safer data operation methods. Additionally, establishing a unified error logging system facilitates problem tracking and debugging.