resource mysql_connect([string host], [string username], [string password])

Establishes a connection to the MySQL DBMS. The function returns a connection resource handle on success that can be used to access databases through subsequent commands. Returns false on failure (error handling is discussed later in this section).

The command has three optional parameters, all of which-host, username, and password-are used in practice. The first permits not only the hostname, but also an optional port number; the default port for MySQL is 3306 (ports are discussed in more detail in Appendix B). However, when the DBMS runs on the same machine as the PHP scripting engine and the web server-and you have set up a database user that can access the DBMS from the local machine-the first parameter need only be localhost.

In Example 4-1, the function call:

mysql_connect("localhost", "fred", "shhh")

connects to the MySQL DBMS on the local machine with the username fred and a password of shhh. As discussed in the last section, you should replace these with the username and password values you chose in Appendix A and used in Chapter 3. If the connection is successful, the returned result is a connection resource handle that should be stored in a variable for use as a parameter to other MySQL functions.

This function needs to be called only once in a script, assuming you don't close the connection (see mysql_close( ), later in this section). Indeed, subsequent calls to the function in the same script with the same parameters-the same host, username, and password triple-don't return a new connection. They return the same connection handle returned from the first successful call to the function.

int mysql_select_db (string database, [resource connection])

Uses the specified database on a connection. In Example 4-1, the database winestore is used on the connection returned from mysql_connect( ). If the second parameter is omitted, the last connection opened is assumed, or an attempt is made to open a connection with mysql_connect( ) and no parameters. We caution against omitting the connection parameter.

resource mysql_query(string SQL_command, [resource connection])

Runs the SQL statement SQL_command. In practice, the second argument isn't optional and should be a connection handle returned from a call to mysql_connect( ). The function mysql_query( ) returns a resource-a result handle that can fetch the result set-on success, and false on failure.

In Example 4-1, the function call:

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

runs the SQL query SELECT * FROM wine through the previously established DBMS connection resource $connection. The return value is assigned to $result, a result resource handle that is used as a parameter to mysql_fetch_row( ) to retrieve the data.

The query string passed to mysql_query( ) or mysql_unbuffered_query() doesn't need to be terminated with a semicolon; the latter function is discussed later in this section.

If the second parameter to mysql_query( ) is omitted, PHP tries to use any open connection to the MySQL DBMS. If no connections are open, a call to mysql_connect( ) with no parameters is issued. In practice, the second parameter should be supplied.

array mysql_fetch_row(resource result_set)

Fetches the result set data one row at a time by using as a parameter the result handle result_set that was returned from an earlier mysql_query( ) function call. The results are returned as an array, and the elements of the array can then be processed with a loop statement. The function returns false when no more rows are available.

In Example 4-1, a while loop repeatedly calls the function and fetches rows into the array variable $row until there are no more rows available.

int mysql_num_fields(resource result_set)

Returns the number of attributes associated with a result set handle result_set. The result set handle is returned from a prior call to mysql_query( ).

This function is used in Example 4-1 to determine how many elements to process with the for loop that prints the value of each attribute. In practice, the function might be called only once per query and the returned result assigned to a variable that can be used in the for loop. This is possible since all rows in a result set have the same number of attributes. Avoiding repeated calls to DBMS functions where possible is likely to improve performance.

The array function count( ) can also be used to count the number of elements in an array.

int mysql_close([resource connection])

Closes a MySQL connection that was opened with mysql_connect( ). The connection parameter is optional. If it is omitted, the most recently opened connection is closed.

As we discuss later, this function doesn't really need to be called to close a connection opened with mysql_connect( ), because all connections are closed when a script terminates. Also, this function has no effect on persistent connections opened with mysql_pconnect( ); these connections stay open until they are unused for a specified period. We discuss persistent connections in the next section.

The functions we have described are a contrasting approach for DBMS access to the consolidated interface of the MySQL command line interpreter. mysql_connect( ) and mysql_close( ) perform equivalent functions to running and quitting the interpreter. The mysql_select_db( ) function provides the use database command, and mysql_query( ) permits an SQL statement to be executed. The mysql_fetch_row( ) and mysql_num_fields( ) functions manually retrieve a result set that's automatically output by the interpreter.

More MySQL Functions in PHP

Web database applications can be developed that use only the six functions we have described. However, in many cases, additional functionality is required. For example, database tables sometimes need to be created, information about database table structure needs to be used in reporting or querying, and it is desirable to retrieve specific rows in a result set without processing the complete dataset.

Additional functions for interacting with a MySQL DBMS using PHP are the subject of this section. We have omitted functions that are used to report on insertions, deletions, and updates. These are discussed in Chapter 6.