CHAPTER 6 – Mysql & PHP Interface

The mysqli PHP extension was written from the ground up to support the new features of the MySQL 4.1 and 5.0 Client API. The improvements from the old mysql extension include the following:

Native bind/prepare/execute functionality Cursor support SQLSTATE error codes Multiple statements from one query Index analyzer The following sections give an overview of how to use the mysqli extension, and how it differs from the old mysql extension. Almost every mysqli function has a method or property counterpart, and the following list of functions describes both of them. The notation for the methods is similar to $mysqli->connect() for regular methods, calling connect() in an instance of the mysqli class. The parameter list is usually the same between mysqli functions and methods, except that functions in most cases have an object parameter first. Following that, function parameter lists are identical to that of their method counterparts. For the sake of brevity, ... replaces the method parameter list in the parameter descriptions. 6.2.3 Example Data This section uses data from the "world" example database, available at http:// dev.mysql.com/get/Downloads/Manual/world.sql.gz/from/pick. 6.2.4 Connections Table 6.1 shows the mysqli functions that are related to connections.

Table 6.1 mysqli Connection Functions and Methods Function Name Description mysqli_connect(...) Opens a connection to the MySQL server. Para- $mysqli = new mysqli(...) meters (all are optional) · host name (string) · user name (string) · password (string) · database name (string) · TCP port (integer) · UNIX domain socket (string) mysqli_init() Initializes MySQLi and returns an object for use $mysqli = new mysqli with mysqli_real_connect mysqli_options(...) Set various connection options $mysqli->options(...) mysqli_real_connect(...) Opens a connection to the MySQL server $mysqli->real_connect(...) mysqli_close(...) Closes a MySQL server connection $mysqli->close() The parameter is connection object (function only) mysqli_connect_errno() Obtains the error code of the last failed connect mysqli_connect_error() Obtains the error message of the last failed connect mysqli_get_host_info(...) Returns a string telling what the connection is $mysqli->host_info connected to Here is a simple example: <?php $conn = mysqli_connect("localhost", "test", "", "world"); if (empty($conn)) { die("mysqli_connect failed: " . mysqli_connect_error()); } print "connected to " . mysqli_get_host_info($conn) . "n"; mysqli_close($conn); Here, the mysqli_connect() function connects to "localhost" with the user name "test", an empty password, and selects the "world" database as the default database. If the connect fails, mysqli_connect() returns FALSE, and mysqli_connect_error() returns a message saying why it could not connect. When using the object-oriented interface, you can also specify your con- nection parameters by passing them to the constructor of the mysqli object:

<?php $mysqli = new mysqli("localhost", "test", "", "world"); if (mysqli_connect_errno) { die("mysqli_connect failed: " . mysqli_connect_error()); } print "connected to " . $mysqli->host_info . "n"; $mysqli->close(); Sometimes, you might need some more options when connecting to a MySQL server. In this case, you can use the mysqli_init, mysqli_options, and mysqli_real_connect functions, which allow you to set different options for your database connection. The following example demonstrates how you can use these functions: <?php $mysqli = mysqli_init(); $mysqli->options(MYSQLI_INIT_CMD, "SET AUTOCOMMIT=0"); $mysqli->options(MYSQLI_READ_DEFAULT_FILE, "SSL_CLIENT"); $mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5); $mysqli->real_connect("localhost", "test", "", "world"); if (mysqli_connect_errno) { die("mysqli_connect failed: " . mysqli_connect_error()); } print "connected to " . $mysqli->host_info . "n"; $mysqli->close(); The mysqli_options functions allow you to set the options shown in Table 6.2. Table 6.2 mysqli_options Constants Option Description MYSQLI_OPT_CONNECT_TIMEOUT Specifies the connection timeout in seconds MYSQLI_OPT_LOCAL_INFILE Enables or disables the use of the LOAD LOCAL MYSQLI_INIT_CMD INFILE command Specifies the command that must be executed MYSQLI_READ_DEFAULT_FILE after connect MYSQLI_READ_DEFAULT_GROUP Specifies the name of the file that contains named options Reads options from the named group from my.cnf (or the file specified with MYSQLI_READ_ DEFAULT_FILE)