It’s Festive Season! Enjoy 30% Off

Use code FESTIVE30 at checkout

Code has been added to clipboard!

Understanding PHP MySQLi: A Handy Cheat Sheet of Functions

Reading time 5 min
Published Aug 8, 2017
Updated Oct 15, 2019

There are three ways to establish a connection with MySQL. Out of all three, you have to use PHP MySQLi for two.

PHP MySQLi (the i stands for improved) is an extension that works on Linux and Windows operating systems, and with MySQL 4.1.13 or newer versions. It's free and easy to download, install, and use.

Due to its popularity, there are some inbuilt PHP MySQLi functions that make working with database queries easier. In our PHP MySQLi tutorial, we will introduce you an extensive list of them.

PHP MySQLi: Main Tips

  • PHP has inbuilt functions for handling MySQL queries using MySQLi extension. In this PHP MySQLi tutorial, you will be presented with an alphabetized list of all of them.
  • MySQLi extension is free to download and use. You can find the installation package here.
  • PHP MySQL functions make connecting to MySQL databases easier. Also, query creation takes fewer lines of code.

List of Functions

Take a look at the functions listed below. While using MySQLi PHP, developers may find a lot of these functions useful:

Function Description
mysqli_affected_rows() Return number of rows affected in previous MySQL operations
mysqli_autocommit() Turn auto-committing database modifications on/off
mysqli_change_user() Changes the user of the specified database connection
mysqli_character_set_name() Return default character set for the database connection
mysqli_close() Close previously opened database connection
mysqli_commit() Commit current transaction
mysqli_connect_errno() Return error code from last connection error
mysqli_connect_error() Return error description from last connection error
mysqli_connect() Open new connection to MySQL server
mysqli_data_seek() Adjust result pointer to arbitrary row in result-set
mysqli_debug() Perform debugging operations
mysqli_dump_debug_info() Dump debugging info to log
mysqli_errno() Return last error code for most recent function call
mysqli_error_list() Return list of errors for most recent function call
mysqli_error() Return last error description for most recent function call
mysqli_fetch_all() Fetch result rows as associative array, numeric array, or both
mysqli_fetch_array() Fetch result row as associative, numeric array, or both
mysqli_fetch_assoc() Fetch result row as associative array
mysqli_fetch_field_direct() Return meta-data for single field in result set, as object
mysqli_fetch_field() Return next field in result set, as object
mysqli_fetch_fields() Return array of objects that represent fields in result set
mysqli_fetch_lengths() Return lengths of columns of current row in result set
mysqli_fetch_object() Return current row of result set, as object
mysqli_fetch_row() Fetch one row from result-set and returns the set as enumerated array
mysqli_field_count() Return number of columns for most recent query
mysqli_field_seek() Set field cursor to given field offset
mysqli_field_tell() Return position of field cursor
mysqli_free_result() Free memory associated with result
mysqli_get_charset() Return character set object
mysqli_get_client_info() Return MySQL client library version
mysqli_get_client_stats() Return statistics about client per-process
mysqli_get_client_version() Return MySQL client library version as integer
mysqli_get_connection_stats() Return statistics about client connection
mysqli_get_host_info() Return MySQL server hostname and connection type
mysqli_get_proto_info() Return MySQL protocol version
mysqli_get_server_info() Return MySQL server version
mysqli_get_server_version() Return MySQL server version as integer
mysqli_info() Return information about most recently executed query
mysqli_init() Initialize MySQLi and return resource for using with mysqli_real_connect()
mysqli_insert_id() Return auto-generated id used in last query
mysqli_kill() Ask server to kill MySQL thread
mysqli_more_results() Check whether there are more results from multi query
mysqli_multi_query() Perform one or more queries on database
mysqli_next_result() Prepare next result set from mysqli_multi_query()
mysqli_num_fields() Return number of fields in result set
mysqli_num_rows() Return number of rows in result set
mysqli_options() Set extra connect options and affect behavior for connection
mysqli_ping() Ping server connection, or tries reconnecting if connection has gone down
mysqli_prepare() Prepare SQL statement for execution
mysqli_query() Perform query against the database
mysqli_real_connect() Open new connection to MySQL server
mysqli_real_escape_string() Escape special characters in string for using in SQL statements
mysqli_real_query() Execute SQL queries
mysqli_reap_async_query() Return results from async queries
mysqli_refresh() Refresh tables or caches, or reset the replication server info
mysqli_rollback() Roll back current transaction for database
mysqli_select_db() Change default database for connection
mysqli_set_charset() Set default client character set
mysqli_set_local_infile_default() Unset user defined handler for load local infile command
mysqli_set_local_infile_handler() Set callback function for LOAD DATA LOCAL INFILE command
mysqli_sqlstate() Return SQLSTATE error code for last MySQL operation
mysqli_ssl_set() Used in order to establish secure connections using SSL
mysqli_stat() Return current system status
mysqli_stmt_init() Initialize statement and return object for using with mysqli_stmt_prepare()
mysqli_store_result() Transfer result set from last query
mysqli_thread_id() Return thread ID for current connection
mysqli_thread_safe() Return whether client library is compiled as thread-safe
mysqli_use_result() Initiate retrieval of result set from last query executed with the mysqli_real_query()
mysqli_warning_count() Return number of warnings from last query in connection

Note: a deprecated MySQL extension had a useful function called mysql_result, and those who switched to MySQLi often look for a corresponding mysqli_result function. Unfortunately, it does not exist.

PHP MySQLi: Summary

  • If you are using MySQL database management system via MySQLi extension, you can find useful PHP inbuilt functions (such as MySQLi fetch array) meant for handling MySQL queries.
  • If you're not using MySQLi at this moment, you can download the installation package here free of charge.
  • PHP MySQL functions let you access MySQL databases and send queries using less code lines than usual.
  • Unlike MySQL extension that is now deprecated, MySQLi has no mysqli_result function.