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_resultfunction.