The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

How do I fetch data from a table?

Being connected to a database is nice, sure. But what good is it if we can't get anything from it?

There are a number of ways to fetch data from MySQL, but the most widely used is probably mysql_fetch_array in conjunction with mysql_query.

We just need to add a little more to the connectToDb function we saw in the section called "How do I access a MySQL database?" to fetch data from this table:

Example 3.6. 4.php    
   
// Connect to MySQL    
$dbConn = &connectToDb($host, $dbUser, $dbPass, $dbName);    
   
// A query to select all articles    
$sql = "SELECT * FROM articles ORDER BY title";    
   
// Run the query, identifying the connection    
$queryResource = mysql_query($sql, $dbConn);    
   
// Fetch rows from MySQL one at a time    
while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC)) {    
 echo 'Title: '  . $row['title']  . '<br />';    
 echo 'Author: ' . $row['author'] . '<br />';    
 echo 'Body: '   . $row['body']   . '<br />';    
}

Essentially, there are three steps to getting to your data:

1. First, place the necessary SQL query in a string (if you are unfamiliar with Structured Query Language (SQL), I'll cover the basics throughout this chapter. For a more complete treatment, however, refer to Build Your Own Database Driven Website Using PHP & MySQL, 2nd Edition (ISBN 0–9579218–1–0)) like so:

   $sql = "SELECT * FROM articles ORDER BY title";

   It's handy to keep it in a separate variable, as when we get into writing more complex queries and something goes wrong, we can double-check our query with this one-liner:

   echo $sql;

2. Next, tell MySQL to perform the query:

   $queryResource = mysql_query($sql, $dbConn);

   This can be confusing at first. When you tell MySQL to perform a query, it doesn't immediately give you back the results. Instead, it holds the results in memory until you tell it what to do next. PHP keeps track of the results with a resource identifier, which is what you get back from the mysql_query function. In the code above, we've stored the identifier in $queryResource.

3. Finally, use mysql_fetch_array to fetch one row at time from the set of results:

   while ($row = mysql_fetch_array($queryResource, MYSQL_ASSOC))

   This places each row of the results in turn in the variable $row. Each of these rows will be represented by an array. By using the additional argument MYSQL_ASSOC with mysql_fetch_array, we've told the function to give us an array in which the keys correspond to column names in the table. If you omit the MYSQL_ASSOC argument, each column will appear twice in the array: once with a numerical index (i.e. $row[0], $row[1], etc.), and once with a string index (i.e. $row['title'], $row['author'], etc.). While this doesn't usually cause a problem, specifying the type of array value you want will speed things up slightly.

Using a while loop, as shown above, is a common way to process each row of the result set in turn. The loop effectively says, "Keep fetching rows from MySQL until I can't get any more", with the body of the loop processing the rows as they're fetched.

Forego Buffering on Large Queries

For large queries (that is, queries that produce large result sets), you can improve performance dramatically by telling PHP not to buffer the results of the query. When a query is buffered, the entire result set is retrieved from MySQL and stored in memory before your script is allowed to proceed. An unbuffered query, on the other hand, lets MySQL hold onto the results until you request them, one row at a time (e.g. with mysql_fetch_array). Not only does this allow your script to continue running while MySQL performs the query, it also saves PHP from having to store all of the rows in memory at once.

Of course, all good things come at a price—with unbuffered queries you can no longer use the mysql_num_rows function to count the number of rows. Obviously, as PHP doesn't keep a copy of the complete result set, it is unable to count the rows it contains! You also must fetch all rows in the result set from MySQL before you can make another query.

Although other functions exist for getting rows and cells from query results, like mysql_fetch_object and mysql_result, you can achieve more or less the same things with just mysql_fetch_array, and the consistency may help keep your code simple.