PHP - MySQLi

The MySQLi extension allows you to access MySQL database servers. It is designed to work with MySQL version 4.1 and above.

Installation & Runtime Configuration

The MySQLi extension was introduced with PHP version 5.0.0. The MySQL Native Driver was included in PHP version 5.3.0.

For installation details, go to: https://php.net/manual/en/mysqli.installation.php

For runtime configuration details, go to: https://php.net/manual/en/mysqli.configuration.php

PHP mysqli Class

Class Description
mysqli	The mysqli class.
Methods/Properties	Description
__construct()	Open a new connection to the MySQL server.
$affected_rows	Gets the number of affected rows in a previous MySQL operation.
autocommit()	Turns on or off auto-committing database modifications.
begin_transaction()	Starts a transaction.
change_user()	Changes the user of the specified database connection.
character_set_name()	Returns the current character set of the database connection.
close()	Closes a previously opened database connection.
$client_info	Get MySQL client info.
$client_version	Returns the MySQL client version as an integer.
commit()	Commits the current transaction.
$connect_errno	Returns the error code from last connect call.
$connect_error	Returns a description of the last connection error.
debug()	Performs debugging operations.
dump_debug_info()	Dump debugging information into the log.
$errno	Returns the error code for the most recent function call.
$error_list	Returns a list of errors from the last command executed.
$error	Returns a string description of the last error.
$field_count	Returns the number of columns for the most recent query.
get_charset()	Returns a character set object.
get_connection_stats()	Returns statistics about the client connection.
get_warnings()	Get result of SHOW WARNINGS.
$host_info	Returns a string representing the type of connection used.
$info	Retrieves information about the most recently executed query.
init()	Initializes MySQLi and returns an object for use with mysqli_real_connect().
$insert_id	Returns the value generated for an AUTO_INCREMENT column by the last query.
kill()	Asks the server to kill a MySQL thread.
options()	Set options.
ping()	Pings a server connection, or tries to reconnect if the connection has gone down.
poll()	Poll connections.
$protocol_version	Returns the version of the MySQL protocol used.
more_results()	Check if there are any more query results from a multi query.
multi_query()	Performs one or more queries on the database.
next_result()	Prepare next result from multi_query.
prepare()	Prepares an SQL statement for execution.
query()	Performs a query on the database.
real_connect()	Opens a connection to a mysql server.
real_escape_string()	Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection.
real_query()	Execute an SQL query.
reap_async_query()	Get result from async query.
refresh()	Refreshes.
release_savepoint()	Removes the named savepoint from the set of savepoints of the current transaction.
rollback()	Rolls back current transaction.
savepoint()	Set a named transaction savepoint.
select_db()	Selects the default database for database queries.
$server_info	Returns the version of the MySQL server.
$server_version	Returns the version of the MySQL server as an integer.
set_charset()	Sets the client character set.
$sqlstate	Returns the SQLSTATE error from previous MySQL operation.
ssl_set()	Used for establishing secure connections using SSL.
stat()	Gets the current system status.
stmt_init()	Initializes a statement and returns an object for use with mysqli_stmt_prepare().
store_result()	Transfers a result set from the last query.
$thread_id	Returns the thread ID for the current connection.
thread_safe()	Returns whether thread safety is given or not.
use_result()	Initiate a result set retrieval.
$warning_count	Returns the number of warnings from the last query for the given link.

PHP mysqli_stmt Class

Class Description
mysqli_stmt	The mysqli_stmt class.
Methods/Properties	Description
__construct()	Constructs a new mysqli_stmt object.
$affected_rows	Returns the total number of rows changed, deleted, inserted, or matched by the last statement executed.
attr_get()	Used to get the current value of a statement attribute.
attr_set()	Used to modify the behavior of a prepared statement.
bind_param()	Binds variables to a prepared statement as parameters.
bind_result()	Binds variables to a prepared statement for result storage.
close()	Closes a prepared statement.
data_seek()	Seeks to an arbitrary row in statement result set.
$errno	Returns the error code for the most recent statement call.
$error_list	Returns a list of errors from the last statement executed.
$error	Returns a string description of the last statement error.
execute()	Executes a prepared statement.
fetch()	Fetch results from a prepared statement into the bound variables.
$field_count	Returns the number of columns in the given statement.
free_result()	Frees stored result memory for the given statement handle.
get_result()	Gets a result set from a prepared statement as a mysqli_result object.
get_warnings()	Get result of SHOW WARNINGS.
$insert_id	Get the ID generated from the previous INSERT operation.
more_results()	Check if there are more query results from a multiple query.
next_result()	Reads the next result from a multiple query.
$num_rows	Returns the number of rows fetched from the server.
$param_count	Returns the number of parameters for the given statement.
prepare()	Prepares an SQL statement for execution.
reset()	Resets a prepared statement.
result_metadata()	Returns result set metadata from a prepared statement.
send_long_data()	Send data in blocks.
$sqlstate	Returns SQLSTATE error from previous statement operation.
store_result()	Stores a result set in an internal buffer.

PHP mysqli_result Class

Class Description
mysqli_result	The mysqli_result class.
Methods/Properties	Description
$current_field	Get current field offset of a result pointer.
data_seek()	Adjusts the result pointer to an arbitrary row in the result.
fetch_all()	Fetch all result rows as an associative array, a numeric array, or both.
fetch_array()	Fetch the next row of a result set as an associative, a numeric array, or both.
fetch_assoc()	Fetch the next row of a result set as an associative array.
fetch_column()	Fetch a single column from the next row of a result set.
fetch_field()	Returns the next field in the result set.
fetch_field_direct()	Fetch meta-data for a single field.
fetch_fields()	Returns an array of objects representing the fields in a result set.
fetch_object()	Fetch the next row of a result set as an object.
fetch_row()	Fetch the next row of a result set as an enumerated array.
$field_count	Gets the number of fields in the result set.
field_seek()	Set result pointer to a specified field offset.
free()	Frees the memory associated with a result.
$lengths	Returns the lengths of the columns of the current row in the result set.
$num_rows	Gets the number of rows in the result set.

PHP mysqli_driver Class

Class Description
mysqli_driver	The mysqli_driver class.
Methods/Properties	Description
embedded_server_end()	Stop embedded server.
embedded_server_start()	Initialize and start embedded server.
$report_mode	Sets mysqli error reporting mode.

PHP mysqli_warning Class

Class Description
mysqli_warning	The mysqli_warning class.
Methods	Description
next()	Fetch next warning.

PHP mysqli_sql_exception Class

Class Description
mysqli_sql_exception	The mysqli_sql_exception class.

Aliases and deprecated MySQLi Functions

Functions	Description
mysqli_connect()	Open a new connection to the MySQL server. Alias of mysqli::__construct().
mysqli::escape_string()	Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection. Alias of mysqli_real_escape_string().
mysqli_get_links_stats()	Return information about open and cached links.
mysqli_execute()	Executes a prepared statement. Alias of mysqli_stmt_execute().
mysqli_get_client_stats()	Returns client per-process statistics.
mysqli_report()	Sets mysqli error reporting mode. Alias of mysqli_driver->report_mode.
mysqli::set_opt()	Set options. Alias of mysqli_options().

PHP MySQLi Predefined Constants

The constants below are defined by this extension, and will only be available when the extension has either been compiled into PHP or dynamically loaded at runtime.

Constants	Description
MYSQLI_READ_DEFAULT_GROUP	Read options from the named group from my.cnf or the file specified with MYSQLI_READ_DEFAULT_FILE
MYSQLI_READ_DEFAULT_FILE	Read options from the named option file instead of from my.cnf
MYSQLI_OPT_CONNECT_TIMEOUT	Connect timeout in seconds
MYSQLI_OPT_READ_TIMEOUT	Command execution result timeout in seconds. Available as of PHP 7.2.0.
MYSQLI_OPT_LOCAL_INFILE	Enables command LOAD LOCAL INFILE
MYSQLI_OPT_INT_AND_FLOAT_NATIVE	Convert integer and float columns back to PHP numbers. Only valid for mysqlnd.
MYSQLI_OPT_NET_CMD_BUFFER_SIZE	The size of the internal command/network buffer. Only valid for mysqlnd.
MYSQLI_OPT_NET_READ_BUFFER_SIZE	Maximum read chunk size in bytes when reading the body of a MySQL command packet. Only valid for mysqlnd.
MYSQLI_OPT_SSL_VERIFY_SERVER_CERT	Requires MySQL 5.1.10 and up
MYSQLI_INIT_COMMAND	Command to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting.
MYSQLI_CLIENT_SSL	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	Use compression protocol
MYSQLI_CLIENT_INTERACTIVE	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	Allow spaces after function names. Makes all functions names reserved words.
MYSQLI_CLIENT_NO_SCHEMA	Don't allow the db_name.tbl_name.col_name syntax.
MYSQLI_CLIENT_MULTI_QUERIES	Allows multiple semicolon-delimited queries in a single mysqli_query() call.
MYSQLI_STORE_RESULT	For using buffered resultsets
MYSQLI_USE_RESULT	For using unbuffered resultsets
MYSQLI_ASSOC	Columns are returned into the array having the fieldname as the array index.
MYSQLI_NUM	Columns are returned into the array having an enumerated index.
MYSQLI_BOTH	Columns are returned into the array having both a numerical index and the fieldname as the associative index.
MYSQLI_NOT_NULL_FLAG	Indicates that a field is defined as NOT NULL
MYSQLI_PRI_KEY_FLAG	Field is part of a primary index
MYSQLI_UNIQUE_KEY_FLAG	Field is part of a unique index.
MYSQLI_MULTIPLE_KEY_FLAG	Field is part of an index.
MYSQLI_BLOB_FLAG	Field is defined as BLOB
MYSQLI_UNSIGNED_FLAG	Field is defined as UNSIGNED
MYSQLI_ZEROFILL_FLAG	Field is defined as ZEROFILL
MYSQLI_AUTO_INCREMENT_FLAG	Field is defined as AUTO_INCREMENT
MYSQLI_TIMESTAMP_FLAG	Field is defined as TIMESTAMP
MYSQLI_SET_FLAG	Field is defined as SET
MYSQLI_NUM_FLAG	Field is defined as NUMERIC
MYSQLI_PART_KEY_FLAG	Field is part of an multi-index
MYSQLI_GROUP_FLAG	Field is part of GROUP BY
MYSQLI_TYPE_DECIMAL	Field is defined as DECIMAL
MYSQLI_TYPE_NEWDECIMAL	Precision math DECIMAL or NUMERIC field (MySQL 5.0.3 and up)
MYSQLI_TYPE_BIT	Field is defined as BIT (MySQL 5.0.3 and up)
MYSQLI_TYPE_TINY	Field is defined as TINYINT
MYSQLI_TYPE_SHORT	Field is defined as SMALLINT
MYSQLI_TYPE_LONG	Field is defined as INT
MYSQLI_TYPE_FLOAT	Field is defined as FLOAT
MYSQLI_TYPE_DOUBLE	Field is defined as DOUBLE
MYSQLI_TYPE_NULL	Field is defined as DEFAULT NULL
MYSQLI_TYPE_TIMESTAMP	Field is defined as TIMESTAMP
MYSQLI_TYPE_LONGLONG	Field is defined as BIGINT
MYSQLI_TYPE_INT24	Field is defined as MEDIUMINT
MYSQLI_TYPE_DATE	Field is defined as DATE
MYSQLI_TYPE_TIME	Field is defined as TIME
MYSQLI_TYPE_DATETIME	Field is defined as DATETIME
MYSQLI_TYPE_YEAR	Field is defined as YEAR
MYSQLI_TYPE_NEWDATE	Field is defined as DATE
MYSQLI_TYPE_INTERVAL	Field is defined as INTERVAL
MYSQLI_TYPE_ENUM	Field is defined as ENUM
MYSQLI_TYPE_SET	Field is defined as SET
MYSQLI_TYPE_TINY_BLOB	Field is defined as TINYBLOB
MYSQLI_TYPE_MEDIUM_BLOB	Field is defined as MEDIUMBLOB
MYSQLI_TYPE_LONG_BLOB	Field is defined as LONGBLOB
MYSQLI_TYPE_BLOB	Field is defined as BLOB
MYSQLI_TYPE_VAR_STRING	Field is defined as VARCHAR
MYSQLI_TYPE_STRING	Field is defined as CHAR or BINARY
MYSQLI_TYPE_CHAR	Field is defined as TINYINT. For CHAR, see MYSQLI_TYPE_STRING
MYSQLI_TYPE_GEOMETRY	Field is defined as GEOMETRY
MYSQLI_TYPE_JSON	Field is defined as JSON. Only valid for mysqlnd and MySQL 5.7.8 and up.
MYSQLI_NEED_DATA	More data available for bind variable
MYSQLI_NO_DATA	No more data available for bind variable
MYSQLI_DATA_TRUNCATED	Data truncation occurred. Available since MySQL 5.0.5.
MYSQLI_ENUM_FLAG	Field is defined as ENUM.
MYSQLI_BINARY_FLAG	Field is defined as BINARY.
MYSQLI_CURSOR_TYPE_FOR_UPDATE
MYSQLI_CURSOR_TYPE_NO_CURSOR
MYSQLI_CURSOR_TYPE_READ_ONLY
MYSQLI_CURSOR_TYPE_SCROLLABLE
MYSQLI_STMT_ATTR_CURSOR_TYPE
MYSQLI_STMT_ATTR_PREFETCH_ROWS
MYSQLI_STMT_ATTR_UPDATE_MAX_LENGTH
MYSQLI_SET_CHARSET_NAME
MYSQLI_REPORT_INDEX	Report if no index or bad index was used in a query.
MYSQLI_REPORT_ERROR	Report errors from mysqli function calls.
MYSQLI_REPORT_STRICT	Throw a mysqli_sql_exception for errors instead of warnings.
MYSQLI_REPORT_ALL	Set all options on (report all).
MYSQLI_REPORT_OFF	Turns reporting off.
MYSQLI_DEBUG_TRACE_ENABLED	Is set to 1 if mysqli_debug() functionality is enabled.
MYSQLI_SERVER_QUERY_NO_GOOD_INDEX_USED
MYSQLI_SERVER_QUERY_NO_INDEX_USED
MYSQLI_SERVER_PUBLIC_KEY
MYSQLI_REFRESH_GRANT	Refreshes the grant tables.
MYSQLI_REFRESH_LOG	Flushes the logs, like executing the FLUSH LOGS SQL statement.
MYSQLI_REFRESH_TABLES	Flushes the table cache, like executing the FLUSH TABLES SQL statement.
MYSQLI_REFRESH_HOSTS	Flushes the host cache, like executing the FLUSH HOSTS SQL statement.
MYSQLI_REFRESH_REPLICA	Alias of MYSQLI_REFRESH_SLAVE constant. Available as of PHP 8.1.0.
MYSQLI_REFRESH_STATUS	Reset the status variables, like executing the FLUSH STATUS SQL statement.
MYSQLI_REFRESH_THREADS	Flushes the thread cache.
MYSQLI_REFRESH_SLAVE	On a slave replication server: resets the master server information, and restarts the slave. Like executing the RESET SLAVE SQL statement.
MYSQLI_REFRESH_MASTER	On a master replication server: removes the binary log files listed in the binary log index, and truncates the index file. Like executing the RESET MASTER SQL statement.
MYSQLI_TRANS_COR_AND_CHAIN	Appends "AND CHAIN" to mysqli_commit() or mysqli_rollback().
MYSQLI_TRANS_COR_AND_NO_CHAIN	Appends "AND NO CHAIN" to mysqli_commit() or mysqli_rollback().
MYSQLI_TRANS_COR_RELEASE	Appends "RELEASE" to mysqli_commit() or mysqli_rollback().
MYSQLI_TRANS_COR_NO_RELEASE	Appends "NO RELEASE" to mysqli_commit() or mysqli_rollback().
MYSQLI_TRANS_START_READ_ONLY	Start the transaction as "START TRANSACTION READ ONLY" with mysqli_begin_transaction().
MYSQLI_TRANS_START_READ_WRITE	Start the transaction as "START TRANSACTION READ WRITE" with mysqli_begin_transaction().
MYSQLI_TRANS_START_CONSISTENT_SNAPSHOT	Start the transaction as "START TRANSACTION WITH CONSISTENT SNAPSHOT" with mysqli_begin_transaction().
MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT	Requires MySQL 5.6.5 and up.