MySQL Fetch Array vs Fetch Assoc in PHP - Explained

Confused between mysql_fetch_array() and mysql_fetch_assoc()? This 2025 PHP guide compares speed, memory use, and return types with easy examples for beginners.

Many of the PHP newbies get confused with mysql_fetch_row(), mysql_fetch_object(), mysql_fetch_assoc(), mysql_fetch_array() functions. In this post we will check then in detail with example.

 

🧠 Understanding the Difference Between These Fetch Functions

Even though all four functions seem similar, they actually serve slightly different purposes depending on how you want to access the data. Here’s a simple explanation:

• mysqli_fetch_row() – Returns data as a numeric array (index-based like 0, 1, 2...).
• mysqli_fetch_assoc() – Returns data as an associative array using column names as keys (like 'id', 'username').
• mysqli_fetch_array() – Combines both the above results (numeric + associative).
• mysqli_fetch_object() – Returns the row as an object, and values can be accessed using $row->column_name.

So in short, all four give you the same data — the only difference is how you access it in PHP.

 

💡 Which One Should You Use?

For most projects, developers prefer mysqli_fetch_assoc() because it gives meaningful keys (like 'username') and avoids confusion when the query changes the column order.

Example scenario: Imagine your SQL query later adds another column or reorders the fields — if you are using mysqli_fetch_row(), your code will break because it depends on numeric indexes. But with mysqli_fetch_assoc(), your code still works since it refers to column names directly.

On the other hand, mysqli_fetch_array() is handy when you are experimenting or debugging because it gives access both ways. However, it takes slightly more memory since it stores duplicate keys (both numeric and associative).

Finally, mysqli_fetch_object() is best used in object-oriented PHP projects. It makes your code look cleaner and easier to maintain when dealing with multiple records or when using frameworks like Laravel or CodeIgniter.

In order to understand with examples, lets create a table called users by running the below query:

create table users(
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(250) NOT NULL,
    PRIMARY KEY ( id )
 );

Insert some testing data as well:

 INSERT INTO `users` (`id`, `username`, `email`) VALUES (NULL, 'Piers', 'piers@test.com');
 INSERT INTO `users` (`id`, `username`, `email`) VALUES (NULL, 'Warren', 'warren@test.com');
 INSERT INTO `users` (`id`, `username`, `email`) VALUES (NULL, 'Tim', 'tim@test.com');
 INSERT INTO `users` (`id`, `username`, `email`) VALUES (NULL, 'Joseph', 'joseph@test.com');
 INSERT INTO `users` (`id`, `username`, `email`) VALUES (NULL, 'Robert', 'robert@test.com');
 INSERT INTO `users` (`id`, `username`, `email`) VALUES (NULL, 'Peter', 'peter@test.com');
 INSERT INTO `users` (`id`, `username`, `email`) VALUES (NULL, 'Boris', 'boris@test.com');

 

Create a file called index.php and try to use the below code for these functions.

 

mysqli_fetch_row()

Fetch a result row as a numeric way. This function will return a row where the values will come in the order as they are defined in the SQL query, and the keys will span from 0 to one less than the number of columns selected.

<?php
//connect database
$con    = mysqli_connect("localhost","root","password","test") or die(mysqli_error());

//select values from users table
$sql    = "select * from users where email='tim@test.com'";
$result = mysqli_query($con,$sql);
$row    = mysqli_fetch_row($result);

// access individual values
echo $userid    = $row[0];
echo $username  = $row[1];
echo $useremail = $row[2];

mysqli_close($con);

Output:

3
Tim
tim@test.com

 

Herewith this  mysqli_fetch_row() function, if you are fetching the selected records then their order matters while getting the result.

For example, if you have written, "select id, username, email from users" then,

$rows[0]; will have the value of the id
$rows[1]; will have the value of username
$rows[2]; will have the value of the email
 

but if you write, "select id, email, username from users"  then,

$rows[0]; will have the value of the id
$rows[1]; will have the value of the email
$rows[2]; will have the value of username

 

 

mysqli_fetch_assoc()

Fetch a result row as an associative array. This function will return a row as an associative array where the column names will be the keys storing corresponding value.

<?php

//connect database
$con    = mysqli_connect("localhost","root","password","test") or die(mysqli_error());

//select values from users table
$sql    = "select * from users where email='tim@test.com'";
$result = mysqli_query($con,$sql);
$row    = mysqli_fetch_assoc($result);

// access individual values
echo $userid    = $row['id'];
echo $username  = $row['username'];
echo $useremail = $row['email'];

mysqli_close($con);

 Output:

3
Tim
tim@test.com

 

 

mysqli_fetch_array()

Fetch a result row as an associative array & numeric array both. It returns an array with both the contents of mysqli_fetch_row() and mysqli_fetch_assoc() merged into one. It will have both numeric and string keys.

<?php

//connect database
$con    = mysqli_connect("localhost","root","password","test") or die(mysqli_error());

//select values from users table
$sql    = "select * from users where email='tim@test.com'";
$result = mysqli_query($con,$sql);
$row    = mysqli_fetch_array($result);

print_r($row);

mysqli_close($con);

Output:

Array
(
    [0] => 3
    [id] => 3
    [1] => Tim
    [username] => Tim
    [2] => tim@test.com
    [email] => tim@test.com
)

 

 Access individual values by string keys:

echo $userid    = $row['id'];
echo $username  = $row['username'];
echo $useremail = $row['email'];

Output:

3
Tim
tim@test.com

 

Access individual values by numeric keys:

echo $userid    = $row[0];
echo $username  = $row[1];
echo $useremail = $row[2];

 Output:

3
Tim
tim@test.com

 

 

mysqli_fetch_object()

Fetch a result row as an object.

<?php

//connect database
$con    = mysqli_connect("localhost","root","password","test") or die(mysqli_error());

//select values from users table
$sql    = "select * from users where email='tim@test.com'";
$result = mysqli_query($con,$sql);
$row    = mysqli_fetch_object($result);

// access individual values as object
echo $userid    = $row->id;
echo $username  = $row->username;
echo $useremail = $row->email;

mysqli_close($con);

 Output:

3
Tim
tim@test.com

 

📋 Quick Comparison Table

Function	Return Type	Access Style	Example	When to Use
mysqli_fetch_row()	Numeric Array	$row[0]	When you know the exact column order	Good for simple and small queries
mysqli_fetch_assoc()	Associative Array	$row['username']	When you prefer column names as keys	Commonly used and beginner-friendly
mysqli_fetch_array()	Both Numeric + Associative	$row[0] or $row['username']	When you need both types of access	Useful but slightly heavier on memory
mysqli_fetch_object()	Object	$row->username	When working with OOP or classes	Cleaner syntax for modern PHP projects

 

🚫 Common Mistakes Beginners Make

• Not checking query success: Always verify if mysqli_query() returned a valid result before using fetch functions.
• Using wrong key types: If you use $row['0'] instead of $row[0], you’ll get an undefined index error.
• Mixing mysqli and mysql functions: The mysql_* functions are deprecated — always use mysqli_* or PDO in new projects.
• Forgetting to close connection: Always close your MySQL connection using mysqli_close() after the operation.

 

🔐 Performance and Memory Considerations

When fetching thousands of rows, you should consider performance. The mysqli_fetch_assoc() and mysqli_fetch_row() are generally faster because they store fewer references compared to mysqli_fetch_array(). Using mysqli_fetch_array() doubles the data internally (both numeric and associative), which means more memory usage.

So, if you are displaying a lot of data, it’s best to stick with mysqli_fetch_assoc() for cleaner and faster performance.

 

🌐 Using Fetch Functions in Real Projects

Here are a few practical situations where these functions are commonly used:

• Displaying User Profiles: Fetching a single user’s details from the database.
• Listing Records: Showing all users, products, or orders in an admin panel.
• Data Export: Reading MySQL data and writing it to a CSV or JSON file.
• API Output: Using mysqli_fetch_assoc() to convert rows into JSON response for REST APIs.

 

✅ Best Practices

• Use prepared statements (like mysqli_prepare() or PDO) for better security against SQL injection.
• Always validate database results before fetching.
• Use mysqli_fetch_assoc() by default for clarity and simplicity.
• For object-oriented codebases, consider mysqli_fetch_object() or PDO’s fetchObject().

 

📘 Conclusion

Understanding the difference between mysqli_fetch_array() and mysqli_fetch_assoc() helps you write more efficient and maintainable PHP code. While both can fetch the same data, mysqli_fetch_assoc() is usually preferred for its readability and performance.

To summarize:

• Use mysqli_fetch_assoc() for clean and safe data access.
• Use mysqli_fetch_row() when column order is fixed and performance matters.
• Use mysqli_fetch_array() for flexible debugging or dual access.
• Use mysqli_fetch_object() for OOP-based scripts or frameworks.

As you continue practicing with different fetch methods, you’ll naturally learn when to use which one. Understanding this small difference gives you better control over your data handling and makes your PHP-MySQL applications more professional.

---