In this lesson, you learned how to communicate with a MySQL database from a program using the C API. In the
Lesson 24. Using MySQL with PHP
In this lesson, you learn how to communicate with a MySQL database from a PHP script.
PHP actually has two different APIs for MySQL, both of which are covered in this chapter. The first, mysql, is the classic set of functions that are available in all versions of PHP.
The new interface is mysqli, which stands for MySQL Improved. It is available only in PHP version 5 and works only with MySQL version 4.1 and higher. The mysqli interface can be used through a set of functions or in an object-oriented manner, to fit the improved OO capabilities of PHP 5.
Using the mysql API
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
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
$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
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. 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";
function works in a similar manner and
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
function. The first time you call
on a result handle, the first row of data is returned and each
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
statement. They also have textual keys that
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
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.
takes a database handle argument. It disconnects from the MySQL server immediately and