CODEWITHSUNDEEP
phpmysqli
Jan
Jan

Reputation: 39

prepared statement count numbers of returning rows

I want to display the total count of users and users being online. However, there are two simple queries for that:

SELECT id FROM users;

and

SELECT id FROM users WHERE online=1;

And this is my code for the first statement

if($stmt = $mysqli->prepare("SELECT id FROM users"))
    {
        $stmt->execute();
        $stmt->fetch();
        $totalcount = $stmt->num_rows;
        $stmt->close();
        $stmt = null;

    }

for the second:

if($stmt = $mysqli->prepare("SELECT id FROM users WHERE online=?"))
    {
        $requ = 1;
        $stmt->bind_param("i", $requ);
        $stmt->execute();
        $stmt->bind_result($onlineUsers);
        $stmt->fetch();
        $count = $stmt->num_rows;
        $stmt->close();
        $stmt = null;

    }

It just returns 0 as total count of users and online users. How can you perform such a check?

Upvotes: 2

Views: 1223

Answers (2)

Gilbert
Gilbert

Reputation: 1

if($stmt = $mysqli->prepare("SELECT COUNT(id) FROM users WHERE online=?"))



 $requ = 1;
    $stmt->bind_param("i", $requ);
    $stmt->execute();
    $stmt->bind_result($onlineUsers);
    $stmt->fetch();
    echo $onlineUsers;
    $stmt->close();
    $stmt = null;

Upvotes: 0

Barmar
Barmar

Reputation: 782107

Instead of selecting all the rows, do a SELECT COUNT(*) query so that the count is returned as the result. In fact, you can combine the two queries into one:

if ($stmt = $mysqli->prepare("SELECT COUNT(*), SUM(online = ?) FROM users") {
    $requ = 1;
    $stmt->bind_param("i", $requ);
    $stmt->execute();
    $stmt->bind_result($totalcount, $count);
    $stmt->fetch();
    $stmt->close();
    $stmt = null;
}

Upvotes: 1

Related Questions