3.3 Before and After: Connecting to the Database Server

Before you can issue queries, you need to connect to the MySQL server. This sounds so basic that you'd think it would be the same in both versions of the MySQL extension, but there are quite a few changes.

Connecting isn't just specifying the location of your database and providing a username and password. You also specify a variety of configuration options, such as whether to use SSL and the number of seconds before the connection times out.

3.3.1 mysql: Making a Connection

The mysql connection functions take five parameters:

mysql_connect(server, username, password, new_link, client_flags)

All of these parameters are actually optional, because the extension defaults to values specified in a series of MySQL-related configuration directives, such as mysql.default_host.

The server parameter is usually the same as the host, but you can also append a port name or a path to a socket. For example, if your database runs on port 3307 on db.example.org:


Separate the hostname and port with a colon (:) so PHP can tell them apart.

The username and password variables are not the username and password for your local account, but for MySQL's account system.

By default, if you try to reconnect to the same database with the same set of credentials, PHP will reuse the existing connection. Setting new_link to true forces PHP to always make another link to MySQL.

Use the final parameter, client_flags, to control the session. You can modify it by passing any combination of the following three constants: MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE, and MYSQL_CLIENT_INTERACTIVE. These tell MySQL to compress the connection, ignore spaces after functions, and modify how it determines when to close the connection, respectively.

3.3.2 mysqli: Making a Connection

Under mysqli there are two different ways to connect to MySQL: the familiar mysqli_connect( ) and a new method, mysqli_real_connect( ).

Don't feel that mysqli_real_connect( ) is somehow superior to mysqli_connect( ) because it contains the word real. It's not. This name comes simply from the underlying MySQL C API. Besides, mysqli_connect( ) uses the same C functions as mysqli_real_connect( ); it just wraps them in an easy-to-use form.

Unfortunately, while mysqli_connect( ) looks similar to mysql_connect( ), it is not identical. For one, it now takes six parameters, all of them still optional:

mysqli_connect(hostname, username, password, database, port, socket)

The first three options?hostname, username, and password?are the same as mysql_connect( ), but the remaining three are different. The database parameter controls which database you want to query. Using this parameter is the same as calling mysql_select_database( ). This function is still available as mysqli_select_database( ), but you should not need to use it unless you are reusing the same connection to switch from one database to another on the same server.

The port option controls which port to contact on the database server, and socket specifies which socket to use. This is identical to adding :port or :/path/to/socket after the hostname in mysql_connect( ). (You cannot use this syntax in mysqli.) These two parameters are mutually exclusive. If you use one, there's no purpose in using the other.

Here's an example that uses the first five options:

mysqli_connect('db.example.org', 'web', 'xyz@123', 'users', 3306);

This function tries to connect to a MySQL server running on db.example.com on port 3306. It uses a MySQL username of web with password xyz@123. If it's authorized, it then sets the database to events, just like entering USE events; in the MySQL prompt.

The mysql_pconnect( ) function has been removed. You cannot create persistent connections using MySQLi. While the goal of persistent connections?reducing the overhead time necessary to establish a connection?was a good idea, they caused problems. On high traffic sites they actually prevented PHP from accessing MySQL because they were holding onto available connections.

The mysql extension had an optional client_ flags parameter, which let you set options such as whether the connection should compress data. These options now require a different set of connection functions:

$db = mysqli_init( );

mysqli_real_connect($db, 'db.example.com', 'web', 'xyz@123', 'users', 

                   3306, NULL, MYSQLI_CLIENT_COMPRESS);

The mysqli_init( ) function returns a MySQL object that can then be used by mysqli_real_connect( ) to make the connection.

The mysqli_real_connect( ) function has the same syntax as mysqli_connect( ), except that you can add client flags as a seventh parameter. The call just shown is identical to the example using mysqli_connect( ), except that it also compresses the connection.

Table 3-1 shows the five constants you can pass to mysqli_real_connect( ). It includes the three flags available with mysql, but also adds two new ones: MYSQLI_CLIENT_FOUND_ROWS and MYSQLI_CLIENT_SSL. The pre-existing constants have also been renamed to begin with MYSQLI.

Table 3-1. mysqli_real_connect( ) options




Compress the connection.


Return the number of found rows instead of the number of changed rows.


Allow spaces after function names.


Close the connection after interactive_timeout seconds instead of wait_timeout seconds. These values are usually the same unless you've modified your MySQL configuration.


Encrypt the connection using SSL (should be automatically invoked when you add SSL information).

For more information on these options, see http://www.mysql.com/doc/en/mysql_options.html.

Using mysqli_init( ) also allows you to set a variety of other configuration options before establishing the link:

$db = mysqli_init( );

mysqli_options($db, MYSQLI_OPT_CONNECT_TIMEOUT, 120);

mysqli_options($db, MYSQLI_OPT_LOCAL_INFILE, false);

mysqli_real_connect($db, 'db.example.com', 'web', 'xyz@123', 'users', 

                   3306, NULL, MYSQLI_CLIENT_COMPRESS);

The mysqli_options( ) function provides you with the ability to control more esoteric details about the connection. Since these options usually allow you to specify a parameter, they can't be passed alongside the other client flags.

This example sets the connection timeout to 120 seconds and disables the ability to use LOAD LOCAL INFILE. See Table 3-2 for a list of all the connection options usable in mysqli_options( ) and mysqli_real_connect( ).

Table 3-2. mysqli_options( ) options




Read the options for this group from your MySQL configuration file.


Use this file as your MySQL configuration file instead of my.cnf.


Number of seconds before MySQL times out.


Enable the LOAD LOCAL INFILE command.


Always issue this command when connecting (and reconnecting) to the server.

Additional details on these options are available at http://www.mysql.com/doc/en/mysql_options.html.