PHP: mysql_fetch_array()

PHP includes several functions specifically designed to serve as an interface for MySQL databases, all of which are prefixed with "mysql_".

The purpose of mysql_fetch_array(), is to retrieve ("fetch") a row from a set created via a MySQL query, and then store it in one of the two types of PHP arrays.

MySQL_Fetch_Array() Prototype

array mysql_fetch_array ( resource result [, int result_type] )

Array: This is the return value. The value returned is an array corresponding to the next row in the result set. The exact type of array depends on the value of result_type. Each time mysql_fetch_array() is executed on the same result set, it will return the next row in sequence. Should it reach the bottom and be executed again, mysql_fetch_array() will return FALSE.

Result: This is the result set you wish to fetch the array from.

Result_Type: This determines the type of array returned by mysql_fetch_array(). It can be one of three values:

MYSQL_BOTH - The array returned has both a numerical index and an associative index. This is the default value, and since this parameter is optional, it does not have to be included.

MYSQL_ASSOC - An associative array is returned. This means that the fields are retrieved via an associated key. I.e., $result['FirstName'];

MYSQL_NUM - A numerical array is returned. This means that the fields are accessed via an integer, for example, $result[1] = first field, $result2 = second field, etc.

How to Use MySQL_Fetch_Array()

Lets say we have this table stored in a MySQL table:

Table Name: People

Title	FirstName	Surname
Mr	Joe	Bloggs
Mr	David	Smith
Miss	Jane	Doe

First, we require a query to retrieve the data from the table:

$query = 'SELECT Title, FirstName, Surname FROM People';

Now, we need to execute the query:

$result = mysql_query($query);

$result now contains the three rows from the table above. Now we will use mysql_fetch_array(), in order to store the first row as an array, so we can use it for our program:

$row = mysql_fetch_array($result, MYSQL_BOTH);

$row now contains the following: ['Mr'] ['Jim'] ['Socks']

Because we used MYSQL_BOTH as our result_type, there are 2 ways we can access the data:

1) Numerically - Each cell in our table row, $result, has a numerical index, starting from '0' on the left (title), and ending with '2' on the right (surname). Thus,

$title = $row[0];
$firstname = $row[1];
$surname = $row[2];

One advantage of this method, is that you can access columns with the same name, which can sometimes happen after a joined query, as each column still has its own unique number.

2) Associatively - Alternatively, we can access each cell by using its field name as the the index. Thus,

$theTitle = $row['Title];
$theFirstName= $row['FirstName'];
$theSurname = $row['Surname'];

The disadvantage of using this method is that, should you end up with two columns with the same name, you can only access the right-most column with that name using this method. If you wish to access any other column with the same name, you will need to use an alias in the query.

On the other hand, this method makes the code simpler for others (and yourself) to read.