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:
0=mack
username=mack
1=1
job_num=1
2=Mack Staples
name=Mack Staples
3=5415551234
phone_num= 5415551234
4=test@example.com
email=test@example.com
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.
April 3rd, 2008 by Mack Staples | No Comments »
