MySQL is a powerful database engine, especially in combination with PHP. But how do you retrieve information from a dabatase?

In this post I’ll try to explain the difference between mysql_fetch_array, _assoc, _field, _length, _object and _row.

To do this, I have made a new table. This one I’ll use in all my examples:

Table name: members

id | name        | email
--------------------------------------
1  | Pietje Puk  | pietje@puk.invalid
2  | Manneke Pis | pis@mannekes.be

I assume you already know that you can now use the query ‘SELECT name FROM members’ to get all names from the table. In PHP you can use: $result = mysql_query("SELECT name FROM members");

In combination with a mysql_fetch_something, this looks like:

$result = mysql_query("SELECT name FROM members");
while($row = mysql_fetch_xxx($result)) {
 // do something
}

This isn’t too difficult, but there are six mysql_fetch_something’s. So which one should you use, and what’s the difference? In fact, it’s just taste.

mysql_fetch_array(resource [, type])

Returns an array: $row['field'] or $row[number] or both

This fetch makes an array of your $row-variable. This array contains numeric indexes for your fields, identifiers that match the field name, or both (the default). You can specify what you want in the second parameter of the function, with the three constants: MYSQL_ASSOC, MYSQL_NUM and MYSQL_BOTH. The first tow are aliassed in the functions mysql_fetch_assoc and mysql_fetch_num. The last one will return this:

(...)
while($row = mysql_fetch_array($result)) { // or mysql_fetch_array($result,MYSQL_BOTH)
   echo $row['id'] . " = " . $row[0] . " - ";
   echo $row['name'] . " = " . $row[1] . " - ";
   echo $row['email'] . " = " . $row[2] . "<br />";
}

Returns:

1 = 1 – Pietje Puk = Pietje Puk – pietje@puk.invalid = pietje@puk.invalid
2 = 2 – Manneke Pis = Manneke Pis – pis@mannekes.be = pis@mannekes.be

mysql_fetch_assoc(resource)

Returns an array: $row['field']

This fetch is the same as mysql_fetch_array, but without the numeric indexes. So:

(...)
while($row = mysql_fetch_assoc($result)) {
   echo $row['id'] . " = " . $row[0] . " - ";
   echo $row['name'] . " = " . $row[1] . " - ";
   echo $row['email'] . " = " . $row[2] . "<br />";
}

Returns:

1 =  – Pietje Puk =  – pietje@puk.invalid =
2 =  – Manneke Pis =  – pis@mannekes.be =

mysql_fetch_field(resource [, offset])

Returns an object with information about a field

This method is only interesting if you want information about a column of your table. As this doesn’t have anything to do with the purpose of this blog, I won’t explain this one here. For information about this method, please refer to the PHP-manual.

mysql_fetch_lengths(resource)

Returns an array with information about the length of the values returned by other fetch functions (except fetch_field).

This method, like fetch_field, doesn’t really belong hiere. fetch_lengths is only used in combination with the other fetch methods to retrieve the length of the fields. fetch_lengths returns an array with numeric indexes. An example:

(...)
while($row = mysql_fetch_lengths($result)) {
   echo $row['id'] . " = len: " . $row[0] . " - ";
   echo $row['name'] . " = len: " . $row[1] . " - ";
   echo $row['email'] . " = len: " . $row[2] . "<br />";
}

Returns:

= len: 1 -  = len: 10  -  = len: 17
= len: 1  -  = len: 11  -  = len: 14

mysql_fetch_object(resource [, class name [, parameters]])

Returns an object: $row->field

According to the PHP Manual, this function is equal to mysql_fetch_array, except for the fact that this method returns an object instead of an array. And because an object can’t have numbers as identifiers, those are omitted.

(...)
while($rij = mysql_fetch_object($result)) {
   echo $rij->id . " - ";
   echo $rij->naam . " - ";
   echo $rij->email . "<br />";
}

Returns:

1 – Pietje Puk – pietje@puk.invalid
2 – Manneke Pis – pis@mannekes.be

mysql_fetch_row(resource)

Returns an array: $row[number]

This fetch equals to mysql_fetch_array, except that the fields aren’t indexed.

(...)
while($rij = mysql_fetch_row($result)) {
   echo $rij['id'] . " = " . $rij[0] . " - ";
   echo $rij['name'] . " = " . $rij[1] . " - ";
   echo $rij['email'] . " = " . $rij[2] . "<br />";
}

Returns

= 1 -  = Pietje Puk -  = pietje@puk.invalid
= 2 -  = Manneke Pis -  = pis@mannekes.be

In fact it doesn’t matter. It seems to be quite something, but in fact you choose between arrays or objects. About the performance: mysql_fetch_array, _assoc and _object are almost equal to each other in performance, and not significantly slower then mysql_fetch_row.

Just one note: if you’re using field names as ‘name-of-user’, please not that an object can not have properties with that name, and you’re required to use an array. I recommend mysql_fetch_assoc in that case; I haven’t used a numeric identifier or the mysql_fetch_field/_lengths-methods. The objects are a bit easier to use in strings, but also arrays can be used in them (think of "Hello {$row['name']}").

This post appeared earlier in Dutch on my previous weblog.