MySQLi Tutorial MySQLi References

MySQLi Functions



The MySQLi is a PHP extension allows you to access MySQL database servers using PHP script. 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

The mysqli Class

Class Description
mysqli The mysqli class.
Methods/PropertiesDescription
__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.

The mysqli_stmt Class

Class Description
mysqli_stmt The mysqli_stmt class.
Methods/PropertiesDescription
__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.

The mysqli_result Class

Class Description
mysqli_result The mysqli_result class.
Methods/PropertiesDescription
$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.

The mysqli_driver Class

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

The mysqli_warning Class

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

The mysqli_sql_exception Class

Class Description
mysqli_sql_exception The mysqli_sql_exception class.

Aliases and deprecated MySQLi Functions

FunctionsDescription
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().

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.

ConstantsDescription
MYSQLI_READ_DEFAULT_GROUPRead options from the named group from my.cnf or the file specified with MYSQLI_READ_DEFAULT_FILE
MYSQLI_READ_DEFAULT_FILERead options from the named option file instead of from my.cnf
MYSQLI_OPT_CONNECT_TIMEOUTConnect timeout in seconds
MYSQLI_OPT_READ_TIMEOUTCommand execution result timeout in seconds. Available as of PHP 7.2.0.
MYSQLI_OPT_LOCAL_INFILEEnables command LOAD LOCAL INFILE
MYSQLI_OPT_INT_AND_FLOAT_NATIVEConvert integer and float columns back to PHP numbers. Only valid for mysqlnd.
MYSQLI_OPT_NET_CMD_BUFFER_SIZEThe size of the internal command/network buffer. Only valid for mysqlnd.
MYSQLI_OPT_NET_READ_BUFFER_SIZEMaximum read chunk size in bytes when reading the body of a MySQL command packet. Only valid for mysqlnd.
MYSQLI_OPT_SSL_VERIFY_SERVER_CERTRequires MySQL 5.1.10 and up
MYSQLI_INIT_COMMANDCommand to execute when connecting to MySQL server. Will automatically be re-executed when reconnecting.
MYSQLI_CLIENT_SSLUse SSL (encrypted protocol). This option should not be set by application programs; it is set internally in the MySQL client library
MYSQLI_CLIENT_COMPRESSUse compression protocol
MYSQLI_CLIENT_INTERACTIVEAllow 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_SPACEAllow spaces after function names. Makes all functions names reserved words.
MYSQLI_CLIENT_NO_SCHEMADon't allow the db_name.tbl_name.col_name syntax.
MYSQLI_CLIENT_MULTI_QUERIESAllows multiple semicolon-delimited queries in a single mysqli_query() call.
MYSQLI_STORE_RESULTFor using buffered resultsets
MYSQLI_USE_RESULTFor using unbuffered resultsets
MYSQLI_ASSOCColumns are returned into the array having the fieldname as the array index.
MYSQLI_NUMColumns are returned into the array having an enumerated index.
MYSQLI_BOTHColumns are returned into the array having both a numerical index and the fieldname as the associative index.
MYSQLI_NOT_NULL_FLAGIndicates that a field is defined as NOT NULL
MYSQLI_PRI_KEY_FLAGField is part of a primary index
MYSQLI_UNIQUE_KEY_FLAGField is part of a unique index.
MYSQLI_MULTIPLE_KEY_FLAGField is part of an index.
MYSQLI_BLOB_FLAGField is defined as BLOB
MYSQLI_UNSIGNED_FLAGField is defined as UNSIGNED
MYSQLI_ZEROFILL_FLAGField is defined as ZEROFILL
MYSQLI_AUTO_INCREMENT_FLAGField is defined as AUTO_INCREMENT
MYSQLI_TIMESTAMP_FLAGField is defined as TIMESTAMP
MYSQLI_SET_FLAGField is defined as SET
MYSQLI_NUM_FLAGField is defined as NUMERIC
MYSQLI_PART_KEY_FLAGField is part of an multi-index
MYSQLI_GROUP_FLAGField is part of GROUP BY
MYSQLI_TYPE_DECIMALField is defined as DECIMAL
MYSQLI_TYPE_NEWDECIMALPrecision math DECIMAL or NUMERIC field (MySQL 5.0.3 and up)
MYSQLI_TYPE_BITField is defined as BIT (MySQL 5.0.3 and up)
MYSQLI_TYPE_TINYField is defined as TINYINT
MYSQLI_TYPE_SHORTField is defined as SMALLINT
MYSQLI_TYPE_LONGField is defined as INT
MYSQLI_TYPE_FLOATField is defined as FLOAT
MYSQLI_TYPE_DOUBLEField is defined as DOUBLE
MYSQLI_TYPE_NULLField is defined as DEFAULT NULL
MYSQLI_TYPE_TIMESTAMPField is defined as TIMESTAMP
MYSQLI_TYPE_LONGLONGField is defined as BIGINT
MYSQLI_TYPE_INT24Field is defined as MEDIUMINT
MYSQLI_TYPE_DATEField is defined as DATE
MYSQLI_TYPE_TIMEField is defined as TIME
MYSQLI_TYPE_DATETIMEField is defined as DATETIME
MYSQLI_TYPE_YEARField is defined as YEAR
MYSQLI_TYPE_NEWDATEField is defined as DATE
MYSQLI_TYPE_INTERVALField is defined as INTERVAL
MYSQLI_TYPE_ENUMField is defined as ENUM
MYSQLI_TYPE_SETField is defined as SET
MYSQLI_TYPE_TINY_BLOBField is defined as TINYBLOB
MYSQLI_TYPE_MEDIUM_BLOBField is defined as MEDIUMBLOB
MYSQLI_TYPE_LONG_BLOBField is defined as LONGBLOB
MYSQLI_TYPE_BLOBField is defined as BLOB
MYSQLI_TYPE_VAR_STRINGField is defined as VARCHAR
MYSQLI_TYPE_STRINGField is defined as CHAR or BINARY
MYSQLI_TYPE_CHARField is defined as TINYINT. For CHAR, see MYSQLI_TYPE_STRING
MYSQLI_TYPE_GEOMETRYField is defined as GEOMETRY
MYSQLI_TYPE_JSONField is defined as JSON. Only valid for mysqlnd and MySQL 5.7.8 and up.
MYSQLI_NEED_DATAMore data available for bind variable
MYSQLI_NO_DATANo more data available for bind variable
MYSQLI_DATA_TRUNCATEDData truncation occurred. Available since MySQL 5.0.5.
MYSQLI_ENUM_FLAGField is defined as ENUM.
MYSQLI_BINARY_FLAGField 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_INDEXReport if no index or bad index was used in a query.
MYSQLI_REPORT_ERRORReport errors from mysqli function calls.
MYSQLI_REPORT_STRICTThrow a mysqli_sql_exception for errors instead of warnings.
MYSQLI_REPORT_ALLSet all options on (report all).
MYSQLI_REPORT_OFFTurns reporting off.
MYSQLI_DEBUG_TRACE_ENABLEDIs 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_GRANTRefreshes the grant tables.
MYSQLI_REFRESH_LOGFlushes the logs, like executing the FLUSH LOGS SQL statement.
MYSQLI_REFRESH_TABLESFlushes the table cache, like executing the FLUSH TABLES SQL statement.
MYSQLI_REFRESH_HOSTSFlushes the host cache, like executing the FLUSH HOSTS SQL statement.
MYSQLI_REFRESH_REPLICAAlias of MYSQLI_REFRESH_SLAVE constant. Available as of PHP 8.1.0.
MYSQLI_REFRESH_STATUSReset the status variables, like executing the FLUSH STATUS SQL statement.
MYSQLI_REFRESH_THREADSFlushes the thread cache.
MYSQLI_REFRESH_SLAVEOn a slave replication server: resets the master server information, and restarts the slave. Like executing the RESET SLAVE SQL statement.
MYSQLI_REFRESH_MASTEROn 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_CHAINAppends "AND CHAIN" to mysqli_commit() or mysqli_rollback().
MYSQLI_TRANS_COR_AND_NO_CHAINAppends "AND NO CHAIN" to mysqli_commit() or mysqli_rollback().
MYSQLI_TRANS_COR_RELEASEAppends "RELEASE" to mysqli_commit() or mysqli_rollback().
MYSQLI_TRANS_COR_NO_RELEASEAppends "NO RELEASE" to mysqli_commit() or mysqli_rollback().
MYSQLI_TRANS_START_READ_ONLYStart the transaction as "START TRANSACTION READ ONLY" with mysqli_begin_transaction().
MYSQLI_TRANS_START_READ_WRITEStart the transaction as "START TRANSACTION READ WRITE" with mysqli_begin_transaction().
MYSQLI_TRANS_START_CONSISTENT_SNAPSHOTStart the transaction as "START TRANSACTION WITH CONSISTENT SNAPSHOT" with mysqli_begin_transaction().
MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERTRequires MySQL 5.6.5 and up.