num_rows is 0 when it should be >0 for php mysqli code

Question

My num_rows is coming back as 0, and I've tried calling it several ways, but I'm stuck. Here is my code:

    $conn = new mysqli($dbserver, "dbuser", "dbpass", $dbname);

    // get the data 
    $sql = 'SELECT AT.activityName, AT.createdOn
            FROM userActivity UA, users U, activityType AT  
            WHERE U.userId = UA.userId
            and AT.activityType = UA.activityType
            and U.username = ?
            order by AT.createdOn';             
    $stmt = $conn->stmt_init();
    $stmt->prepare($sql);
    $stmt->bind_param('s', $requestedUsername);
    $stmt->bind_result($activityName, $createdOn);
    $stmt->execute();

    // display the data
    $numrows = $stmt->num_rows;
    $result=array("user activity report for:  " . $requestedUsername . " with " . $numrows . " rows:");
    $result[]="Created On --- Activity Name";
    while ($stmt->fetch())
    {
        $msg = " " . $createdOn . " --- " . $activityName . " ";
        $result[] = $msg;
    }

    $stmt->close();

There are multiple rows found, and the fetch loop process them just fine. Any suggestions on what will enable me to get the number of rows returned in the query?

Suggestions are much appreciated. Thanks in advance.

score 2 · Accepted Answer · answered Apr 13 '12 at 23:30

2

You need to call $stmt->store_result() first, just before $stmt->num_rows.

answered Apr 13 '12 at 23:30

webbiedave

48,414
8
88
101

score 1 · Answer 2 · answered Apr 13 '12 at 23:27

1

Try to add this before you call num_rows;.

$stmt->store_result();

answered Apr 13 '12 at 23:27

Jake Toolson

480
3
8

1

For the record I did not copy your answer :) Before, your answer said something about calling `num_rows` as a method instead of a member. Then you edited it after I already answered. Don't be offended! – webbiedave Apr 13 '12 at 23:34

JT Smith · Answer 3 · 2012-04-13T23:38:04.917

I don't know if this will fix it, but you can't bind_results until after you execute the query, if I'm not mistaken.

Also your while loop:

while ($stmt->fetch())
 {
     $msg = " " . $createdOn . " --- " . $activityName . " ";
     $result[] = $msg;
 }

you will lose all of the $msg variables with each iteration of the loop, except the last setting because you either need to do $msg .= or make $msg an array $msg[] =

score 0 · Answer 4 · answered Apr 13 '12 at 23:35

0

$stmt->store_result();
$numrows = $stmt->num_rows;

Check this: http://php.net/manual/en/mysqli-result.num-rows.php and this http://php.net/manual/en/mysqli-stmt.num-rows.php

answered Apr 13 '12 at 23:35

Nathan

2,705
23
28

num_rows is 0 when it should be >0 for php mysqli code

4 Answers4