PHP MySQL Functions

90

PHP MySQL FunctionsThe MySQL functions allows you to access MySQL database servers.


Installation

For the MySQL functions to be available, you must compile PHP with MySQL support.

For compiling, use –with-mysql=DIR (the optional DIR points to the MySQL directory).

Note: For full functionality of MySQL versions greater than 4.1., use the MySQLi extension instead. If you would like to install both the mysql extension and the mysqli extension you should use the same client library to avoid any conflicts.

Installation on Linux Systems

PHP 5+: MySQL and the MySQL library is not enabled by default. Use the –with-mysql=DIR configure option to include MySQL support and download headers and libraries from www.mysql.com.

Installation on Windows Systems

PHP 5+: MySQL is not enabled by default, so the php_mysql.dll must be enabled inside of php.ini. Also, PHP needs access to the MySQL client library. A file named libmysql.dll is included in the Windows PHP distribution, and in order for PHP to talk to MySQL this file needs to be available to the Windows systems PATH.

To enable any PHP extension, the PHP extension_dir setting (in the php.ini file) should be set to the directory where the PHP extensions are located. An example extension_dir value is c:\php\ext.

Note: If you get the following error when starting the web server: “Unable to load dynamic library ‘./php_mysql.dll'”, this is because php_mysql.dll or libmysql.dll cannot be found by the system.


Runtime Configuration

The behavior of the MySQL functions is affected by settings in the php.ini file.

MySQL configuration options:

NameDefaultDescriptionChangeable
mysql.allow_persistent“1”Whether or not to allow persistent connectionsPHP_INI_SYSTEM
mysql.max_persistent“-1”The maximum number of persistent connections per processPHP_INI_SYSTEM
mysql.max_links“-1”The maximum number of connections per process (persistent connections included)PHP_INI_SYSTEM
mysql.trace_mode“0”Trace mode. When set to “1”, warnings and SQL-errors will be displayed. Available since PHP 4.3PHP_INI_ALL
mysql.default_portNULLThe default TCP port number to usePHP_INI_ALL
mysql.default_socketNULLThe default socket name to use. Available since PHP 4.0.1PHP_INI_ALL
mysql.default_hostNULLThe default server host to use (doesn’t apply in SQL safe mode)PHP_INI_ALL
mysql.default_userNULLThe default user name to use (doesn’t apply in SQL safe mode)PHP_INI_ALL
mysql.default_passwordNULLThe default password to use (doesn’t apply in SQL safe mode)PHP_INI_ALL
mysql.connect_timeout“60”Connection timeout in secondsPHP_INI_ALL

 


Resource Types

There are two resource types used in the MySQL extension. The first one is the link_identifier for a database connection, the second is a resource which holds the result of a query.

Note: Most MySQL functions accept link_identifier as the last optional parameter. If it is not provided, the last opened connection is used.


PHP MySQL Functions

PHP: indicates the earliest version of PHP that supports the function.

FunctionDescriptionPHP
mysql_affected_rows()Returns the number of affected rows in the previous MySQL operation3
mysql_change_user()Deprecated. Changes the user of the current MySQL connection3
mysql_client_encoding()Returns the name of the character set for the current connection4
mysql_close()Closes a non-persistent MySQL connection3
mysql_connect()Opens a non-persistent MySQL connection3
mysql_create_db()Deprecated. Creates a new MySQL database. Use mysql_query() instead3
mysql_data_seek()Moves the record pointer3
mysql_db_name()Returns a database name from a call to mysql_list_dbs()3
mysql_db_query()Deprecated. Sends a MySQL query. Use mysql_select_db() and mysql_query() instead3
mysql_drop_db()Deprecated. Deletes a MySQL database. Use mysql_query() instead3
mysql_errno()Returns the error number of the last MySQL operation3
mysql_error()Returns the error description of the last MySQL operation3
mysql_escape_string()Deprecated. Escapes a string for use in a mysql_query. Use mysql_real_escape_string() instead4
mysql_fetch_array()Returns a row from a recordset as an associative array and/or a numeric array3
mysql_fetch_assoc()Returns a row from a recordset as an associative array4
mysql_fetch_field()Returns column info from a recordset as an object3
mysql_fetch_lengths()Returns the length of the contents of each field in a result row3
mysql_fetch_object()Returns a row from a recordset as an object3
mysql_fetch_row()Returns a row from a recordset as a numeric array3
mysql_field_flags()Returns the flags associated with a field in a recordset3
mysql_field_len()Returns the maximum length of a field in a recordset3
mysql_field_name()Returns the name of a field in a recordset3
mysql_field_seek()Moves the result pointer to a specified field3
mysql_field_table()Returns the name of the table the specified field is in3
mysql_field_type()Returns the type of a field in a recordset3
mysql_free_result()Free result memory3
mysql_get_client_info()Returns MySQL client info4
mysql_get_host_info()Returns MySQL host info4
mysql_get_proto_info()Returns MySQL protocol info4
mysql_get_server_info()Returns MySQL server info4
mysql_info()Returns information about the last query4
mysql_insert_id()Returns the AUTO_INCREMENT ID generated from the previous INSERT operation3
mysql_list_dbs()Lists available databases on a MySQL server3
mysql_list_fields()Deprecated. Lists MySQL table fields. Use mysql_query() instead3
mysql_list_processes()Lists MySQL processes4
mysql_list_tables()Deprecated. Lists tables in a MySQL database. Use mysql_query() instead3
mysql_num_fields()Returns the number of fields in a recordset3
mysql_num_rows()Returns the number of rows in a recordset3
mysql_pconnect()Opens a persistent MySQL connection3
mysql_ping()Pings a server connection or reconnects if there is no connection4
mysql_query()Executes a query on a MySQL database3
mysql_real_escape_string()Escapes a string for use in SQL statements4
mysql_result()Returns the value of a field in a recordset3
mysql_select_db()Sets the active MySQL database3
mysql_stat()Returns the current system status of the MySQL server4
mysql_tablename()Deprecated. Returns the table name of field. Use mysql_query() instead3
mysql_thread_id()Returns the current thread ID4
mysql_unbuffered_query()Executes a query on a MySQL database (without fetching / buffering the result)4

 


PHP MySQL Constants

Since PHP 4.3 it has been possible to specify additional flags for the mysql_connect() and mysql_pconnect() functions:

PHP: indicates the earliest version of PHP that supports the constant.

ConstantDescriptionPHP
MYSQL_CLIENT_COMPRESSUse compression protocol4.3
MYSQL_CLIENT_IGNORE_SPACEAllow space after function names4.3
MYSQL_CLIENT_INTERACTIVEAllow interactive timeout seconds of inactivity before closing the connection4.3
MYSQL_CLIENT_SSLUse SSL encryption (only available with version 4+ of the MySQL client library)4.3

The mysql_fetch_array() function uses a constant for the different types of result arrays. The following constants are defined:

ConstantDescriptionPHP
MYSQL_ASSOCColumns are returned into the array with the fieldname as the array index
MYSQL_BOTHColumns are returned into the array having both a numerical index and the fieldname as the array index
MYSQL_NUMColumns are returned into the array having a numerical index (indeX

starts at o

loading...
SHARE

LEAVE A REPLY