Been using PHP/MySQL for a little while now, and I'm wondering if there are any specific advantages (performance or otherwise) to using mysql_fetch_object() vs mysql_fetch_assoc() / mysql_fetch_array().
Fetching an array with mysql_fetch_array() lets you loop through the result set via either a foreach loop or a for loop. mysql_fetch_object() cannot be traversed by a for loop.
Not sure if that even matters much, just thought I'd mention it.
I think the difference between all these functions is insignificant, especially when compared to code readability.
If you're concerned about this kind of optimization, use mysql_fetch_row()
. It's the fastest because it doesn't use associative arrays (e.g. $row[2]), but it's easiest to break your code with it.
while ($Row = mysql_fetch_object($rs)) {
// ...do stuff...
}
...is how I've always done it. I prefer to use objects for collections of data instead of arrays, since it organizes the data a little better, and I know I'm a lot less likely to try to add arbitrary properties to an object than I am to try to add an index to an array (for the first few years I used PHP, I thought you couldn't just assign arbitrary properties to an object, so it's ingrained to not do that).
Performance-wise it doesn't matter what you use. The difference is that mysql_fetch_object returns object:
while ($row = mysql_fetch_object($result)) {
echo $row->user_id;
echo $row->fullname;
}
mysql_fetch_assoc() returns associative array:
while ($row = mysql_fetch_assoc($result)) {
echo $row["userid"];
echo $row["fullname"];
}
and mysql_fetch_array() returns array:
while ($row = mysql_fetch_array($result)) {
echo $row[0];
echo $row[1] ;
}
Let me just say not to use mysql_fetch_array()
you get back numerically indexed array's which are hard to remember which set of data is where and this if your database schema changes you need to go back and update your code =\
Something to keep in mind: arrays can easily be added to a memory cache (eaccelerator, XCache, ..), while objects cannot (they need to get serialized when storing and unserialized on every retrieval!).
You may switch to using arrays instead of objects when you want to add memory cache support - but by that time you may have to change a lot of code already, which uses the previously used object type return values.
mysql_fetch_array makes your code difficult to read = a maintenace nightmare. You can't see at a glance what data your object is dealing with. It slightly faster, but if that is important to you you are processing so much data that PHP is probably not the right way to go.
mysql_fetch_object has some drawbacks, especially if you base a db layer on it. + Column names may not be valid PHP identifiers, e.g tax-allowance or user.id if your database driver gives you the column name as specified in the query. Then you have to start using {} all over the place. + If you want to get a column based on its name, stroed in some vraible you also have to start using variable properties $row->{$column_name}, while array syntax $row[$column_name] + Constructors don't get invoked when you might expect if you specify the classname. + if you don't specify the class name you geta stdClass, which is hardly better than an array anyway.
mysql_fetch_assoc is the easiest of the three to work with, and I like the distinction this gives in the code between objects and database result rows...
$object->property=$row['column1']; $object->property=$row[$column_name]; foreach($row as $column_name=>$column_value){...}
While many OOP fans (and I am an OOP fan) like the idea of turning everything into an object, I feel that the associative array is a better model of a row from a database than an object, as in my mind an object is a set of properties with methods to act upon them, whereas the row is just data and should be treated as such without further complication.