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;
    }
}

Your query is failing, most likely because your DB connection is failing. — WillardSolutions, Oct 20 '16 at 15:43

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.

@rajdeep-paulif you look at my code up top you will see the line $row = fetch_array($result); as the first.. my question is why no error thrown on local but on live? — Case, Oct 20 '16 at 16:17
@Case 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);`. — Rajdeep Paul, Oct 20 '16 at 16:23
@Case I've updated my answer. I've added more explanation(and code), plus one alternative approach to solve your problem. Hope this will help you. — Rajdeep Paul, Oct 20 '16 at 16:55
@rajdeep-paulif thanks for your explanation it is greatly appreciated i did the error reporting on my zend server localhost it displayed nothing and in zend studio it gave all green ok — Case, Oct 20 '16 at 17:01
@Case Not sure, but do make use of [mysqli::$error](http://php.net/manual/en/mysqli.error.php) — Rajdeep Paul, Oct 20 '16 at 17:06

mysqli num rows gives error on server not localhost

1 Answers1