In this article, we’ll delve into the functionality of mysqli_fetch_row(), explore its use cases, and provide comprehensive code examples to illustrate its implementation.
Introduction to mysqli_fetch_row()
mysqli_fetch_row() is a function in the MySQLi extension of PHP that fetches one row from a result set as a numeric array. Each subsequent call to mysqli_fetch_row() will return the next row in the result set, or NULL if there are no more rows.
Syntax
array mysqli_fetch_row ( mysqli_result $result )
$result: A result set identifier returned by mysqli_query(), mysqli_store_result(), or mysqli_use_result().
Setting Up the Environment
Before we dive into the examples, let’s set up a basic environment. We’ll assume you have a MySQL server running and accessible, and a database with some sample data.
Sample Database and Table
Consider a database named test_db and a table named employees with the following schema:
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
position VARCHAR(50) NOT NULL,
salary DECIMAL(10, 2) NOT NULL
);
INSERT INTO employees (name, position, salary) VALUES
('Alice', 'Software Engineer', 75000.00),
('Bob', 'Data Scientist', 80000.00),
('Charlie', 'Product Manager', 90000.00);
Basic Usage of mysqli_fetch_row()
Let’s start with a basic example of fetching all rows from the employees table.
<?php
// Database credentials
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test_db';
// Create a connection
$conn = new mysqli($host, $user, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Query the database
$sql = "SELECT * FROM employees";
$result = $conn->query($sql);
// Check if the query returned any results
if ($result->num_rows > 0) {
// Fetch and display each row
while ($row = mysqli_fetch_row($result)) {
printf("ID: %d, Name: %s, Position: %s, Salary: %.2f\n", $row[0], $row[1], $row[2], $row[3]);
}
} else {
echo "No records found.";
}
// Close the connection
$conn->close();
?>
Example Explained
Connection Establishment: We establish a connection to the MySQL server using the MySQLi extension.
Query Execution: We execute a query to select all rows from the employees table.
Result Handling: We use mysqli_fetch_row() inside a while loop to fetch each row from the result set.
Row Display: Each row is displayed using printf(), with array indices to access individual fields.
Use Cases for mysqli_fetch_row()
1. Fetching and Displaying Data
The most straightforward use case for mysqli_fetch_row() is fetching and displaying data from a database, as shown in the basic example above. This is useful in various scenarios, such as generating reports, displaying user data on a website, or simply debugging.
2. Processing Data for Further Use
You can fetch data using mysqli_fetch_row() and then process it further according to your application’s needs. For instance, you might want to calculate the average salary of employees.
<?php
// Database credentials
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test_db';
// Create a connection
$conn = new mysqli($host, $user, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Query the database
$sql = "SELECT salary FROM employees";
$result = $conn->query($sql);
// Initialize variables
$totalSalary = 0;
$employeeCount = 0;
// Check if the query returned any results
if ($result->num_rows > 0) {
// Fetch and process each row
while ($row = mysqli_fetch_row($result)) {
$totalSalary += $row[0];
$employeeCount++;
}
$averageSalary = $totalSalary / $employeeCount;
echo "Average Salary: " . number_format($averageSalary, 2);
} else {
echo "No records found.";
}
// Close the connection
$conn->close();
?>
3. Building Data Structures
Another use case is building data structures, such as arrays or objects, from the fetched data for use in other parts of your application.
<?php
// Database credentials
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test_db';
// Create a connection
$conn = new mysqli($host, $user, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Query the database
$sql = "SELECT * FROM employees";
$result = $conn->query($sql);
// Initialize an array to hold employee data
$employees = [];
// Check if the query returned any results
if ($result->num_rows > 0) {
// Fetch and build the array
while ($row = mysqli_fetch_row($result)) {
$employees[] = [
'id' => $row[0],
'name' => $row[1],
'position' => $row[2],
'salary' => $row[3]
];
}
print_r($employees);
} else {
echo "No records found.";
}
// Close the connection
$conn->close();
?>
Comparing mysqli_fetch_row() with Other Fetch Functions
MySQLi provides several functions for fetching data: mysqli_fetch_assoc(), mysqli_fetch_array(), mysqli_fetch_object(), and mysqli_fetch_row(). Here’s a brief comparison:
mysqli_fetch_assoc(): Fetches a result row as an associative array.
mysqli_fetch_array(): Fetches a result row as an associative array, a numeric array, or both.
mysqli_fetch_object(): Fetches a result row as an object.
mysqli_fetch_row(): Fetches a result row as a numeric array.
Each function has its own advantages depending on the context. mysqli_fetch_assoc() is useful when you need to access columns by name, whereas mysqli_fetch_row() can be faster and use less memory when you only need numerical indices.
Error Handling
Proper error handling is crucial when working with databases. The examples above include basic error handling by checking the connection and result set. You can enhance this by using more detailed error messages.
<?php
// Database credentials
$host = 'localhost';
$user = 'root';
$password = '';
$database = 'test_db';
// Create a connection
$conn = new mysqli($host, $user, $password, $database);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Query the database
$sql = "SELECT * FROM employees";
$result = $conn->query($sql);
// Check if the query executed successfully
if (!$result) {
die("Query failed: " . $conn->error);
}
// Fetch and display each row
if ($result->num_rows > 0) {
while ($row = mysqli_fetch_row($result)) {
printf("ID: %d, Name: %s, Position: %s, Salary: %.2f\n", $row[0], $row[1], $row[2], $row[3]);
}
} else {
echo "No records found.";
}
// Close the connection
$conn->close();
?>