Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 15 down vote favorite
12

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().

share|improve this question

9 Answers

up vote 24 down vote accepted

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] ;
}
share|improve this answer
Basically, it depends on your preference. – davethegr8 Sep 23 '08 at 22:53
6  
small difference, _row returns 0,1.. and _array returns 'userid','fullname','0','1'. – Shinhan Sep 24 '08 at 8:49
in mysql_fetch_array's second argument you can specify weather you want associative result, numeric result or both (result meaning an array index value) :) – Zlatan Omerović Mar 2 '12 at 23:13
up vote 20 down vote

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 variable 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 get a 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.

share|improve this answer
2  
+1 for the best answer on the topic. I used to use assoc, then I switched to objects (in an attempt to make everything objects), but have switched back to assoc for the reasons you said; It looks more like data as an array than as an object. – J.Money Aug 21 '12 at 18:35
@J.Money But what about when you actually do switch to database results being represented by objects? Such as in active record patterns, I prefer if the front-end is already using an object interface. $user = User::find(1), then on the front end if it's already implemented as $user->email, there's no change to be made. It gives you more flexiblity to allow methods to be called from the frontend on the results. Though it's still pretty trivial to make the objects implement the ArrayAccess interface to make $user['email'] do the exact same thing to the object as $user->email.. – Anther Aug 22 '12 at 17:53
@Anther Magic methods make it simple to implement an active record using arrays to hold the row data. You are right though as PHP moves more towards OOP the implementation of mysql_fetch_* is happening behind the scenes and therefore is trivial whether one or the other is used. In fact, the only reason I started thinking about this was because I am rewriting someone's procedural code to be more object oriented and it does not use a database abstraction layer. They use all mysql_fetch_* functions without any discernible pattern throughout. – J.Money Aug 22 '12 at 18:24
@bažmegakapa This answer makes a lot of sense to me, and I have actually been thinking about switching back to mysql_fetch_assoc from mysql_fetch_object. I find mysql_fetch_object can get really confusing when you start joining in fields from other tables. One question, if I may. I normally have little utility functions in my object classes. For example, full_name() would return $this->first_name . ' ' . $this->last_name. I loose quick access to these methods when switching to arrays. How have you dealt with that problem? – Jonathan Jan 2 at 22:50
@Jonathan I have not written this answer, just edited it. This is darkpenguin's answer, who seems to be a deleted user. (I would say forget the old mysql_ stuff and switch to PDO) – bažmegakapa Jan 3 at 2:34
up vote 4 down vote

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.

share|improve this answer
Good point (memory cache support). – Camden S. Jul 24 '12 at 22:20
up vote 1 down vote 
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).

share|improve this answer
I've not really seen much code like that in the wild. – Camden S. Jul 24 '12 at 22:19
up vote 1 down vote

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.

share|improve this answer
3  
object properties can be iterated via foreach in php5+ – Owen Sep 23 '08 at 22:17
2  
while ($row = mysql_fetch_object($result)) works. Its usage is exactly the same as mysql_fetch_array() except you access with $row->field instead of $row['field']. – cubex Sep 23 '08 at 22:19
Owen, that's exactly what I said. – Steve Paulo Sep 23 '08 at 22:42
I'm personally more comfortable with associative arrays and foreach, but I'll try out the object method and see if I can get it to work. – Nicholas Flynt Nov 1 '08 at 21:16
Poor Steve just got screwed over by people not reading what he wrote correctly. I hate it when that happens, but I can understand why they read it the way they did. You left the statement rather ambiguous. – Mark Tomlin Apr 6 '10 at 2:08
show 1 more comment
up vote 1 down vote

Additionally, if you eventually want to apply Memcaching to your MySQL results, you may want to opt for arrays. It seems it's safer to store array types, rather than object type results.

share|improve this answer
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

Speed-wise, mysql_fetch_object() is identical to mysql_fetch_array(), and almost as quick as mysql_fetch_row().

Also, with mysql_fetch_object() you will only be able to access field data by corresponding field names.

share|improve this answer
up vote -2 down vote

I vote against mysql_fetch_array()

Because you get back both numerically indexed columns and column names, this creates an array that is twice as large. It's fine if you don't need to debug your code and view it's contents. But for the rest of us, it becomes harder to debug since you have to wade through twice as much data in an odd looking format.

I sometimes run into a project that uses this function then when I debug, I think that something has gone terribly wrong since I have numeric columns mixed in with my data.

So in the name of sanity, please don't use this function, it makes code maintenance more difficult

share|improve this answer
4  
mysql_fetch_array() returns an array with both numerical and associative indices; fun for the whole family. – hlpiii Oct 10 '09 at 23:41
ahh your right! I was thinking of another function. I revised my answer – SeanDowney Jul 25 '12 at 17:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.