The classic mysql interface for PHP is a set of functions that map very closely to the C API functions you learned about in Lesson 23, "Using MySQL with C." To check whether this module is included in your PHP installation, create a simple script that simply contains the following line:
View this script in a web browser and look for a section headed MySQL Support. If this section is not present, you will not be able to use the MySQL commands in PHP, and you will have to recompile PHP to include this module.
Connecting to MySQL
Use the mysql_connect() function to establish a connection to a MySQL database. The first three arguments are the server hostname, username, and password for the connection. The following statement opens a new connection to a local MySQL server and assigns a connection handle named $conn:
$conn = mysql_connect("localhost", "user", "pass");
You must assign the connection handle resource returned by mysql_connect() so that you can use it later. Most MySQL API functions require this to be passed as an argument.
The mysql_connect() function prototype actually has five optional arguments:
The return value from mysql_connect() is FALSE if the connection fails for any reason. You can then use the mysql_errno() and mysql_error() functions to return the MySQL error number and message text, respectively.
The argument to both error functions is a database connection handle, but the argument can be omitted if there is only one open connection in your script. The default behavior is to use the most recently opened connection.
The connection to the database server is made without selecting a database to work with. This must be done using a separate function, mysql_select_db(). The first argument is the name of the database to use. You can pass a connection handle as an optional second argument or omit this argument to use the most recently opened connection.
The following statement causes any queries executed using the $conn connection handle to use the mysql10 database:
Listing 24.1 performs both steps, connecting to a MySQL server and selecting a database to use, with error checking along the way.
Listing 24.1. Establishing a New Database Connection Using the Classic MySQL API
Executing a Query
After you have established a connection to a MySQL server, you can execute a query using the mysql_query() function. This function takes two arguments: a database connection handle and a string containing a single SQL query. The terminating semicolon is not required. The following is an example using a database resource named $conn:
$result = mysql_query($conn, "SELECT * FROM products");
The return value from mysql_query() is a result handle that you then use to find information about the query and fetch records if the query was a SELECT statement.
The result is FALSE if the query failed for any reason; you can then use mysql_errno() and mysql_error() to find out more information.
Finding Information About a Query
If the query you executed was a SELECT statement, you can find how many data rows it returned using the mysql_num_rows() function. It takes a single argumenta query result handle returned by mysql_query()and returns the number of rows in the resulting data set. The following is an example:
echo mysql_num_rows($result). " row(s) were returned";
The mysql_num_fields() function works in a similar manner and tells you the number of columns in the data set.
If the query you executed was an UPDATE, INSERT, or DELETE statement, you can find out how many table rows were affected using the mysql_affected_rows() function. It also takes a result handle argument and returns the number of rows that were affected by the query.
Fetching Queried Data
The most powerful way to fetch data from a result handle is with the mysql_fetch_row() function. The first time you call mysql_fetch_row() on a result handle, the first row of data is returned and each subsequent call returns the next row until no data remains to be fetched.
The data is returned into an array that, by default, has both numeric and associative indexes. The elements are numbered from zero in the order they appear in the SELECT statement. They also have textual keys that correspond to the column name or alias from the query.
For example, suppose your script executes the query SELECT code, price FROM products ORDER BY price and then uses mysql_fetch_row() to return the first data row into $result. The value of $result would be the code for the first product returned, and $result would hold the corresponding price value. However, these values could also be accessed as $result["code"] and $result["price"].
Listing 24.2 puts everything together into a script that connects to MySQL, executes a query, and fetches and displays the resulting data.
Listing 24.2. Performing a Query Using the Classic MySQL API
The output from this script is in an HTML table format. Each time a new record is fetched, the script outputs a <tr> tag and closes it with </tr> at the end of the loop. Each individual data item is enclosed in <td> and </td>. Running this script produces output similar to that shown in Figure 24.1.
Figure 24.1. Output from sample query script.
When your script has finished running, any associated resources are automatically deallocated. However you might want to do some housekeeping in your script to free up resources that you are done with.
The mysql_free_result() function destroys a result handle and deallocates its resources. You then cannot use that result handle with any of the mysql API functions.
The function mysql_close() takes a database handle argument. It disconnects from the MySQL server immediately and frees up the associated resources.