PHP and MySQL are a match made in heaven when it comes to building database-driven websites. Using a database such as MySQL is a great way to store and receive information quickly and securely using PHP. Luckily, PHP supports multiple database vendors, such as MongoDB, PostgreSQL, SQLite, and of course MySQL. When working with the MySQL database, PHP provides a wealth of different functions so you can interact seamlessly with the database, via an API.
In the latest version of PHP, the wrapper you should be using is MySQLi, the improved wrapper to the existing MySQL connection functions, MySQL (Original). MySQLi also known as MySQL Improved Extension, allows you to connect to MySQL databases 4.1 or newer. The extension PHP provides has all the necessary functions, like CRUD (create, read, update, and delete) to perform common tasks when it comes to working with databases. One of the most common functions is mysqli_num_rows. But how does this function work, what can it be used for, and how can you debug common errors with mysqli_num_rows? We'll explore all those questions and more in this guide.
What does mysqli_num_rows do?
When reading out information from the database using PHP, there will be times when you will want to know the total number of rows that are contained in the result set. You might be tempted to loop around each item in the result set and count the number yourself but you will quickly find that this is impractical if you doing a lot of different queries and ultimately if you're only looping to count, this is a waste of resource, and more likely to slow the page load for your users. Instead, MySQL and PHP already know the total amount of results obtained when running your query. so instead, calling this function (instead of writing and looping your own) is much better for performance and code maintainability. This is a great way to display total results to users, maybe they've searched a term and you want to display this back to the user showing the total results found. Using the mysqli_num_rows function is a great way to do this. Let's explore how you can use this function with some PHP examples, showing both object-oriented (object-oriented programming (OOP)) and procedural styles.
How to use mysqli_num_rows in an object-oriented style
When programming class style functions like connecting and interacting with a database such as MySQL, doing this in an object-oriented (OOP) style way is always advised as it's easier for others to work on and you when you come back another time to work on it (or to fix a bug). One thing to note here is that the OOP style of using mysqli_num_rows is labeled just "num_rows".
$result = $mysqli->query("SELECT * FROM students");
// Would echo the total results found
echo $result->num_rows;
How to use mysqli_num_rows in a procedural style
Unlike the OOP style way of getting the total number of results found in a query, here we can obtain the same information in a procedural style by using the full name, mysqli_num_rows(). Here we have to use it as a function and pass the result we want to count to it. This becomes repetitive when doing lots of queries and this is why the object-oriented approach is preferred.
$result = mysqli_query($link, "SELECT * FROM students");
// Would echo the total results found
echo mysqli_num_rows($result);
Both the object-oriented and procedural styles will produce the same result, it's how they are used and the code used that is the difference. It's also important to note, that whichever style you choose, you'll have to stick to that throughout the code when interacting with MySQL at least.
How to fix mysqli_num_rows expects parameter 1 to be mysqli result
In the procedural style of using mysqli_num_rows, because it's a function we have to pass the mysqli result query to it, otherwise it triggers an error. Take the following example, if your code looks like this (passing nothing to the function), then it's incorrect. That's because it will trigger the error "mysqli_num_rows expects parameter 1 to be mysqli result", which is the MySQL query that's been run. That's due to the fact the procedural way needs to be told in each step of the query in question, unlike the OOP style way where this isn't required. A similar error you might face is; "mysqli_num_rows() argument #1 ($result) must be of type mysqli_result", that's also fixed with the code below. Be sure not to mix mysqli and MySQL functions together as this won't work.
$result = mysqli_query($link, "SELECT * FROM students");
// This is incorrect
echo mysqli_num_rows();
// This is correct
echo mysqli_num_rows($result);
If running mysqli_num_rows in unbuffered mode (when data is still waiting to be fetched from the MySQL server), it will return zero, unless all rows have been fetched from the server, otherwise, it's an int value. It is important to note that if the number of rows is greater than the PHP's PHP_INT_MAX number, then this is returned as a string.
Conclusion
Using the mysqli_num_rows function in PHP is a great and quick way to find the total number of results that the MySQL server has found. You could use this to output to your user or handle a particular case for your codebase.
- Choose and style (object-oriented or procedural) and stick to it
- Use num_rows for object-oriented and mysqli_num_rows() as a function for procedural
- This function can be run buffered or unbuffered