<p>I was writing a database handler class in PHP using the mysqli class and prepared statements.  I was attempting to print out the result.  It didn't work right off the bat so I decided to do some debugging.  I tried to use the <code>num_rows()</code> method from the <code>mysqli_statement</code> class, but it kept returning 0.  I decided to write a small portion of the test code to keep it simpler so I could see what was going wrong.  I was then able to return the data I wanted, but the <code>num_rows()</code> method still returns 0 even when it is actually selecting and retrieving some data.  Here is the code:</p>
<pre><code>$mysqli = new mysqli('localhost', 'username', 'password', 'database');
if(mysqli_connect_errno())
{
  die('connection failed');
}

$statement = $mysqli-&gt;stmt_init();

$query = &quot;SELECT name FROM table WHERE id = '2000'&quot;;
if($statement-&gt;prepare($query))
{
    $statement-&gt;execute();
    $statement-&gt;bind_result($name);
    $statement-&gt;fetch();
    $statement-&gt;store_result();
    echo $statement-&gt;num_rows();
    echo $name; 
}
else
{
    echo 'prepare statement failed';
    exit();
}
</code></pre>
<p>The expected result is:</p>
<pre><code>1name
</code></pre>
<p>And the actual result is:</p>
<pre><code>0name
</code></pre>
<p>Can anyone tell me why this is?</p>

[{'answer_id': 80382, 'author': 'Cetra', 'author_id': 15087, 'author_profile': 'https://Stackoverflow.com/users/15087', 'pm_score': -1, 'selected': False, 'text': '<p>It doesn\'t look like you\'ve declared $name.</p>\n\n<p>Also, try removing bind_result() and fetch() so it reads something like this:</p>\n\n<pre><code>$statement-&gt;execute();\n\n$statement-&gt;store_result();\n\nprintf("Number of rows: %d.\\n", $statement-&gt;num_rows);\n</code></pre>\n'}, {'answer_id': 80400, 'author': 'Nathan Strong', 'author_id': 9780, 'author_profile': 'https://Stackoverflow.com/users/9780', 'pm_score': 4, 'selected': True, 'text': '<p>I wonder if num_rows() is reporting relative to the current resultset. Try capturing num_rows() prior to fetching the data. e.g.</p>\n\n<pre><code>if($statement-&gt;prepare($query))\n{\n    $statement-&gt;execute();\n    $statement-&gt;store_result();\n    echo $statement-&gt;num_rows();\n    $statement-&gt;bind_result($name);\n    $statement-&gt;fetch();\n    echo $name; \n}\n</code></pre>\n\n<p>Does that have any effect?</p>\n'}, {'answer_id': 624630, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p><code>num_rows</code> is not a method, it's a property.</p>\n"}, {'answer_id': 66500988, 'author': 'Dharman', 'author_id': 1839439, 'author_profile': 'https://Stackoverflow.com/users/1839439', 'pm_score': 0, 'selected': False, 'text': "<p>In order to be able to use <code>mysqli_stmt::num_rows(),</code> you need to fetch all rows into PHP. There are two ways to fetch everything: buffering using <code>store_result()</code> or manual fetching of all rows using <code>fetch()</code>.</p>\n<p>In your case, you have started manual fetching by calling <code>fetch()</code> once. You can't call <code>store_result()</code> when another fetch process is ongoing. The call to <code>store_result()</code> fails with an error*.</p>\n<pre><code>$statement-&gt;fetch();\n$statement-&gt;store_result(); // produces error. See $mysqli-&gt;error;\necho $statement-&gt;num_rows();\n</code></pre>\n<p>The easiest solution is to swap the order in which you call these two methods.</p>\n<pre><code>$statement-&gt;store_result();\n$statement-&gt;fetch(); // This will initiate fetching from PHP buffer instead of MySQL buffer\necho $statement-&gt;num_rows(); // This will tell you the total number of rows fetched to PHP\n</code></pre>\n<p><sub>* Due to a bug in PHP, this error will not trigger an exception in the exception error reporting mode. The error message can only be seen with <code>mysqli_error()</code> function or its corresponding property.</sub></p>\n"}]
['https://Stackoverflow.com/questions/80292', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/3831/']