INTRODUCTION TO PHP AND MySQL – PHP FUNCTIONS FOR WORKING WITH MySQL
PHP provides a vast library of built-in functions that let you perform all sorts of tasks without having to look to third-party software vendors for a solution. The online reference to these functions provided by the PHP web site http://www.php.net/functionname is second to none.
However, for convenience, the following contains a list of the most commonly used PHP functions specifically designed to interact with MySQL databases.
Common PHP mysqli_* Functions
This list of functions and their definitions are current as of PHP 5.2.9.
mysqli_affected_rows
mysqli_affected_rows(link)
This function returns the number of affected rows in the previous MySQL INSERT, UPDATE, DELETE, or REPLACE operation performed with the specified link. If the previous operation was a SELECT, this function returns the same value as mysqli_num_rows. It returns -1 if the previous operation failed.
mysqli_character_set_name
mysql_client_encoding(link)
This function returns the name of the default character set in use by the specified connection (for example, latin1 or utf8).
mysqli_close
mysqli_close(link)
This function closes the specified MySQL connection (link). If link refers to a persistent connection, this function call is ignored. As non-persistent connections automatically are closed by PHP at the end of a script, this function is usually unnecessary. This function returns TRUE on success, FALSE on failure.
mysqli_connect
mysqli_connect([host[, username[, password[, database[, port[, socket]]]]]])
This function opens a connection to a MySQL server and returns a link reference that may be used in other MySQL-related functions. This function takes up to six arguments, all of them optional:
host The address of the computer where the MySQL server is running, as a hostname or IP address string. The special values 'localhost' or NULL can be used to connect to the same computer that’s running your web server.
Add p: to the start of this value to tell PHP to reuse an existing connection to the server if one has previously been established by your web server.
username The MySQL username to be used for the connection.
password The password for the MySQL user to be used for the connection, or NULL to connect without a password.
database The default database for queries using this connection.
port The port number to connect to on the specified host. MySQL servers usually run on port 3306.
socket The named socket to use for a local server connection. When connecting to a MySQL server running on the same computer, PHP can use a named socket instead of the TCP/IP protocol to create a more efficient connection.
If the connection attempt is unsuccessful, an error message will be displayed and the function will return FALSE.
mysqli_connect_errno
mysqli_connect_errno()
If the last call to mysqli_connect failed, this function will return a number that indicates the type of error that occurred. If the last call to mysqli_connect was successful, this function will return 0.
mysqli_connect_error
mysqli_connect_error()
If the last call to mysqli_connect failed, this function will return a string that describes the error that occurred. If the last call to mysqli_connect was successful, this function will return ‘ ‘ (an empty string).
mysqli_data_seek
mysqli_data_seek(result, row_number)
This function moves the internal result pointer of the result set identified by result to row number row_number, so that the next call to a mysqli_fetch_… function will retrieve the specified row. It returns TRUE on success, and FALSE on failure. The first row in a result set is number 0.
mysqli_errno
mysqli_errno(link)
This function returns a number that indicates the type of error that occurred as a result of the last MySQL operation on specified MySQL connection (link). If no error occurred, this function returns 0.
mysqli_error
mysqli_error(link)
This function returns the text of the error message from the last MySQL operation on the specified MySQL connection (link). If no error occurred, this function returns ‘ ‘ (an empty string).
mysqli_fetch_all
mysqli_fetch_all(result[, type])
This function fetches the complete contents of the MySQL result set result in the form of an array. Each item within that array corresponds to a row of the result set, and is itself represented by an array.
The optional type argument can be used to specify the type of arrays that are used to represent the rows of the result set. If specified, this argument must be set to one of these constants:
MYSQLI_ASSOC
The rows are represented by associative arrays (that is, $results[rowNumber][columnName]).
MYSQLI_NUM
The rows are represented by numbered arrays (that is, $results[rowNumber][columnNumber]).
MYSQLI_BOTH (default)
The rows are represented by arrays that provided both numbered and associative indexes.
mysqli_fetch_array
mysqli_fetch_array(result[, type])
This function fetches the next row of the MySQL result set result, and then advances the internal row pointer of the result set to the next row. If there are no rows left in the result set, it returns NULL instead.
By default, the returned array provides both numbered and associative indexes. However, you can use the optional type argument to specify the type of array to be returned.
mysqli_fetch_assoc
mysqli_fetch_assoc(result)
This function fetches a result row as an associative array. It’s identical to mysqli_fetch_array called with the type argument set to MYSQLI_ASSOC.
mysqli_fetch_field
mysqli_fetch_field(result)
This function returns a PHP object that contains information about a column in the supplied result set (result), beginning with the first column. Call this function repeatedly to retrieve information about each of the columns of the result set in turn. When all of the columns have been described, this function returns FALSE.
Assuming the result of this function is stored in $field, then the properties of the retrieved field are accessible as shown below:
Object fields for mysqli_fetch_field_direct
Object Property / Information Contained$field->name / Column name or alias
$field->orgname / The original (non-alias) column name
$field->table / Name or alias of the table to which the column belongs
$field->orgtable / The original (non-alias) table name
$field->def / The default value for this field, as a string
$field->max_length / Maximum length of the column in this result set
$field->length / Maximum length of the column in the table definition
$field->charsetnr / The number identifying the character set for this field
$field->flags / An integer whose bits describe attributes of the field
$field->type / An integer that indicates the data type of the field
$field->decimals / The number of decimal places in the field (for numbers)
mysqli_fetch_field_direct
mysqli_fetch_field_direct(result, field_pos)
This function returns a PHP object that contains information about a particular column in the supplied result set (result). The field_pos argument specifies the position of the column to describe (0 indicates the first column).
mysqli_fetch_fields
mysqli_fetch_fields(result)
This function returns an array of PHP objects that contain information about each of the columns in the supplied result set (result).
mysqli_fetch_lengths
mysqli_fetch_lengths(result)
This function returns an array containing the lengths of each of the fields in the last-fetched row of the specified result set.
mysqli_fetch_object
mysqli_fetch_object(result[, className[, params]])
This function returns the next result row from result in the form of an object, and advances the internal row pointer of the result set to the next row. Column values for the row become accessible as named properties of the object (for example, $row->user for the value of the user field in the $row object). If there are no rows left in the result set, it returns NULL instead.
The optional className argument specifies the name of a PHP class to use for the object. The params argument can be used to provide an array of parameters to be passed to the constructor.
mysqli_fetch_row
mysqli_fetch_row(result)
This function fetches a result row as a numerical array. It’s identical to mysqli_fetch_array called with the type argument set to MYSQL_NUM.
mysqli_field_count
`
mysqli_field_count(link)
This function returns the number of columns present in the result set of the last query performed with the specified MySQL connection (link). This function will return 0 if the last query was an INSERT, UPDATE, DELETE, or other query that does not return a result set.
mysqli_field_seek
mysqli_field_seek(result, field_position)
This function sets the field position for the next call to mysqli_fetch_field.
mysqli_field_tell
mysqli_field_tell(result)
This function returns the position of the next column of the result set result whose description would be returned by a call to mysqli_fetch_field (0 indicates the first column).
mysqli_free_result
mysqli_free_result(result)
This function destroys the specified result set (result), freeing all memory associated with it. As all memory is freed automatically at the end of a PHP script, this function is only really useful when a large result set is no longer needed and your script still has a lot of work to do.
mysqli_get_client_info
mysqli_get_client_info()
This function returns a string indicating the version of the MySQL client library that PHP is using (for example, '5.1.34').
mysqli_get_client_version
mysqli_get_client_version()
This function returns an integer indicating the version of the MySQL client library that PHP is using (for example, 50134).
mysqli_get_host_info
mysqli_get_host_info(link)
This function returns a string describing the type of connection and server host name for the specified (link) MySQL connection (for example, 'Localhost via UNIX socket').
mysqli_get_proto_info
mysqli_get_proto_info(link)
This function returns an integer indicating the MySQL protocol version in use for the specified (link) MySQL connection (for example, 10).
mysqli_get_server_info
mysqli_get_server_info(link)
This function returns a string indicating the version of MySQL server in use on the specified (link) MySQL connection (for example, '5.1.34').
mysqli_get_server_version
mysqli_get_server_version(link)
This function returns an integer indicating the version of the MySQL server to which the specified connection (link) is connected (for example, 50134).
mysqli_info
mysqli_info(link)
This function returns a string that contains information about the effects of the last query executed on the specified connection (link), if it was a query that inserted new data into the database (such as an INSERT, UPDATE, or LOAD DATA INFILE query).
mysqli_insert_id
mysqli_insert_id(link)
This function returns the value that was assigned to an AUTO_INCREMENT column automatically in the previous INSERT query for the specified MySQL connection (link). If no AUTO_INCREMENT value was assigned in the previous query, 0 is returned instead.
mysqli_num_fields
mysqli_num_fields(result)
This function returns the number of columns in a MySQL result set (result).
mysqli_num_rows
mysqli_num_rows(result)
This function returns the number of rows in a MySQL result set (result). This method is incompatible with unbuffered result sets created by calling mysqli_real_query followed by mysqli_use_result, or by calling mysqli_query with the resultmode parameter set to MYSQLI_USE_RESULT.
mysqli_ping
mysqli_ping(link)
When a PHP script runs for a long time, it’s possible that an open MySQL connection (link) may be closed or disconnected at the server end. If you suspect this possibility, call this function before using the suspect connection to confirm that it’s active, and to reconnect if the connection did indeed go down.
mysqli_query
mysqli_query(link, query[, mode])
This function executes the specified MySQL query (query) using the specified database connection (link), and returns a MySQL result set.
The optional mode parameter can be set to MYSQLI_USE_RESULT to instruct PHP to download the results from the MySQL server on demand, instead of all at once.
This can reduce the amount of memory used by your PHP script when processing large result sets. If you choose to do this, you must make sure to call mysqli_free_result before attempting to perform another query using the same database connection.
If the query fails, an error message to that effect will be displayed, and the function will return FALSE instead of a result set (which evaluates to TRUE). If the error occurs, the error number and message can be obtained using mysqli_errno and mysqli_error respectively.
mysqli_real_escape_string
mysqli_real_escape_string(link, string)
This function returns an escaped version of a string (with backslashes before special characters such as quotes) for use in a MySQL query. This function is more thorough than addslashes or PHP’s Magic Quotes feature.
This function takes into account the character set of the specified MySQL connection (link) when determining which characters need to be escaped.
mysqli_real_query
mysqli_real_query(link, query)
A less convenient alternative to mysqli_query, this function executes the specified MySQL query (query) using the specified database connection (link), but ignores the result set returned by the server (if any). If you wish to retrieve the results of a query performed using this function, you must call mysqli_store_result or mysqli_use_result. You can determine if a result set is available to be retrieved using mysqli_field_count.
This function returns TRUE if the query was successful, or FALSE if an error occurred.
mysqli_select_db
mysqli_select_db(link, database)
This function selects the default database (database) for the MySQL connection specified (link).
mysqli_set_charset
mysqli_set_charset(link, charset)
This function sets the default character set (charset) to be used for text values in the SQL queries sent to and result sets received from the specified MySQL connection (link). In web applications, it’s most common to set the character set to 'utf8' to submit and retrieve UTF-8 encoded text.
mysqli_stat
mysqli_stat(link)
This function returns a string describing the current status of the MySQL server. The string is identical in format to that produced by the mysqladmin utility:
Uptime: 28298 Threads: 1 Questions: 56894 Slow queries: 0
Opens: 16 Flush tables: 1 Open tables: 8 Queries per second avg: 36.846
mysqli_store_result
mysqli_store_result(link)
Retrieves and returns the entire result set for an SQL query just performed using mysqli_real_query on the specified MySQL connection (link). It’s much more common (and convenient) to simply use mysqli_query to perform the query and then immediately retrieve the results.
This function returns FALSE if there’s an error retrieving the result set, or if there’s no result set available to retrieve.
mysqli_thread_id
mysqli_thread_id(link)
This function returns the ID of the server thread responsible for handling the specified connection (link).
mysqli_use_result
mysqli_use_result(link)
Begins to retrieve the result set for an SQL query just performed using mysqli_real_query on the specified MySQL connection (link). The results will be retrieved from the MySQL server a row at a time, on demand, which can reduce the amount of memory required by your PHP script when working with a large result set. It’s much more common (and convenient) to simply use mysqli_query to perform the query and then immediately begin to retrieve the results.