Displaying Query Results

The majority of query commands that are sent to MySQL are of the SELECT nature in which a set of data
will be returned from the query. Understanding how to deal with the query results from SELECT
statements is probably one of the most important tasks associated with PHP and MySQL interactions.
There are four main ways to accomplish this:

• mysql_result($query_result, $row_id, $column_name) – Using the mysql_result()
function will return a single row, single column of data from the query output. The $row_id is an
offset numeric identifier (0 being the first row, 1 being the second row and so on). The
$column_name is the actual column identifier that is returned by MySQL. In the event that the query
used an alias to identify the column, the alias name must be used to identify the column. In a majority
of cases, a single row of data is not that useful; however, using a looping construct with the

mysql_result() function can prove quite useful:

$query = "SELECT * FROM world.Country LIMIT 5";
$query_results = mysql_query($query, $linkID1);
for ($count=0; $count <= mysql_numrows($query_results); $count++)
{
$country_code = mysql_result($query_results, $count, 'Code');
$country_name = mysql_result($query_results, $count, 'Name');
print "$country_name($country_code)<br>\n";
}

This portion of a PHP script would return the Country Name and the associated Country Code, in
parenthesis, for every record in the Country table located in the world database. The
mysql_numrows($query_results) is a function that returns and integer of the number of rows that is
returned from the query executed. Thus in this case, the for loop construct is terminated when it
reaches the end of the rows returned.


mysql_fetch_row($query_result) – The mysql_fetch_row() function is a step up from the
mysql_result() function by retrieving and storing the entire row of data into an indexed array. The
array can then be parsed, by their numeric index location, for the specific results that are desired. By
itself, the advantage may not be much over the mysql_result() function; however, combining this
function with the list() function can provide a much more versatile tool.

$query = "SELECT Code, Name FROM world.Country LIMIT 5,5";
$query_results = mysql_query($query, $linkID1);
while (list($country_name, $country_code) =
mysql_fetch_row($query_results)) {
print "$country_name($country_code)<br>\n";
}

This portion of a PHP script is identical to the previous example used with the mysql_result()
function. The differences between the two scripts include a more defined SQL query statement
(which is a best practice anyways), the use of a while loop construct (instead of a for loop construct)
and the use of the list() function to store the mysql_fetch_row() associated array into two variables
used to create the output contained therein.

• mysql_fetch_array($query_result) – The mysql_fetch_array() function is a step up from the
mysql_fetch_row() function by retrieving and storing the entire row of data into an index and
associative array. The array can then be parsed, either by the numeric index location or by the
column name value, for the specific results that are desired.

$query = "SELECT Code, Name FROM world.Country LIMIT 10,5";
$query_results = mysql_query($query, $linkID1);
while ($row = mysql_fetch_array($query_results, MYSQL_BOTH)) {
$country_code = $row['Code'];
$country_name = $row['Name'];
print "$country_name($country_code)<br>\n";
}

In the above example, the returned result is stored both as an index and an associative array called
$row. The contents of the $row array are then parsed by their associated column names and displayed
duplicating the previous examples. They could of easily have been parsed by their index location in
the array ($row[0] and $row[1]). However, if there is no need for only one array method, the result
type can be modified to provide only one array type:

o MYSQL_ASSOC - Replacing MYSQL_BOTH, in the above example, and replacing it with
MYSQL_ASSOC will result in the $row array containing only an associative array. The
process of calling the values from the array, is by using the column name as the key
representing the data (as shown in the example above). The mysql_fetch_assoc() function is
identical to this function combination.

o MYSQL_NUM - Replacing MYSQL_BOTH, in the above example, and replacing it with
MYSQL_NUM will result in the $row array containing only an indexed array. The process of
calling the values from the array, is by using the numeric indicator as the key representing the
data. In this example above, $country_code = $row[0] and $country_name =
$row[1] is how the array would be parsed to give the same output



mysql_fetch_object($query_result) – The mysql_fetch_object() function is identical to the
mysql_fetch_array() function with the exception that an object is returned rather than an array. The
array can then be parsed, using object oriented programming, for the specific results that are desired.

$query = "SELECT Code, Name FROM world.Country";


$query_results = mysql_query($query, $linkID1);


while ($row = mysql_fetch_object($query_results)) {
$country_code = $row->Code;
$country_name = $row->Name;
print "$country_name($country_code)<br>\n";
}

In the above example, it is clear that there is not a large advantage from objects to arrays; however,
the use of Objected Oriented programming techniques enhances PHP in many other areas and this
capability can be incorporated in those areas.