When you execute a query on a MySQL database through PHP, a resource is returned back to you. This resource contains the information that you queried from the database. However, a resource is not a data type we are used to working with, nor can we work with it directly like an array.
So, we’ll need to use other functions as sort of a bridge between a resource and something we can readily work with (an array). Today, we’ll be looking at a function that will allow you to get the data you retrieved from the database into an associative array. This is the function I use most often when processing database querys.
The function in question in mysql_fetch_assoc(). This function will convert the resource into an associative array, where the field name is the key, and the value of the field is the value of the array. Let’s take a look at an example of how this function works:
[php]$query = mysql_query("SELECT [field_names] FROM [table_name]");
$query_array = mysql_fetch_assoc($query);
print_r($query_array);[/php]
However, this function may not work as you would expect. This function will create an associative array from one of the rows from the given resource, it will then move the (internal) row data pointer forward to the next. So, to get the associate arrays of all the returned rows, we need to use this function in a loop.
As this function will return false once all the rows have been exhausted, the best loop to use would be while. Which you would use in a similar fashion to this:
[php]$query = mysql_query("SELECT [field_names] FROM [table_name]");
while ($query_array = mysql_fetch_assoc($query)) {
print_r($query_array);
}[/php]
You can now iterate through your MySQL resource and process the data how you need to.
