Fetching and Displaying - MySQL

Last week, you began learning how to use PHP to display and modify data from a MySQL database. This week, you'll learn how to select the database, fetch and display data, and more. This article is excerpted from chapter 9 of Learning PHP and MySQL, written by Michele Davis and Jon Phillips (O'Reilly, 2006; ISBN: 0596101104). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

TABLE OF CONTENTS:

By: O'Reilly Media

Poor Best

Rating:

/ 15

May 24, 2007

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Use mysql_fetch_row to get the rows from the result set. It takes the result you stored in $result from the query as a parameter. It returns one row at a time from the query until there are no more rows, and then it returns FALSE. Therefore, you do a loop on the result of mysql_fetch_row and define some code to display each row:

while ($result_row = mysql_fetch_row($result)){
echo $result_row[2] . '
<br />';
}

Fetch types

This is not the only way to fetch the results. Using mysql_fetch_array, PHP can place the results into an array in one step. It takes a result as its first parameter, and the way to bind the results as an optional second parameter. If MYSQL_ASSOC is specified, the results are indexed in an array based on their column names in the query. If MYSQL_NUM is specified, then the number starting at zero accesses the results. The default value, MYSQL_BOTH, returns a result array with both types. The mysql_fetch_ assoc is an alternative to supplying the MYSQL_ASSOC argument.

If you rewrote the code above to use mysql_fetch_array with an associative indexed array, it would look like this:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo $row[title]. '<br />';
}

Closing the Connection

As a rule of thumb, you always want to close a connection to a database when you're done using it. Closing a database with mysql_close will tell PHP and MySQL that you no longer will be using the connection, and will free any resources and memory allocated to it.

mysql_close($connection)

Next: Putting It All Together >>