Statements that return data

It's now time to look at the most common use of SQL, the SELECT statement for retrieving data from a database.

MySQL also supports SHOW, DESCRIBE and EXPLAIN SQL statements for returning results, but we're not going to be considering these here. As usual, the manual contains explanations of these statements.

You will remember from the PostgreSQL chapter that we could either retrieve the data from SQL SELECT statements in a PQexec, where all the data was fetched at once, or use a cursor, where we retrieved data from the database row by row, so that large data sets

MySQL has almost exactly the same choice of retrieval methods, for exactly the same reasons, although it does not actually describe the row-by-row retrieval in terms of cursors. However what it does offer is an API with far fewer differences between the two methods, which will generally make it easier to swap between the two methods, should you ever need to.

Generally there are four stages in retrieving data from a MySQL database:

• issue the query
• retrieve the data
• process the data
• perform any tidy up required

We issue the query with mysql_query, as we did earlier. Retrieving the data is done with either mysql_store_result or mysql_use_result, depending on how we want the data retrieved, followed by a sequence of mysql_fetch_row calls to process the data. Finally we must call mysql_free_result to allow MySQL to perform any required tidying up.

Functions for all-at-once data retrieval

We can retrieve all the data from a SELECT (or other statement that returns data), in a single call, using mysql_store_result:

MYSQL_RES *mysql_store_result(MYSQL *connection);

This function must be called after a mysql_query has retrieved data, to store that data in a result set. This function retrieves all the data from the server and stores it in the client immediately. It returns a pointer to a structure that we haven't met before, a result set structure. A NULL is returned if the statement failed.

As with the PostgreSQL equivalent, be aware that returning a NULL means an error has occurred, and that this is different from no data being retrieved. Even if the returned value is not NULL, it does not mean there is data present to process.

Providing NULL was not returned, you can then call mysql_num_rows and retrieve the number of rows actually returned, which may of course be zero.

my_ulonglong mysql_num_rows(MYSQL_RES *result);

This takes the result structure returned from mysql_store_result, and returns the number of rows in that result set, which may be zero. Providing mysql_store_result succeeded, mysql_num_rows will always succeed.

This combination of mysql_store_result and mysql_num_rows is an easy and intuitive way to retrieve data. Once mysql_store_result has returned successfully, all the query data has been stored on the client, and we know that we can retrieve it from the result structure without risk of further database or network errors occurring, since all the data is now local to our program. We also get to discover the number of rows returned immediately, which can make coding easier. As mentioned earlier, this sends all the results back to the client at once. For large result sets, this can consume enormous quantities of server, network and client resources. For these reasons, when working with larger data sets, it's often better to retrieve the data as we need it. We will see how to do this shortly, using the mysql_use_result function.

Once the data has been retrieved, we can retrieve it with mysql_fetch_row, and also jump around the result set with mysql_data_seek, mysql_row_seek, mysql_row_tell. Before we move on to retrieving the data in stages, let's have a look at these functions.