MySQLi fetch_row Method

PHP

MySQLi fetch_row - Fetch Row as Numeric Array

Welcome to this comprehensive tutorial on the PHP MySQLi fetch_row method. If you're working with MySQL databases in PHP and want a lightweight, efficient way to fetch rows as numeric arrays, this guide is for you. We'll cover everything from setup to best practices and provide real-world examples to help you master mysqli_fetch_row().

Introduction to mysqli_fetch_row()

The mysqli_fetch_row() method retrieves a single row from a MySQLi result set as a numeric array. This means each column's data is accessed via a zero-based index rather than a column name, making it ideal for situations where you want fast, minimal memory overhead without associative array keys.

Prerequisites

  • Basic knowledge of PHP and MySQL
  • PHP environment with MySQLi extension enabled
  • Access to a MySQL database server
  • Basic understanding of SQL SELECT queries

Setup Steps

Step 1: Create a Sample Database and Table

Assuming you have MySQL installed, use the following SQL commands to create a database and a table to experiment with.

CREATE DATABASE demo_db;
USE demo_db;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100)
);

INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('carol', 'carol@example.com');

Step 2: Connect to the Database Using PHP MySQLi

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "demo_db";

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

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

Using mysqli_fetch_row() - Explained Examples

Example 1: Fetching Rows in Numeric Array Format

This example executes a simple SELECT query and fetches rows with fetch_row().

<?php
$sql = "SELECT id, username, email FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_row()) {
        // $row is a numeric array, e.g. [0] => id, [1] => username, [2] => email
        echo "ID: " . $row[0] . " - Username: " . $row[1] . " - Email: " . $row[2] . "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Output:

ID: 1 - Username: alice - Email: alice@example.com
ID: 2 - Username: bob - Email: bob@example.com
ID: 3 - Username: carol - Email: carol@example.com

Example 2: Using mysqli_fetch_row() with Prepared Statements

Secure your queries by using prepared statements, fetch result rows similarly:

<?php
$stmt = $conn->prepare("SELECT id, username, email FROM users WHERE id > ?");
$minId = 1;
$stmt->bind_param("i", $minId);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_row()) {
    echo "ID: " . $row[0] . " - Username: " . $row[1] . " - Email: " . $row[2] . "<br>";
}
$stmt->close();
$conn->close();
?>

Best Practices

  • Use prepared statements: To avoid SQL injection vulnerabilities, always use prepared statements when taking user input.
  • Close connections and statements: Free resources by properly closing your mysqli objects.
  • Access columns by index carefully: Since fetch_row() returns an indexed array, ensure your SELECT query order matches expected field indexes.
  • Prefer fetch_assoc() if readability is important: While fetch_row() is faster and lighter, fetching rows as associative arrays might improve code clarity.

Common Mistakes to Avoid

  • Trying to access result keys as strings (e.g., $row['username']) after using fetch_row() will not work since returned data is a numeric array.
  • Not checking if the query succeeded before fetching rows.
  • Forgetting to close statements or the database connection.
  • Mismatch between SELECT column order and numeric field indexes used in PHP.

Interview Questions

Junior-Level Questions

  • Q1: What does mysqli_fetch_row() return?
    A: It returns a single row from the MySQL result set as a numeric array where each column is accessed by its zero-based index.
  • Q2: How do you access the second column from the array returned by mysqli_fetch_row()?
    A: Use index 1, like $row[1].
  • Q3: Does mysqli_fetch_row() return an associative array?
    A: No, it returns a numeric indexed array only.
  • Q4: What kind of result does fetch_row() operate on?
    A: It operates on a MySQLi result object obtained from a query or prepared statement execution.
  • Q5: Can mysqli_fetch_row() be used with prepared statements?
    A: Yes, after using get_result() method on the statement, you can call fetch_row() on the result.

Mid-Level Questions

  • Q1: Compare mysqli_fetch_row() with mysqli_fetch_assoc(). When would you prefer one over the other?
    A: fetch_row() returns numeric arrays, which is more memory-efficient and faster, suitable for large datasets. fetch_assoc() returns associative arrays for better readability and maintenance. Choose based on use case.
  • Q2: How does the order of columns in a SELECT query affect fetch_row()?
    A: The order of columns determines the numeric indexes; the first selected column is index 0, so accessing the correct index depends on the SELECT clause order.
  • Q3: How do you handle NULL values when using mysqli_fetch_row()?
    A: NULLs are returned as PHP nulls in the numeric array, which requires proper checking in the code to avoid unexpected issues.
  • Q4: Write a code snippet to fetch and print only the usernames using mysqli_fetch_row().
    A: 
    
while ($row = $result->fetch_row()) {
    echo $row[1]; // assuming username is the second selected column
}
  • Q5: Can you use mysqli_fetch_row() to fetch data from a stored procedure call?
    A: Yes, after executing the stored procedure and retrieving the result set, fetch_row() can fetch rows as numeric arrays.

Senior-Level Questions

  • Q1: What are the performance implications of using mysqli_fetch_row() in a high-load application?
    A: fetch_row() uses less memory and CPU since it returns simple numeric arrays without associative keys, which improves speed and reduces resource consumption compared to fetch_assoc().
  • Q2: How would you handle fetching large datasets with mysqli_fetch_row() to avoid memory exhaustion?
    A: Use efficient pagination or batching to limit the number of rows fetched at once; rely on numeric arrays from fetch_row() to reduce memory overhead.
  • Q3: When using mysqli_fetch_row() in prepared statements, what are the necessary MySQLi method calls to get a result set?
    A: Execute the statement, then call get_result() to obtain a result object, followed by fetch_row() to fetch individual rows.
  • Q4: Can you explain the difference between mysqli_fetch_row() and mysqli_stmt::bind_result() in result retrieval?
    A: fetch_row() fetches rows as numeric arrays from a result set, while bind_result() binds variables to prepared statement columns and fetches data into those variables directly.
  • Q5: How do you ensure error handling when using mysqli_fetch_row() in production-level code?
    A: Always check if query and result are successful, verify that the result object is valid, and confirm return value of fetch_row() is not false before processing rows.

FAQ

What is the difference between fetch_row() and fetch_array()?

fetch_row() returns the row as a numeric array, whereas fetch_array() returns the row as an array indexed by both column name and numeric index.

Can I fetch multiple rows at once with fetch_row()?

No, fetch_row() fetches only one row per call. Use loops or other batching methods to iterate over multiple rows.

Is fetch_row() faster than fetch_assoc()?

Generally yes, because numeric arrays are simpler in structure and require less overhead.

What happens if I access an invalid index in the array returned by fetch_row()?

You will get an undefined offset notice or warning in PHP. Always ensure you know the SELECT query column order.

Can I use fetch_row() with procedural MySQLi?

Yes, it works similarly, for example: mysqli_fetch_row($result).

Conclusion

The PHP mysqli_fetch_row() method is a powerful and efficient way to fetch database result rows as numeric arrays. By understanding its usage, paired with best practices such as prepared statements and proper resource handling, you can write fast, clean, and scalable database-interacting PHP code. Use this tutorial to confidently implement fetch_row() in your projects and master numeric index result fetching.