The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above. More information about the MySQL Database server can be found at http://www.mysql.com/
Documentation for MySQL can be found at http://dev.mysql.com/doc/.
Parts of this documentation included from MySQL manual with permissions of MySQL AB.
In order to have these functions available, you must compile PHP with support for the mysqli extension.
Note: The mysqli extension is designed to work with the version 4.1.3 or above of MySQL. For previous versions, please see the MySQL extension documentation.
To install the mysqli extension for PHP, use the --with-mysqli=mysql_config_path/mysql_config configuration option where mysql_config_path represents the location of the mysql_config program that comes with MySQL versions greater than 4.1.
If you would like to install the mysql extension along with the mysqli extension you have to use the same client library to avoid any conflicts.
The behaviour of these functions is affected by settings in php.ini.
Table 1. MySQLi Configuration Options
| Name | Default | Changeable | Changelog |
|---|---|---|---|
| mysqli.max_links | "-1" | PHP_INI_SYSTEM | Available since PHP 5.0.0. |
| mysqli.default_port | "3306" | PHP_INI_ALL | Available since PHP 5.0.0. |
| mysqli.default_socket | NULL | PHP_INI_ALL | Available since PHP 5.0.0. |
| mysqli.default_host | NULL | PHP_INI_ALL | Available since PHP 5.0.0. |
| mysqli.default_user | NULL | PHP_INI_ALL | Available since PHP 5.0.0. |
| mysqli.default_pw | NULL | PHP_INI_ALL | Available since PHP 5.0.0. |
For further details and definitions of the above PHP_INI_* constants, see the chapter on configuration changes.
Here's a short explanation of the configuration directives.
The maximum number of MySQL connections per process.
The default TCP port number to use when connecting to the database server if no other port is specified. If no default is specified, the port will be obtained from the MYSQL_TCP_PORT environment variable, the mysql-tcp entry in /etc/services or the compile-time MYSQL_PORT constant, in that order. Win32 will only use the MYSQL_PORT constant.
The default socket name to use when connecting to a local database server if no other socket name is specified.
The default server host to use when connecting to the database server if no other host is specified. Doesn't apply in safe mode.
The default user name to use when connecting to the database server if no other name is specified. Doesn't apply in safe mode.
The default password to use when connecting to the database server if no other password is specified. Doesn't apply in safe mode.
Represents a connection between PHP and a MySQL database.
mysqli - construct a new mysqli object
autocommit - turns on or off auto-commiting database modifications
change_user - changes the user of the specified database connection
character_set_name - returns the default character set for the database connection
close - closes a previously opened connection
commit - commits the current transaction
connect - opens a new connection to MySQL database server
debug - performs debugging operations
dump_debug_info - dumps debug information
get_client_info - returns client version
get_host_info - returns type of connection used
get_server_info - returns version of the MySQL server
get_server_version - returns version of the MySQL server
init - initializes mysqli object
info - retrieves information about the most recently executed query
kill - asks the server to kill a mysql thread
multi_query - performs multiple queries
more_results - check if more results exist from currently executed multi-query
next_result - reads next result from currently executed multi-query
options - set options
ping - pings a server connection or reconnects if there is no connection
prepare - prepares a SQL query
query - performs a query
real_connect - attempts to open a connection to MySQL database server
escape_string - escapes special characters in a string for use in a SQL statement, taking into account the current charset of the connection
rollback - rolls back the current transaction
select_db - selects the default database
set_charset - sets the default client character set
ssl_set - sets ssl parameters
stat - gets the current system status
stmt_init- initializes a statement for use with mysqli_stmt_prepare
store_result - transfers a resultset from last query
thread_safe - returns whether thread safety is given or not
use_result - transfers an unbuffered resultset from last query
affected_rows - gets the number of affected rows in a previous MySQL operation
client_info - returns the MySQL client version as a string
client_version - returns the MySQL client version as an integer
errno - returns the error code for the most recent function call
error - returns the error string for the most recent function call
field_count - returns the number of columns for the most recent query
host_info - returns a string representing the type of connection used
info - retrieves information about the most recently executed query
insert_id - returns the auto generated id used in the last query
protocol_version - returns the version of the MySQL protocol used
server_info - returns a string that represents the server version number
server_version - returns the version number of the server as an integer
sqlstate - returns a string containing the SQLSTATE error code for the last error
thread_id - returns the thread ID for the current connection
warning_count - returns the number of warnings generated during execution of the previous SQL statement
Represents a prepared statement.
bind_param - binds variables to a prepared statement
bind_result - binds variables to a prepared statement for result storage
close - closes a prepared statement
data_seek - seeks to an arbitrary row in a statement result set
execute - executes a prepared statement
fetch - fetches result from a prepared statement into bound variables
free_result - frees stored result memory for the given statement handle
prepare - prepares a SQL query
reset - resets a prepared statement
result_metadata - retrieves a resultset from a prepared statement for metadata information
send_long_data - sends data in chunks
store_result - buffers complete resultset from a prepared statement
affected_rows - returns affected rows from last statement execution
errno - returns errorcode for last statement function
error - returns errormessage for last statement function
field_count - returns the number of columns in a result set
id - returns the statement identifier
insert_id - returns the value generated for an AUTO_INCREMENT column by the prepared statement
num_rows - returns the number of rows in the result set
param_count - returns number of parameter for a given prepare statement
sqlstate - returns a string containing the SQLSTATE error code for the last statement function
Represents the result set obtained from a query against the database.
close - closes resultset
data_seek - moves internal result pointer
fetch_array - fetches a result row as an associative array, a numeric array, or both.
fetch_assoc - fetches a result row as an associative array
fetch_field - gets column information from a resultset
fetch_fields - gets information for all columns from a resulset
fetch_field_direct - gets column information for specified column
fetch_object - fetches a result row as an object
fetch_row - gets a result row as an enumerated array
field_seek - set result pointer to a specified field offset
free_result - frees result memory
current_field - returns offset of current fieldpointer
field_count - returns number of fields in resultset
lengths - returns an array of columnlengths
num_rows - returns number of rows in resultset
type - returns MYSQLI_STORE_RESULT or MYSQLI_USE_RESULT
Table 2. MySQLi Constants
| Name | Description |
|---|---|
| MYSQLI_READ_DEFAULT_GROUP (integer) | Read options from the named group from `my.cnf' or the file specified with MYSQLI_READ_DEFAULT_FILE |
| MYSQLI_READ_DEFAULT_FILE (integer) | Read options from the named option file instead of from my.cnf |
| MYSQLI_OPT_CONNECT_TIMEOUT (integer) | Connect timeout in seconds |
| MYSQLI_OPT_LOCAL_INFILE (integer) | Enables command LOAD LOCAL INFILE |
| MYSQLI_INIT_COMMAND (integer) | Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting. |
| MYSQLI_CLIENT_SSL (integer) | Use SSL (encrypted protocol). This option should not be set by application programs; it is set internally in the MySQL client library |
| MYSQLI_CLIENT_COMPRESS (integer) | Use compression protocol |
| MYSQLI_CLIENT_INTERACTIVE (integer) | Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection. The client's session wait_timeout variable will be set to the value of the session interactive_timeout variable. |
| MYSQLI_CLIENT_IGNORE_SPACE (integer) | Allow spaces after function names. Makes all functions names reserved words. |
| MYSQLI_CLIENT_NO_SCHEMA (integer) | Don't allow the db_name.tbl_name.col_name syntax. |
| MYSQLI_CLIENT_MULTI_QUERIES (integer) | |
| MYSQLI_STORE_RESULT (integer) | For using buffered resultsets |
| MYSQLI_USE_RESULT (integer) | For using unbuffered resultsets |
| MYSQLI_ASSOC (integer) | Columns are returned into the array having the fieldname as the array index. |
| MYSQLI_NUM (integer) | Columns are returned into the array having an enumerated index. |
| MYSQLI_BOTH (integer) | Columns are returned into the array having both a numerical index and the fieldname as the associative index. |
| MYSQLI_NOT_NULL_FLAG (integer) | Indicates that a field is defined as NOT NULL |
| MYSQLI_PRI_KEY_FLAG (integer) | Field is part of a primary index |
| MYSQLI_UNIQUE_KEY_FLAG (integer) | Field is part of a unique index. |
| MYSQLI_MULTIPLE_KEY_FLAG (integer) | Field is part of an index. |
| MYSQLI_BLOB_FLAG (integer) | Field is defined as BLOB |
| MYSQLI_UNSIGNED_FLAG (integer) | Field is defined as UNSIGNED |
| MYSQLI_ZEROFILL_FLAG (integer) | Field is defined as ZEROFILL |
| MYSQLI_AUTO_INCREMENT_FLAG (integer) | Field is defined as AUTO_INCREMENT |
| MYSQLI_TIMESTAMP_FLAG (integer) | Field is defined as TIMESTAMP |
| MYSQLI_SET_FLAG (integer) | Field is defined as SET |
| MYSQLI_NUM_FLAG (integer) | Field is defined as NUMERIC |
| MYSQLI_PART_KEY_FLAG (integer) | Field is part of an multi-index |
| MYSQLI_GROUP_FLAG (integer) | Field is part of GROUP BY |
| MYSQLI_TYPE_DECIMAL (integer) | Field is defined as DECIMAL |
| MYSQLI_TYPE_NEWDECIMAL (integer) | Precision math DECIMAL or NUMERIC field (MySQL 5.0.3 and up) |
| MYSQLI_TYPE_BIT (integer) | Field is defined as BIT (MySQL 5.0.3 and up) |
| MYSQLI_TYPE_TINY (integer) | Field is defined as TINYINT |
| MYSQLI_TYPE_SHORT (integer) | Field is defined as INT |
| MYSQLI_TYPE_LONG (integer) | Field is defined as INT |
| MYSQLI_TYPE_FLOAT (integer) | Field is defined as FLOAT |
| MYSQLI_TYPE_DOUBLE (integer) | Field is defined as DOUBLE |
| MYSQLI_TYPE_NULL (integer) | Field is defined as DEFAULT NULL |
| MYSQLI_TYPE_TIMESTAMP (integer) | Field is defined as TIMESTAMP |
| MYSQLI_TYPE_LONGLONG (integer) | Field is defined as BIGINT |
| MYSQLI_TYPE_INT24 (integer) | Field is defined as MEDIUMINT |
| MYSQLI_TYPE_DATE (integer) | Field is defined as DATE |
| MYSQLI_TYPE_TIME (integer) | Field is defined as TIME |
| MYSQLI_TYPE_DATETIME (integer) | Field is defined as DATETIME |
| MYSQLI_TYPE_YEAR (integer) | Field is defined as YEAR |
| MYSQLI_TYPE_NEWDATE (integer) | Field is defined as DATE |
| MYSQLI_TYPE_ENUM (integer) | Field is defined as ENUM |
| MYSQLI_TYPE_SET (integer) | Field is defined as SET |
| MYSQLI_TYPE_TINY_BLOB (integer) | Field is defined as TINYBLOB |
| MYSQLI_TYPE_MEDIUM_BLOB (integer) | Field is defined as MEDIUMBLOB |
| MYSQLI_TYPE_LONG_BLOB (integer) | Field is defined as LONGBLOB |
| MYSQLI_TYPE_BLOB (integer) | Field is defined as BLOB |
| MYSQLI_TYPE_VAR_STRING (integer) | Field is defined as VARCHAR |
| MYSQLI_TYPE_STRING (integer) | Field is defined as CHAR |
| MYSQLI_TYPE_GEOMETRY (integer) | Field is defined as GEOMETRY |
| MYSQLI_NEED_DATA (integer) | More data available for bind variable |
| MYSQLI_NO_DATA (integer) | No more data available for bind variable |
| MYSQLI_DATA_TRUNCATED (integer) | Data truncation occurred. Available since PHP 5.1.0 and MySQL 5.0.5. |
All Examples in the MySQLI documentation use the world database from MySQL AB. The world database can be found at http://dev.mysql.com/get/Downloads/Manual/world.sql.gz/from/pick