mysqli num rows gives error on server not localhost

Question

I created a helper function and i use this a lot throughout my code get the following error.

but what i don't understand is I have zend server and my code runs fine on local host. why the grieve on a live server.

Warning: mysqli_ num_rows() expects parameter 1 to be mysqli_result, boolean on line 26

this is line 26

function row_count($result){   
    global $connection;
    return mysqli_num_rows($result);
}

login code

function login_user($email, $password){
    
    $active = 1;
    
    $connection = dbconnect();
    $stmt = $connection->prepare('SELECT user_pwd, user_id, username FROM users WHERE user_email = ? AND active= ?');
    $stmt->bind_param('ss', $email, $active);
    $stmt->execute();
    $result = $stmt->fetch();      
    
    if (row_count($result) == 1) {
        
        $row = fetch_array($result);
        
        $db_password = $row['user_pwd'];
        
        if (password_verify($password, $db_password)) {
            
            $_SESSION['email'] = $email;
            $_SESSION['user_id'] = $row['user_id'];
            $_SESSION['username'] = $row['username'];
            
            return true;
        
        } else {
            
            return false;
        }
        
        return true;
    
    } else {
        
        return false;
    }
}

Rajdeep Paul · Accepted Answer · 2016-10-20T17:06:14.327

Look at this statement here,

$result = $stmt->fetch();

From the documentation of mysqli_stmt::fetch,

                                    Return Values 
    Value    Description
    TRUE     Success. Data has been fetched
    FALSE    Error occurred
    NULL     No more rows/data exists or data truncation occurred

First, ->fetch() doesn't return any result set on success. And second, this method is used to fetch results from a prepared statement into the bound variables, which you're not doing.

The solution is, first use ->store_result() followed by ->num_rows to get total number of rows returned by the SELECT query. And then fetch results from the prepared statement into the bound variables, so that you could use those bound variables later in the code. So your code should be like this:

function login_user($email, $password){
    $active = 1;

    $connection = dbconnect();
    $stmt = $connection->prepare('SELECT user_pwd, user_id, username FROM users WHERE user_email = ? AND active= ?');
    $stmt->bind_param('ss', $email, $active);
    $stmt->execute();
    $stmt->store_result();     

    if ($stmt->num_rows == 1) {
        $stmt->bind_result($db_password, $user_id, $username);
        $stmt->fetch();

        if (password_verify($password, $db_password)) {
            $_SESSION['email'] = $email;
            $_SESSION['user_id'] = $user_id;
            $_SESSION['username'] = $username;
            return true;
        } else {
            return false;
        }
        return true;
    } else {
        return false;
    }
}

Alternatively, you can also use ->get_result() method to achieve the desired result. Your code should be like this:

function login_user($email, $password){
    $active = 1;

    $connection = dbconnect();
    $stmt = $connection->prepare('SELECT user_pwd, user_id, username FROM users WHERE user_email = ? AND active= ?');
    $stmt->bind_param('ss', $email, $active);
    $stmt->execute();
    $result = $stmt->get_result();     

    if ($result->num_rows == 1) {
        $row = $result->fetch_array();
        $db_password = $row['user_pwd'];

        if (password_verify($password, $db_password)) {
            $_SESSION['email'] = $email;
            $_SESSION['user_id'] = $row['user_id'];
            $_SESSION['username'] = $row['username'];
            return true;
        } else {
            return false;
        }
        return true;
    } else {
        return false;
    }
}

Note: ->get_result() method is available only with MySQL Native driver(mysqlnd), so it won't work if you don't have that particular driver installed.

Also, from your question:

but what i don't understand is I have zend server and my code runs fine on local host. why the grieve on a live server.

That's because error reporting is probably turned off on your local server. Add these lines at the very top of your PHP script to turn on error reporting, ini_set('display_errors', 1); error_reporting(E_ALL);. Also, make use of mysqli::$error.

mysqli num rows gives error on server not localhost

1 Answers1