Using mysqli_num_rows()

When working with databases in PHP, especially MySQL databases, the ability to interact efficiently and accurately with the data is crucial. One such function that aids in this process is mysqli_num_rows(). This function is commonly used when you need to know how many rows were returned from a database query, allowing you to make informed decisions on how to proceed with your code.

In this article, we’ll explore the mysqli_num_rows() function in depth, discussing its syntax, usage, and practical examples.

Introduction to mysqli_num_rows()

The mysqli_num_rows() function is part of the MySQLi extension in PHP, which is an improved version of the original MySQL extension. This extension is designed to take advantage of the newer features in MySQL and to provide a more secure, faster, and feature-rich API for database interaction. mysqli_num_rows() is used to count the number of rows in a result set that is returned by a SELECT query. This can be particularly useful in situations where you want to determine if a query has returned any results or to simply count the number of records that match a particular condition.

Syntax of mysqli_num_rows()

The syntax for using mysqli_num_rows() is straightforward:

				
					int mysqli_num_rows ( mysqli_result $result )

$result: This is the result set returned by the mysqli_query() function or any other MySQLi function that returns a result set.
Return Value: The function returns an integer value representing the number of rows in the result set. If the result set is empty, the function returns 0.

Practical Usage of mysqli_num_rows()

Let’s walk through a basic example of how to use mysqli_num_rows() in a PHP script.

Example 1: Checking if a Record Exists

One of the most common uses of mysqli_num_rows() is to check if a record exists in the database. This is particularly useful for authentication systems where you need to verify if a user exists in the database.

				
					<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Query to check if a user exists
$sql = "SELECT id FROM users WHERE username = 'john_doe'";
$result = $conn->query($sql);

// Use mysqli_num_rows() to check if the user exists
if (mysqli_num_rows($result) > 0) {
    echo "User exists.";
} else {
    echo "User does not exist.";
}

// Close the connection
$conn->close();
?>

In this example, we first establish a connection to the MySQL database using MySQLi. We then execute a SELECT query to check if a user with the username ‘john_doe’ exists. The mysqli_num_rows() function is used to count the number of rows returned by the query. If the count is greater than 0, it means the user exists; otherwise, the user does not exist.

Example 2: Counting the Number of Rows in a Table

Another common use of mysqli_num_rows() is to count the number of rows in a table that match a certain condition.

				
					<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Query to count the number of active users
$sql = "SELECT id FROM users WHERE status = 'active'";
$result = $conn->query($sql);

// Use mysqli_num_rows() to count the number of active users
$activeUsersCount = mysqli_num_rows($result);

echo "There are " . $activeUsersCount . " active users.";

// Close the connection
$conn->close();
?>

In this example, the script connects to the database and executes a SELECT query to find all users with a status of ‘active’. The mysqli_num_rows() function is then used to count how many active users are in the result set, and this number is displayed.

Best Practices for Using mysqli_num_rows()

While mysqli_num_rows() is straightforward, there are best practices you should follow to ensure your code is secure, efficient, and error-free.

1. Always Check for Errors

Before using mysqli_num_rows(), ensure that your query was successful. If the query fails, the result set will be false, and attempting to use mysqli_num_rows() on a false value will cause an error.

				
					if ($result === false) {
    echo "Error in query: " . $conn->error;
    exit();
}

2. Close the Connection

After you’re done with your database operations, always close the connection to free up resources.

				
					$conn->close();

3. Sanitize Input

Never trust user input directly. Always sanitize and validate any user input before using it in a query to prevent SQL injection attacks.

				
					$username = $conn->real_escape_string($_POST['username']);
$sql = "SELECT id FROM users WHERE username = '$username'";

4. Use Prepared Statements

While mysqli_num_rows() itself does not directly expose you to SQL injection vulnerabilities, the queries you run with mysqli_query() can. To protect against SQL injection, always use prepared statements.

				
					$stmt = $conn->prepare("SELECT id FROM users WHERE username = ?");
$stmt->bind_param("s", $username);
$stmt->execute();
$result = $stmt->get_result();
if (mysqli_num_rows($result) > 0) {
    echo "User exists.";
}
$stmt->close();

Advanced Example: Paginating Results

mysqli_num_rows() can also be useful in implementing pagination. When dealing with large datasets, it’s often necessary to paginate results to display a subset of data on each page.

				
					<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$database = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Define pagination variables
$limit = 10; // Number of entries to show per page
if (isset($_GET["page"])) {
    $page = $_GET["page"];
} else {
    $page = 1;
}
$start_from = ($page-1) * $limit;

// Query to fetch results with limit
$sql = "SELECT id, username FROM users ORDER BY id ASC LIMIT $start_from, $limit";
$result = $conn->query($sql);

// Fetch and display results
if (mysqli_num_rows($result) > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"] . " - Name: " . $row["username"] . "<br>";
    }
}

// Query to find total number of records
$sqlTotal = "SELECT id FROM users";
$resultTotal = $conn->query($sqlTotal);
$total_records = mysqli_num_rows($resultTotal);
$total_pages = ceil($total_records / $limit);

// Display pagination links
for ($i=1; $i<=$total_pages; $i++) {
    echo "<a href='index.php?page=".$i."'>".$i."</a> ";
}

// Close the connection
$conn->close();
?>

In this example, we are displaying a paginated list of users. The script calculates the number of pages needed based on the total number of records and the limit of records per page. The mysqli_num_rows() function is used to determine the total number of records in the table.

Conclusion

The mysqli_num_rows() function is a powerful and essential tool for counting rows in a MySQL database result set when using PHP. Whether you are checking if a record exists, counting rows that match a certain condition, or implementing pagination, mysqli_num_rows() can be incredibly useful. By understanding its usage and following best practices, you can write efficient, secure, and error-free PHP code that interacts with your MySQL database effectively. Always remember to handle database connections carefully, sanitize inputs, and use prepared statements to ensure the security and reliability of your applications. With these principles in mind, mysqli_num_rows() can help you manage your database interactions with ease.