..->num_rows showing wrong result

Question

I have created a function in PHP that checks if a record is existing in the database or not. The query I am using to get the row is:

SELECT COUNT(*) AS Total, Conversation_ID FROM conversation WHERE User_ID = 3 AND With_User_ID = 4

Now, when I run this query manually into my sql tab of phpmyadmin, I get this: result_of_running_query

The image shows that there is one row in the database that matches the conditions of my query which is perfectly fine.

Now, here's my PHP function:

public static function CheckIfConversationExists($userid, $withuserid) {
    if(Utilities::IsValid($withuserid) && Utilities::IsValid($userid)) {
        Database::OpenConnection();

        $userId = Utilities::SafeString(Database::$databaseConnection, $userid);
        $withUserId = Utilities::SafeString(Database::$databaseConnection, $withuserid);

        $query = Database::$databaseConnection->prepare("SELECT COUNT(*) AS Total, Conversation_ID FROM conversation WHERE User_ID = ? AND With_User_ID = ?");
        $query->bind_param("ii", $userid, $withuserid);
        $result = $query->execute();
        $query->bind_result($total, $conversationid);
        if($result && $query->num_rows > 0) {
            $id = array("Conversation_ID"=>"{$conversationid}");
            return json_encode($id);
        } else {
            return -1;
        }
        Database::CloseConnection();
    }
}

The problem is that when I echo $query->num_rows the result I get is 0 and because of this my function is returning -1 all the time. However, as you can see that the same query that I run in the database gives me one row.

Now to clear you, you would ask that if the values in the function are correct. Yes they are, I've checked them thrice by echoing them on every possible place I could and the result I got was the same which is 3 and 4.

Can you please help me?

Your approach is wrong, `SELECT COUNT(*)` will always return 1 row for a successful query. You should fetch a row and check the value when you have your other problems sorted. — jeroen, Dec 30 '13 at 17:54
@MohammadAreebSiddiqui Then, can you include details of what `Utilities::SafeString()` is doing and what effect it has on the values of `$userId` and `$withUserId`? — Jonathan Lonowski, Dec 30 '13 at 17:59
Add error handling to your database calls. The problem is both somewhere else and in the approach. When you add error handling you'll see where the problem is and then you need to fetch a row as `SELECT COUNT(*)` will always return 1 row exactly. — jeroen, Dec 30 '13 at 17:59

score 1 · Answer 1 · answered Dec 30 '13 at 17:57

Returns the number of rows in the result set. The use of mysqli_stmt_num_rows() depends on whether or not you used mysqli_stmt_store_result() to buffer the entire result set in the statement handle.

http://www.php.net/manual/en/mysqli-stmt.num-rows.php

It counts the results stored with mysqli_stmt_store_result(),not the actual rows.

Add $query->store_result(); before.Also read the first comment,its exactly your issue.

..->num_rows showing wrong result

1 Answers1