Developing MySQL Database Applications With PHP Part 2

Test

Using the MySQL Extension, mysql
This series of tutorials shows the essential steps involved in installing PHP with MySQL extensions mysql, mysqli, and pdo_mysql, and with support for the MySQL native driver for PHP, mysqlnd. The tutorials also provide simple examples to connect, insert, and retrieve data from a MySQL database. Because the focus is on database connectivity from a PHP script, these tutorials assume that some kind of MySQL database is already up and accessible from the client machine.
For general information about accessing MySQL with PHP database extensions, a list of the major PHP API offerings, and instructions on setting up the test database used in these tutorials, see the first sections of the first tutorial in this series, Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli.
Application developers who are new to the development of MySQL database applications with PHP are the target audience of this article.
Contents

• Use the MySQL Extension, mysql
• Install PHP With the mysql Extension
• Test the MySQL Database Connectivity Using the PHP mysql Extension
• SQL Safe Mode
• Persistent Database Connections
• Explore Other Tutorials in This Series
• For More Information
  

Use the MySQL Extension, mysql

The mysql extension is the original extension designed to develop PHP applications that interact with a MySQL database. The ext/mysql extension provides a procedural interface and is intended for use only with MySQL versions older than 4.1.3. This extension is compatible with versions of MySQL 4.1.3 or newer, but all the latest MySQL features are not accessible through ext/mysql. It is strongly discouraged to use ext/mysql in new developments. Use ext/mysqli instead to intereact with MySQL 4.1.3 and later versions. For more information, see the tutorial Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli.
Install PHP With the mysql Extension

The PHP MySQL extension, mysql, is compiled into PHP 4. Hence the ext/mysql extension is enabled by default in all PHP 4 installations. However in PHP 5 and later versions, ext/mysql is no longer enabled by default, nor is the MySQL library bundled with PHP. Users building PHP 5 from the source code must use the --with-mysql[=MySQL_HOME_DIR] configure option to include MySQL support, where MySQL_HOME_DIR is the absolute path to the MySQL installation directory. Then ext/mysql uses the MySQL client library, libmysql, to talk to the MySQL database server.
Alternatively, if you wish to use the MySQL native driver for PHP (mysqlnd) in place of the MySQL client library (libmysql) to communicate with the MySQL server, check the installation instructions in the tutorial Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd.
Sun Solaris OS users can download the optimized PHP 5 packages for Solaris OS from either the Cool Stack or from the Web Stack. Once installed, make sure that the mysql extension is enabled by uncommenting the line extension="mysql.so" in the PHP configuration file, php.ini. The directory /opt/coolstack/php5/lib contains the php.ini configuration file in the Cool Stack installation, whereas the /usr/php5/<version> directory contains the php.ini configuration file in the Web Stack PHP installation.
For example:

bash# grep -w mysql.so php.ini         extension="mysql.so"

Check the PHP Manual for the installation steps for platforms other than Solaris.  Also check the Runtime Configuration to configure the php.ini file that affects the behavior of the MySQL functions.
Test the MySQL Database Connectivity Using the PHP mysql Extension

The following sample PHP script shows how to connect to a MySQL database running on the same host, using the mysql extension. The code sample connects to the MySQL database test by using the ext/mysql API, sends out a query to retrieve all the rows from the table City, extracts the data from the result set, and finally displays the data on the standard output. To minimize the dependencies, this script is shown as a stand-alone script rather than a PHP page that can be accessed over the web server.
The sample code is provided for the purpose of demonstration only; it is not meant to indicate that readers should adopt a particular style of coding. To keep it simple, the sample code assumes that the user always provides well-formed input, hence there is no explicit error checking code in the following example. Use discretion in reusing the sample code.

1. bash# cat PHPmysqlClient.php
   
2. 
   
3. <?php
   
4. 
   
5.         $host="localhost:3306";
   
6.         $user="root";
   
7.         $password="admin";
   
8.         $dbname="test";
   
9. 
   
10.         $cxn = mysql_connect ($host, $user, $password)
    
11.                 or die ('Could not connect to the database server' . mysql_error() );
    
12. 
    
13.             /* select the database */
    
14.             mysql_select_db ($dbname, $cxn)
    
15.                   or die ("Unable to select database" . mysql_error());
    
16. 
    
17.         $query = "SELECT * FROM City";
    
18.         
    
19.         $result = mysql_query ($query, $cxn)
    
20.                 or die ("Could not execute the query." . mysql_error());
    
21. 
    
22.         /* find the number of rows in the result set */
    
23.             $nrows = mysql_num_rows ($result);
    
24. 
    
25.         echo "Retrieved $nrows row(s):\n\n";
    
26. 
    
27.         echo "CityName\n--------\n";
    
28. 
    
29.         while ($row = mysql_fetch_assoc ($result))
    
30.         {
    
31.                 $data1 = $row["CityName"];
    
32.                 echo "$data1\n";
    
33.         }
    
34. 
    
35.         mysql_free_result($result);
    
36. 
    
37.         /* close the database connection */
    
38.         mysql_close($cxn);
    
39. ?>
    
40. 
    
41. bash# /opt/coolstack/php5/bin/php PHPmysqlClient.php
    
42. 
    
43. Retrieved 3 row(s).
    
44. 
    
45. CityName
    
46. --------
    
47. Hyderabad, India
    
48. San Francisco, USA
    
49. Sydney, Australia

复制代码

Some of the important steps in the above code sample are explained below.
Make a Connection to the Database
Use the mysql_connect() method of the ext/mysql interface to connect to the data source and to obtain a handle for that connection.

handle mysql_connect ([string $host[, string $username [, string $password [, bool $new_link [, int $client_flags]]]]] )

The hostname of the MySQL server is $host. It can also include a port number, for example, hostname:port or a path to a local socket, for example, :/path/to/socket for the localhost. This is an optional parameter. The hostname, if not specified or if specified as null or localhost or localhost:3306, defaults to a MySQL server running on the local machine using the UNIX socket. If the host is localhost, then the port number is ignored and 3306 is assumed by default. If the MySQL server is running on any port other than the default port 3306, use the IP address 127.0.0.1 in place of localhost for the $host argument. To connect to a MySQL server on the local machine via TCP, you must specify the loopback IP address, 127.0.0.1, for the host. In the safe mode, this parameter is ignored and the value localhost:3306 is always used.

Test

SQL Safe Mode
When the safe mode is turned on, database connect functions that specify default values will use those values in place of the supplied arguments. To enable the safe mode, edit the safe_mode parameter in php.ini as shown below.

1. [PHP]
   
2. ...
   
3. safe_mode = On

复制代码

Examples of valid $host values for MySQL:

1.   null
   
2.   localhost
   
3.   localhost:3306
   
4.   127.0.0.1
   
5.   127.0.0.1:3306
   
6.   :/tmp/mysql.sock

复制代码

The parameters $username and $password are the database username and the password respectively. In the safe mode, the name of the user that owns the server process and an empty password overrides the supplied username and the password.The method mysql_connect() can be used to reuse a connection to a MySQL server. If a second call is made to mysql_connect() with the same arguments, no new connection is established, but the connection handle of the already opened database connection is returned instead. Setting the $new_link parameter to TRUE modifies this default behavior and forces mysql_connect() to open a new database connection even if mysql_connect() was called before with the same parameters. In the safe mode this optional parameter is ignored.

The $client_flags parameter can be a combination of the following pre-defined constants:

• 128 - Enable LOAD DATA LOCAL handling.
• MYSQL_CLIENT_SSL - Use SSL encryption.
• MYSQL_CLIENT_COMPRESS - Use compression protocol.
• MYSQL_CLIENT_IGNORE_SPACE - Allow space after function names.
• MYSQL_CLIENT_INTERACTIVE - Allow interactive_timeout (instead of wait_timeout) seconds of inactivity before closing the connection.
  

In the safe mode, this optional parameter is ignored.
In the sample PHP script, the following code fragment connects to the MySQL server running on the localhost on the default port 3306 and returns a database connection handle.

1. $host="localhost:3306";
   
2. $user="root";
   
3. $password="admin";
   
4. 
   
5. $cxn = mysql_connect ($host, $user, $password)
   
6.         or die ('Could not connect to the database server' . mysql_error() )

复制代码

Persistent Database Connections
Persistent connections improve performance for some applications. When persistent connections are created from the application, the database connection is opened once and kept in pool for the application's lifetime. The PHP runtime tries to reuse the existing database connections from the pool whenever there is a request to connect to the database.
You can switch to persistent connections in MySQL by changing the function call from mysql_connect() to mysql_pconnect(). Both of those methods accept the same parameters.

resource mysql_pconnect ([ string $server [, string $username [, string $password [, int $client_flags]]]] )

The call mysql_pconnect() establishes a persistent connection to a MySQL server. This method acts very much like mysql_connect() with two major differences:

• When connecting, the function first tries to find a persistent connection that is already open with the same host, username, and password. If it finds one, an identifier for it is returned instead of opening a new connection.
• The connection to the SQL server is not closed when the execution of the script ends. Instead, the connection remains open for future use. Note that calling mysql_close() does not close the database connection established by mysql_pconnect().
  

While persistent connections can improve performance for some applications, especially running over slow network links, they can lead to trouble if not used properly. For example, if you lock a table and close the database connection before unlocking it, normally it is unlocked when the connection is terminated. However because persistent connections do not close, any tables you accidentally leave locked will remain locked, and the only way to unlock them is probably to wait for the connection to time out or kill the process. Also due to the persistent nature of the connections, the process might consume more resources. To alleviate this issue, it might be a good idea to register a function that does an explicit rollback of non-committed transactions on script shutdown, with register_shutdown_function(). Check the Using persistent connections with MySQL page at mysql.com to learn more about the advantages and disadvantages of using persistent connections and decide for yourself whether or not to use persistent connections in your application.

Select the Database to Use


Once the database connection is established, the next step is to select any database on the MySQL server as the default (current) database for subsequent transactions. The mysql_select_db() method of ext/mysql can be used to select a MySQL database programmatically.

bool mysql_select_db (string $database_name [, handle $link_identifier])

where $database_name is the name of the database that is to be selected, and $link_identifier is an optional parameter that represents the database connection handle. If the connection handle is not specified, by default, the last connection opened by mysql_connect() is assumed. If no such connection is found, it tries to create one as if mysql_connect() was called with no arguments.

The method mysql_select_db() returns TRUE on success and FALSE on failure. If the current active database is set successfully, every subsequent call to mysql_query() will be made against the active database.
In the sample code, the following code fragment tries to set test as the active database.

1. $dbname="test";
   
2. 
   
3. mysql_select_db ($dbname, $cxn)
   
4.         or die ("Unable to select database" . mysql_error());

复制代码

Execute the SQL Statement


Use the mysql_query() method of ext/mysql to execute repeated SELECT and non-SELECT statements. The query mysql_query() sends a unique query (multiple queries or Multiple Statements are not supported) to the currently active database on the server that is associated with the specified database connection handle.

resultset mysql_query ( string $query [, handle $link_identifier])

where $query is a single SQL DDL or DML statement to execute without the terminating semicolon or \g, and $link_identifier is the optional parameter that represents the database connection handle. If the connection handle is not specified, by default, the last connection opened by mysql_connect() is assumed. If no such connection is found, it tries to create one as if mysql_connect() was called with no arguments.

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning a result set, mysql_query() returns a result set on success, or FALSE on error.

For other type of SQL statements like INSERT, UPDATE, DELETE, or DROP,  mysql_query() returns TRUE on success or FALSE on error.

In the code sample, the following code fragment executes the SQL query.

1. $query = "SELECT * FROM City";
   
2.        
   
3. $result = mysql_query ($query, $cxn)
   
4.         or die ("Could not execute the query." . mysql_error());
   
5. }

复制代码

Let's try to insert a few rows into the City table by calling the same method, mysql_query(). Before we delve any further, note that MySQL runs with autocommit mode enabled by default. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent. To disable autocommit mode, use the following statement in your MySQL environment:

SET autocommit=0;

After disabling autocommit mode by setting the autocommit variable to zero, changes to transaction-safe tables such as those for InnoDB and NDBCLUSTER are not made permanent immediately. You must explicitly use COMMIT to store your changes to disk or ROLLBACK to ignore the changes.

To disable autocommit mode for a single series of statements, use the START TRANSACTION statement:

1. START TRANSACTION;
   
2. INSERT INTO <table> VALUES (..);
   
3. COMMIT;

复制代码

With START TRANSACTION, autocommit remains disabled until you end the transaction with COMMIT or ROLLBACK. The autocommit mode then reverts to its previous state.
The following code fragment tries to insert two rows, but only one of those records is committed to the database, whereas the other one is discarded.

1.         ...
   
2.         ...
   
3. 
   
4.         $qry1 = "INSERT INTO City VALUES ('London, UK')";
   
5.         $qry2 = "INSERT INTO City VALUES ('Paris, France')";
   
6. 
   
7.         mysql_query ("START TRANSACTION", $cxn)
   
8.                 or die ("Could not execute the START TRANSACTION." . mysql_error());
   
9. 
   
10.         mysql_query ($qry1, $cxn)
    
11.                 or die ("Could not execute the INSERT." . mysql_error());
    
12. 
    
13.         mysql_query ("COMMIT", $cxn)
    
14.                 or die ("Could not COMMIT." . mysql_error());
    
15. 
    
16.         mysql_query ("START TRANSACTION", $cxn)
    
17.                 or die ("Could not execute the START TRANSACTION." . mysql_error());
    
18. 
    
19.         mysql_query ($qry2, $cxn)
    
20.                 or die ("Could not execute the INSERT." . mysql_error());
    
21. 
    
22.         mysql_query ("ROLLBACK", $cxn)
    
23.                 or die ("Could not COMMIT." . mysql_error());
    
24. 
    
25.         ...
    
26.         ...

复制代码

Test

Retrieve the Data From the Result Set


The mysql extension has multiple methods to retrieve the data from a result set. Because the mysql_fetch_assoc() method was used in the sample PHP script, let's start by looking at its syntax.

array mysql_fetch_assoc (resource $result)

The method mysql_fetch_assoc() accepts the result set that is returned by mysql_query() as the input, and returns an associative array of strings that corresponds to the fetched row. It returns FALSE when there are no more rows to fetch. An associative array consists of a collection of unique keys and a collection of values where each key is associated with one value. In the context of mysql_fetch_assoc() returned associative array, the name of each column is a key in the array. The following code fragment in the sample script retrieves all the rows in the result set and displays them on the standard output.

1. while ($row = mysql_fetch_assoc ($result))
   
2. {
   
3.         $data = $row["CityName"];
   
4.         echo "$data\n";
   
5. }

复制代码

The above code fragment can be rewritten using different mysql_fetch_XXX() methods of the ext/mysql as shown below.

• Using mysql_fetch_row()
  
  

  array mysql_fetch_row (resource $result)

  
  
  The method mysql_fetch_row() accepts the result set that is returned by mysql_query() as the input and returns an enumerated array that corresponds to the fetched row. Each result column is stored in an array offset, starting at offset 0.
  
  For example:
  
  
  1. while ($row = mysql_fetch_row ($result))
     
  2. {
     
  3.         $data = $row[0];
     
  4.         echo "$data\n";
     
  5. }

  复制代码
  
  $row[0] contains the first column of the row being fetched, that is, the CityName column of the table City.
  
• Using mysql_fetch_array()
  
  

  array mysql_fetch_array (resource $result [, int $result_type])

  
  
  The method mysql_fetch_array() accepts the mandatory result set that is returned by mysql_query() and an optional result type -- the type of array to be fetched -- as the input, and returns an associative array, a numeric array, or both based on the $result_type argument. The $result_type is a constant and can take the values MYSQL_ASSOC, MYSQL_NUM, and the default value of MYSQL_BOTH.
  
  The type of returned array depends on how the $result_type is defined. By using MYSQL_BOTH (default), you get an array with both associative and number indices. Passing MYSQL_ASSOC as the $result_type to mysql_fetch_array() makes it behave like mysql_fetch_assoc(), and the value of MYSQL_NUM for the $result_type results in similar behavior to that of mysql_fetch_row().
  
  For example:
  
  1. 1. MYSQL_BOTH for $result_type
     
  2. 
     
  3. // mysql_fetch_array ($result, MYSQL_BOTH) is equivalent to:
     
  4. // mysql_fetch_array ($result)
     
  5. while ($row = mysql_fetch_array ($result, MYSQL_BOTH))
     
  6. {
     
  7.         $data = $row[0];
     
  8.         $data1 = $row["CityName"];
     
  9. 
     
  10.         // both $data and $data1 hold the same piece of data
      
  11.                echo "$data\n";
      
  12.         //echo $data1\n";
      
  13. }
      
  14. 
      
  15. 2. MYSQL_ASSOC for $result_type
      
  16. 
      
  17. while ($row = mysql_fetch_array ($result, MYSQL_ASSOC))
      
  18. {
      
  19.         $data = $row["CityName"];
      
  20.         echo "$data\n";
      
  21. }
      
  22. 
      
  23. 3. MYSQL_NUM for $result_type
      
  24. 
      
  25. while ($row = mysql_fetch_array ($result, MYSQL_NUM))
      
  26. {
      
  27.                $data = $row[0];
      
  28.         echo "$data\n";
      
  29. }
      
  30. 
      

  复制代码
  
  
• Using mysql_fetch_object()
  
  

  object mysql_fetch_object (resource $result [, string $class_name [, array $params]])

  
  
  The method mysql_fetch_object() returns an object with string properties that correspond to the fetched row, or FALSE if there are no more rows. The mandatory parameter $result is the result set that is returned by mysql_query(). The optional parameter $class_name is the name of the class to instantiate. If none is specified, a stdClass object is returned. The final parameter $params is an optional array of parameters to pass to the constructor for $class_name objects.
  The method mysql_fetch_object() is similar to mysql_fetch_array(), with one difference: an object is returned rather than an array. That is, you can access the data only by the field names but not by their offsets.
  
  Field names returned by this function are case-sensitive.
  
  For example:
  
  1. while ($row = mysql_fetch_object($result)) {
     
  2.         echo $row->CityName;
     
  3.         echo "\n";
     
  4. }

  复制代码
  
  
  

The method mysql_num_rows() returns the row count for a SELECT statement, whereas mysql_affected_rows() returns the number of rows affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

Clean Up: Release the Resources


Once the required data has been retrieved from the result set, it is a recommended practice to free up the connection to the database server so that further queries can be executed. Explicitly call the mysql_free_result() method to accomplish this task.

bool mysql_free_result (resource $result)

The method mysql_free_result() requires one argument: $result, the result set that is returned by mysql_query(), and it returns TRUE on success or FALSE on failure.

The following statement in the sample script frees up the memory used by the result set.

mysql_free_result($result);

If mysql_free_result() isn't called explicitly, all the associated result memory is automatically freed at the end of the script's execution.

Finally, close the MySQL connection by calling the mysql_close() method.

bool mysql_close ([resource $db_conn_handle])

The method mysql_close() closes the non-persistent connection to the MySQL server that is associated with the specified database connection handle. If the connection handle is not specified, by default, the last connection opened by mysql_connect() is assumed. If no such connection is found, it tries to create one as if mysql_connect() was called with no arguments.

The following statement in the sample PHP script closes the database connection.

mysql_close($cxn);

Check the MySQL Functions documentation at PHP.net if you are looking for something beyond what's been explained in this tutorial.

Explore Other Tutorials in This Series



To find out more about using MySQL with PHP, see all tutorials in the series:

• Developing MySQL Database Applications With PHP Part 1: Using the MySQL Improved Extension, mysqli -- Describes mysqli, the MySQL Improved Extension that is the successor to the mysql extension. This extension gives you access to all MySQL server features provided by MySQL 4.1 and above; for example, support for Prepared Statements, Character Sets, and Stored Procedures, which are not available with mysql.
  
• Developing MySQL Database Applications With PHP Part 3: Using the PDO Extension With MySQL Driver, pdo_mysql -- Describes the pdo_mysql driver, which implements the PHP Data Objects (PDO) interface to access the MySQL Server from PHP. Due to its portable interfaces, PDO is useful when you need to build applications capable of talking to different database systems; however, PDO does not allow you to use all of the features that are available in the latest versions of MySQL.
  
• Developing MySQL Database Applications With PHP Part 4: Using the MySQL Native Driver for PHP, mysqlnd -- Describes the MySQL native driver for PHP, mysqlnd, which is an alternative to the MySQL client library, libmysql, for connecting from PHP 5 and PHP 6 to MySQL Server 4.1 or later. The mysqlnd driver is tightly integrated into PHP starting with the release of PHP 5.3, thus mysqlnd eliminates the dependency on MySQL client programming support when the database extensions and the database driver are built with support for mysqlnd.
  

For More Information

• It is strongly recommended to read the extensive documentation that is available at php.net and dev.mysql.com before choosing an appropriate extension for your application.
• Also check the comparison of features among those three extensions at php.net.
• PHP Manual
• PHP Database Extensions
• MySQL 5.1 Reference Manual
• Beginning PHP and MySQL: From Novice to Professional, W. Jason Gilmore
• Ulf Wendel's Blog: Internet Super Hero