Return row count from mysqli prepared statement

Question

I want to know if it is okay/safe to use a prepared statement and mysqli_num_rows like this:

public function getUnreadNumber() {
    $userLoggedIn = $this->user_obj->getUsername();

    // get_result for * and bind for select columns
    // bind_result Doesn't work with SQL query that use *
    $query = $this->con->prepare('SELECT * FROM notifications WHERE viewed="0" AND user_to = ? ');
    $query->bind_param("s", $userLoggedIn);
    $query->execute();
    $query_result = $query->get_result();

    return mysqli_num_rows($query_result);
}

Or should I do this?

$query = $this->con->prepare('SELECT * FROM notifications WHERE viewed="0" AND user_to = ? ');
$query->bind_param("s", $userLoggedIn);
$query->execute();
$query_result = $query->get_result();
$numRows = $query_result->num_rows;

return $numRows;

Answer 1

You should definitely not be mixing procedural and object-oriented syntax.

Although it works with un-mixed syntax, the process is working harder than it needs to and delivering more result set data than you intend to use.

I would add COUNT(1) or COUNT(*) to the sql like this:

$sql = "SELECT COUNT(1) FROM notifications WHERE viewed='0' AND user_to = ?";
$query = $this->con->prepare($sql);
$query->bind_param("s", $userLoggedIn);
$query->execute();
$query->bind_result($count);
$query->fetch();
return $count;

Assuming the sql is not broken due to syntax errors, this will always return a number.

Answer 2

@mickmackusa is correct, you should never ever use num_rows to count the rows in the database, it could kill your database server. This function is rather useless for any other use case too.

Besides, always follow the rule of thumb: make a database to do the job. If you need tell a database to give you the count.

As a code improvement, let me suggest you a mysqli helper function I wrote to put all the prepare/bind/execute business under the hood

public function getUnreadNumber()
{
    $userLoggedIn = $this->user_obj->getUsername();
    $sql = "SELECT COUNT(1) FROM notifications WHERE viewed='0' AND user_to = ?";
    return prepared_query($this->con, $sql, $userLoggedIn)->get_result()->fetch_row()[0];
}