While refactoring some code that I had written about six years ago, I was puzzled by the MySQLi num_rows property constantly returning 0, even though I knew there were matching records in the database. It turns out that the problem was caused by using a prepared statement, but not storing the result before attempting to access num_rows. I hadn’t bothered to store the result because all I wanted to know was if there were any matching records. This is the code I was using:
$stmt = $db->stmt_init();
$sql = 'SELECT username FROM users WHERE username = ?';
$stmt->prepare($sql);
$stmt->bind_param('s', $_POST['username']);
$stmt->execute();
$numrows = $stmt->num_rows;
The code worked without error, so it wasn’t immediately obvious why $numrows was always 0. So, I checked the PHP documentation for mysqli_stmt::num_rows. The description is rather ambiguous in that it refers only to the need to store the result when using the procedural style, but the object-oriented example makes it clear that you need to call the store_result() method before accessing the num_rows property. When I changed my code like this, I got the expected result:
$stmt = $db->stmt_init();
$sql = 'SELECT username FROM users WHERE username = ?';
$stmt->prepare($sql);
$stmt->bind_param('s', $_POST['username']);
$stmt->execute();
// store result of prepared statement
$stmt->store_result();
$numrows = $stmt->num_rows;
At first, it seemed counterintuitive to store a result that I was going to throw away, but that’s how num_rows works with a MySQLi prepared statement. Thinking about it a bit more, it makes sense because if you use the MySQLi query() method instead of a prepared statement, you store the result in a MySQLi_Result object and get the number of rows from the result like this:
$sql = 'SELECT user_id, name, username FROM users';
$result = $db->query($sql);
$numrows = $result->num_rows;
So I’m doing the exact same thing, and it’s NOT working. I still get a zero. So obviously something is different!
function get_tabs_sub($tabID) {
global $db;
$query = “SELECT *
FROM tabs_sub
WHERE tabID = $tabID
ORDER BY subName”;
$tab_subs = $db->query($query);
return $tab_subs;
}
….
$tab_subs_count = $tab_subs->num_rows;
….
If I do a foreach loop, I can ‘count’ the rows but that just isn’t the right way to do it!
Any ideas?
Sorry, I don’t know what would be causing the problem. You’re returning a MySQLi_Result object from your function, so it should work. I’ve had a look at the PHP bugbase, but don’t see anything relevant there either. Very strange.
I’m having the same problem as Ray (getting 0 for num_rows). Could it be related to this change in PHP 5.3, noted in the first comment (update at bottom of comment) on this page of the PHP manual: http://www.php.net/manual/en/mysqli-stmt.bind-param.php? The commenter doesn’t state what specific problem is caused by not passing values by reference, but in my case the values are not bound and num_rows returns 0.
(Note: I haven’t tried the commenter’s solution as I’m trying to follow the code in PHP Solutions. I did try passing by reference when binding parameters and then when binding the result, but received a warning that call-time pass-by reference had been deprecated.)
Fatal error: Call to a member function query() on a non-object in C:\wamp\www\phpsols\mysql\mysqli_10.php on line 9
line 9 $result = $conn->query($sql) or die(mysqli_error());
Where is the code to create the phsols database
Ken
The solution to both issues is on the book’s corrections and updates page.
I am looking for the script to create the database phpsols or the Data dictionary on what is internal to the tables and structures.
There is no script. The instructions for creating the database structure are in Chapter 10.
For simple queries I tend to use $db->query($r), the difference is that variables in the query should be escaped e.g. $q=”SELECT * FROM users WHERE user_id=”$user_id”
Seriously, the official php documention is the biggest bullshit ever ! the official “num_rows”page does not really explain how to get num_rows. 100 of lines of example code, but not aa single line that display shows how to use it! a doc made by nerds.