Frequently used functions

This function retrieves only some results from a query. It allows retrieval from a result set to begin at a row other than the first row. For example, executing the function for a result_set with a row parameter of 10, and then issuing a mysql_fetch_row( ), mysql_fetch_array( ), or mysql_fetch_object( ), retrieves the tenth row of the result set.

This function can reduce communications between the database and middle tiers in an application.

The parameter result_set is the result resource handle returned from mysql_query( ). The function returns true on success and false on failure.

This function is an extended version of mysql_fetch_row( ) that returns results into an associative array, permitting access to values in the array by their table attribute names.

Consider an example query on the wine table using the mysql_query( ) function:

$result=mysql_query("SELECT * FROM wine", $connection)

A row can then be retrieved into the array $row using:

$row=mysql_fetch_array($result)

After retrieving the row, elements of the array $row can be accessed by their attribute names in the wine table. For example, echo $row["wine_name"] prints the value of the wine_name attribute from the retrieved row. Attributes can also be accessed by their element numbers. For example, echo $row[1] also works.

There are three tricks to using mysql_fetch_array( ):

• Even though an attribute might be referenced as customer.name in the SELECT statement, it must be referenced as $row["name"] in the associative array; this is a good reason to design databases so that attribute names are unique across tables. If attribute names are not unique, aliases can be used in the SELECT statement; we discuss this later in this chapter.

• Aggregates fetched with mysql_fetch_array( )-for example, SUM(cost)-are associatively referenced as $row["SUM(cost)"].

• NULL values are ignored when creating the returned array. This has no effect on associative access to the array but can change the numbering of the array elements for numeric access.

The second parameter to mysql_fetch_array( ), result_type, controls whether associative access, numeric access, or both are possible on the returned array. Because the default is MYSQL_BOTH, there is no reason to supply or change the parameter.

This function is another alternative for returning results from a query. It returns an object that contains one row of results associated with the result_set handle, permitting access to values in an object by their table attribute names.

For example, after a query to SELECT * from wine, a row can be retrieved into the object $object using:

$object =mysql_fetch_object($result)

The attributes can then be accessed in $object by their attribute names. For example:

echo $object->wine_name

prints the value of the wine_name attribute from the retrieved row. Attributes can also be accessed by their element numbers. For example, echo $object->1 also works.

The second parameter to mysql_fetch_object( ) controls whether associative access, numeric access, or both are possible on the returned array. The default is MYSQL_BOTH, but MYSQL_ASSOC and MYSQL_NUM can also be specified.

This function frees the resources associated with a result_set handle. This process happens when a script terminates, so the function need be called only if repeated querying is performed in one script and MySQL memory use is a concern.

This function returns the number of rows associated with the result_set query result resource handle. This function works only for SELECT queries; queries that modify a database should use mysql_affected_rows( ), which is discussed in Chapter 6.

If the number of rows in a table is required but not the data itself, it is more efficient to run an SQL query of the form SELECT count(*) FROM table and retrieve the result, rather than running SELECT * FROM table and then using mysql_num_rows( ) to determine the number of rows in the table.

This function is a performance-oriented alternative to mysql_connect( ) that reuses open connections to the MySQL DBMS. The p in mysql_pconnect( ) stands for persistent, meaning that a connection to the DBMS stays open after a script terminates. Open connections are maintained as a pool that is available to PHP. When a call to mysql_pconnect( ) is made, a pooled connection is used in preference to creating a new connection. Using pooled connections saves the costs of opening and closing connections.

Whether persistency is faster in practice depends on the server configuration and the application. However, in general, for web database applications with many users running on a server with plenty of main memory, persistency is likely to improve performance.

This function need be called only once in a script. Subsequent calls to mysql_pconnect( ) in any script-with the same parameters-check the connection pool for an available connection. If no connections are available, a new connection is opened.

The function takes the same parameters and returns the same results as its non-persistent sibling mysql_connect( ). It returns a connection resource handle on success that can access databases through subsequent commands; it returns false on failure. The command has the same three optional parameters as mysql_connect( ).

A connection opened with mysql_pconnect( ) can't be closed with mysql_close( ). It stays open until unused for a period of time. The timeout is a MySQL DBMS parameter-not a PHP parameter-and is set by default to five seconds; it can be adjusted with a command-line option to the MySQL DBMS script safe_mysqld. For example, to set the timeout to 10 seconds: safe_mysqld --set-variable connect_timeout=10

This function is available only in PHP 4.0.6 or later. The function executes a query without retrieving and buffering the result set. This is useful for queries that return large result sets or that are slow to execute. The advantage is that no resources are required to store a large result set, and the function returns before the SQL query is complete. In contrast, the function mysql_query( ) doesn't return until the query is complete and the results have been buffered for subsequent retrieval.

The disadvantage of mysql_unbuffered_query( ) is that mysql_num_rows( ) can't be called for the result resource handle, because the number of rows returned from the query isn't known.

The function is otherwise identical to mysql_query( ).