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:

 <?php phpinfo();?> 

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.

Configuring PHP

At compile time, MySQL support is enabled by using the --with-mysql configure switch. This option was enabled by default in PHP version 4 but is not in PHP 5.

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:

  1. serverThe hostname of the MySQL server. This can be an IP address, domain name or hostname, or localhost where the MySQL server and web server are running on the same host.

  2. usernameThe username for database authentication.

  3. passwordThe password that corresponds to the username passed in argument 2.

  4. new_linkPass a nonzero value in this argument to cause a second call to mysql_connect() to create a separate database link. Otherwise, any existing connection will be reused.

  5. client_flagsThis argument can be used to enable certain features of the client library that are not covered in this book. Omit this argument unless you have a specific need.

Server Port

There is no port argument in the mysql_connect() function. Instead, to change the TCP/IP port used, add a colon followed by the port number to the server argumentfor example, myserver.mydomain:1234.

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:

 mysql_select_db("mysql10", $conn); 


Because selecting a database is done separately, the mysql_connect() function might succeed even if the database you intend to use is not available to the given user. You will receive an authentication error from mysql_select_db() if the user is not allowed to access the required 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

 <?php $conn = mysql_connect("localhost", "user", "pass"); if (!$conn) {    echo "Unable to connect to MySQL server <>";    echo "Error " . mysql_errno() . " - " . mysql_error();      exit; } if (!mysql_select_db("mysql10")) {      echo "Unable to select database <>";      echo "Error " . mysql_errno() . " - " . mysql_error();      exit; } ?> 

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.

Affected Rows

If the query was an UPDATE statement, the value returned by mysql_affected_rows() will be the number of rows in which a value was changed from its previous value. This number might be less than the total number of rows matched by the WHERE clause.

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[0] would be the code for the first product returned, and $result[1] 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

 <?php $conn = mysql_connect("localhost", "user", "pass"); if (!$conn) {   echo "Unable to connect to MySQL server <>";   echo "Error " . mysql_errno() . " - " . mysql_error();   exit; } if (!mysql_select_db("mysql10")) {   echo "Unable to select database <>";   echo "Error " . mysql_errno() . " - " . mysql_error();   exit; } $sql = "SELECT * FROM customers ORDER BY name"; $result = mysql_query($sql, $conn); if (!$result) {   echo "Unable to execute query <>";   echo "Error " . mysql_errno() . " - " . mysql_error();   exit; } $numrows = mysql_num_rows($result); $numcols = mysql_num_fields($result); echo "<table border=1>\n"; while ($data = mysql_fetch_array($result)) {   echo "<tr>";   for ($i=0; $i<$numcols; $i++) {     echo "<td>" . $data[$i] . "</td>\n";   }   echo "</tr>\n"; } echo "</table>\n"; ?> 

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.

Tidying Up

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.

Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: