PHP, MySQL, and mysql_fetch_array()


Today, I spent a couple hours working on a customer management system for APS Web Design.  Over the course of that work, I discovered a very interesting behavior in a particular function, specifically mysql_fetch_array().

For those not familiar with it, but familiar with PHP, this function takes the results returned by mysql_query(), and turns it into an array of values.  There are two other functions that do similar things: mysql_fetch_row() and mysql_fetch_assoc().

mysql_fetch_row() “fetches one row of data from the result associated with the specified result identifier.  The row is returned as an array.  Each result column is stored in an array offset, starting at offset 0”. source

So, first value is $result[0], second is $result[1], and so on.

mysql_fetch_assoc() “returns an asociative array of strings that corresponds to the fetched row”. source  That is, each value that is returned is part of a pair: a “key” (index) value, and the actual value.  The “key” names correspond to the name of the field in the database.

So, first value is $result[‘name’], second is $result[’email’], and so on.

When I’m coding, I like to have access to both the “key” values, and the numeric index.  Now, in all honesty, I almost always use “key” values, over numeric, but I still like having the option.  This is where mysql_fetch_array() comes in.

mysql_fetch_array() will give you “an array with both associative and number indices.” source

Many other languages can do both associative (“key” index) and numeric indices by default.  Others are constrained to just numeric.  Here, PHP is giving us both.

The question: how?

The answer: it cheats!

Seriously.  It cheats.  What do I mean?  I mean that the numeric indices are not truly referencing the position of the value.  One would expect that “$result[0]” would reference the value in the first position.  It does not.  This can easily be verified with a handy print_r(), or the following loop:

$result = mysql_fetch_array($result);
foreach ($result as $key => $value)
echo $key . ‘=’ . $value . ‘<br />’;

The result of that loop, on a data set I was working with, returned the following:

2=Mack Staples
name=Mack Staples
phone_num= 5415551234
[email protected]
[email protected]

What do you notice?  Every value is in there twice, and while the ‘0’ value is indeed in the first position, the rest of the numbers don’t correlate.  So what is it doing?

mysql_fetch_array() is building a larger array (twice the size of either of the other functions) and using the numbers not as true indexes, but as associative “key” values.  This means a couple of things.

1 – You are wasting speed if you reference the values, but don’t use quotes.  According to Reinhold Weber’s Blog:  #17: “$row[’id’] is 7 times faster than $row[id]”.  If you’re doing this a lot, and often… ouch.

2 – You’re wasting speed just using it.  From having to initially populate twice the data into an array, to a slowdown copying the array to other memory locations, this can cost you performance.

So, what can we do about it?

1 – Use quotes!  1/7th of the time to get $result[‘2’] compared to $result[2].  Simple speed fix.

2 – Using mysql_fetch_array() is up to you.  For what I usually use database results for, the difference is negligible.  I do small, infrequent queries, and I like having both numeric and associated indices.  To me, it’s worth the minor performance hit.

Code Safe. Code Smart.  Be paranoid.


2 thoughts on “PHP, MySQL, and mysql_fetch_array()

  1. What do you mean by “Use quotes! 1/7th of the time to get $result[‘2’] compared to $result[2]. Simple speed fix.”? We should use quotes even when we are requesting the variable by it’s position in array, not only when we are requesting the variable by it’s key? Thank you

  2. That’s what I’m saying, yes: use quotes for array position as well as array key. As I explained, the array being created actually contains your data twice, once by key => value and once by position => value. The trick is, the position => value is actually just another key => value set. In this case, then, when you request array[2], PHP figures out that you mean array[‘2’]. So, cut to the chase, use quotes, request array[‘2’], and speed up your code.

Leave a Reply

Your email address will not be published. Required fields are marked *