view works fine, but return 0 rows when call in php

Question

I created folowing view (select list of last active users):

SELECT U.login, U.name, U.surname
FROM sessions S LEFT JOIN user U 
ON S.id_user = U.id_user 
WHERE U.id_user != 0 AND UNIX_TIMESTAMP()-S.set_time < 300
ORDER BY S.set_time DESC

SELECT *FROM vonline; gives me(i call it in phpmyadmin):

login   name    surname
admin   Chuck   Norris
user2   John    Cena

I am trying to get same output in php:

if ($stmt = $mysqli->prepare("SELECT * FROM vonline")) {
    $stmt->execute();

    $result = $stmt->get_result();
    echo "Online users: $stmt->num_rows"; // This shows "0"
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        echo "Login: $row[1] Name: $row[1] Surname: $row[2]";
    }

} else {
    echo "Select Error";
}

Why get no results, num_rows returns 0.

The code above works perfectly for selecting data from other tables, but not for this one.

Answer 1

Firstly, you're passing your code inside a string literal, rather than an executable.

echo "Online users: $stmt->num_rows"; // This shows "0"

That should read as

echo "Online users: " . $stmt->num_rows;

and using concatenation.

Example from the manual:

if ($result = $mysqli->query("SELECT Code, Name FROM Country ORDER BY Name")) {

    /* determine number of rows result set */
    $row_cnt = $result->num_rows;

    printf("Result set has %d rows.\n", $row_cnt);

    /* close result set */
    $result->close();
}

• http://php.net/manual/en/mysqli-result.num-rows.php

• Change $stmt->num_rows to $result->num_rows which I feel you are using the wrong variable. You need to use the variable on the "result" and the get_result() function.

However, get_result() may not be available for you to use.

Consult this Q&A on Stack:

• What's wrong with mysqli::get_result?

• As well as the manual http://www.php.net/manual/en/mysqli-stmt.get-result.php

Also make sure you're using the same MySQL API to connect with as your query.

Check for errors also against your db and query:

• http://php.net/manual/en/mysqli.error.php

---

Edit:

This does not help you if there are errors in your query:

else {
    echo "Select Error";
}

Change $stmt->execute(); to read as

if(!$stmt->execute()){
    trigger_error("there was an error....".$mysqli->error, E_USER_WARNING);
    }

Or use

echo("Error description: " . mysqli_error($mysqli)); 

in your else.

Plus, make sure you did select the right "database".