- resource mysql_connect([string
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
falseon 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
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
Uses the specified
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
- resource mysql_query(string
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
In Example 4-1, the function call:
$result=mysql_query("SELECT * FROM wine", $connection)
runs the SQL query
winethrough 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_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
Fetches the result set data one row at a time by using as a parameter the result handle
result_setthat 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
falsewhen no more rows are available.
In Example 4-1, a
whileloop repeatedly calls the function and fetches rows into the array variable
$rowuntil there are no more rows available.
- int mysql_num_fields(resource
Returns the number of attributes associated with a result set handle
result_set. The result set handle is returned from a prior call to
This function is used in Example 4-1 to determine how many elements to process with the
forloop 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
forloop. 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
Closes a MySQL connection that was opened with
mysql_connect( ). The
connectionparameter 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.