All the different ways to retrieve data

[glowball]

Hi all, I just wanted to see how everyone else was doing it because I can't find any good examples of what functions to use to return MySQL data. There are a few of them, which are best? Here are my impressions, tell me where I'm going wrong:

• mysql_fetch_object and mysql_fetch_array are the same thing, so you should use whichever one you personally like (if you're comfortable with objects or arrays)

• mysql_fetch_row should be used if you're only getting one row back

• mysql_result should be used if you're only getting one field back

• mysql_free_result is overkill because PHP already takes care of releasing memory

Yes? No? Thanks!

[mithril]

Glowball,

mysql_fetch_object and mysql_fetch_array are not the same thing. One returns an object which has x number of object variables, each named after a fieldname from the result set. The other returns an array with either numerical indices, associative indices, or both (the default). In reality, mysql_fetch_array and mysql_fetch_row are much more closely related, as mysql_fetch_array is an enhanced version of mysql_fetch_row.

mysql_result is up to your disgression, but I personally find that mysql_fetch_array/row is fine for all queries and never use it. As to mysql_free_result, it is overkill in most cases as the PHP engine destroys it's variables at the end of the script run. However, it can be very useful if you're worried about the about of memory used on the server to store result sets in variable.

geoff

[[deleted]]

Use whichever method suits your needs.
From a performance point of view there really isn't that much difference.
From a userfriendly point of view I find that mysql_fetch_aray($result, MYSQL_ASSOC) is most friendly.

"mysql_free_result is overkill because PHP already takes care of releasing memory"

No, PHP does not 'take care of releasing memory', PHP cleans up the mess that you left when you are too lazy to clean up yourself.

This is a failsafe feature that's only there because so many programmers think it's overkill to release memory themselves.

[Anon]

mysql_fetch_row should be used if you're only getting one row back

The number of rows in the result has nothing to do with it. The three main fetch functions differ as follows:

  mysql_fetch_object() - columns put into member fields of object
  mysql_fetch_array() - columns put into associative array
  mysql_fetch_row() - columns put into numerically-indexed array

Neither am I comfortable with the statement "if you're comfortable with objects or arrays". If you're not comfortable with objects and arrays, you're going to be extremely limited in what you can do with PHP, so the right response to that is to get comfortable with them.

Oh, and one final word on mysql_fetch_row(): just because it returns its data as an array, you don't have to use it that way. Who wants to write

  $data = mysql_fetch_row( $result );
  echo $data[0];

or even:

  $data = mysql_fetch_row( $result );
  $userid = $data[0];
  echo $userid;

The fix for this is to use the list() pseudo-function, like this:

  list( $userid, $name, $last_logon) = mysql_fetch_row( $result );

Now you can directly use the named variables.

[glowball]

Great tips everyone, thanks a lot! I was wondering if the way I was doing things was the way everyone else was doing things or if I was being a bonehead.

Haven't been destroying stuff at the end, guess I'll start doing that...