Why Does the fetch_array() Function Even Exist?

When looping through a MySQL result set, which function do you use? My preference has always been for the fetch_array() function since it allows access to the data using the database column names. However, is that the most efficient function to use?

Overview

Since learning PHP (and MySQL), I've always thought that there were only two main ways to grab a row from the database query results. Using the fetch_row() function returns the data in a numeric array where the array index is based on how the columns are ordered in the query. If we have the following query:

<?php
$sql = "SELECT firstName, lastName FROM book_authors";
$result = $mysqli->query($sql);
while($row = $result->fetch_row()) {
     //...
}
?>

The first name can be accessed, within the loop, using $row[0]. Changing to $row[1] gives us the last name. The problem with the numeric array is that the code gets a little convoluted. Especially when dealing with dozens of fields.

That's why my chosen method for the longest time has been fetch_array(). This function returns an associative array which lets us access the values using the column names. If our script was changed to

<?php
$sql = "SELECT firstName, lastName FROM book_authors";
$result = $mysqli->query($sql);
while($row = $result->fetch_array()) {
     //...
}
?>

The first and last name can be accessed using $row['firstName'] and $row['lastName'] respectively.

Alternate Solution

Well, it turns out that there's another option. I've actually seen it floating around on PHPFreaks.com. It just hasn't clicked with me until I noticed what fetch_array() really returns. During the debugging process for one of my projects, I ran the following line of code to quickly see what was stored within $row:

<?php
print '<pre>' . print_r($row, true) . '</pre>';
?>

In addition to creating the associative array, fetch_array() also returns a numeric version of the array. So, we have duplicate copies of the data. To avoid this unnecessary overhead, we can turn to fetch_assoc() which only returns the associated array entries.

Conclusion

Now, of course, fetch_array() has an optional parameter which tells the function how to behave. It can be set so the function only returns the associative array. However, this seems like an unnecessary step when fetch_assoc() is right there.

With that said, what is the purpose of fetch_array()? We already have options to return numeric and associative arrays. Is there a reason for needing both? If so, I would love to hear your thoughts in the comments section below.

0 Comments

There are currently no comments.

Leave a Comment