6.3 MySQL Function Reference

This section lists PHP functions for interacting with a MySQL server. We've divided them into those that are frequently used and those that are less frequently used. We've also included a list of the functions we don't use, and the reasons why you should avoid them. We recommend that at a minimum you read the information about the five main functions we've used in this chapter: mysql_connect( ), mysql_select_db( ), mysql_query( ), mysql_fetch_array( ), and mysql_error( ).

Web database applications can be developed with only a few functions. However, in many cases, additional functionality is required. For example, you may want to choose performance-conscious alternatives and it's often useful to retrieve only a part of the data without processing the complete dataset. Functions for all of these tasks are described in this section. Writing data to a database and the functions mysql_affected_rows( ) and mysql_insert_id( ) are discussed in more detail in Chapter 8.

6.3.1 Frequently Used Functions


int mysql_affected_rows([resource connection])

Returns the number of rows affected by the last UPDATE, DELETE, or INSERT SQL statement, and -1 if the last query failed. The function takes as an optional parameter a server connection resource handle. If no parameter is passed, the most recently opened connection is assumed.

This function doesn't work for SELECT statements; mysql_num_rows( ) should be used instead.

For example, if a customer is deleted with the SQL statement:

DELETE FROM customer WHERE CUST_ID=1

then mysql_affected_rows( ) returns a value of 1 if that customer has been successfully deleted.

The function may report that zero rows were affected, even if a statement works successfully, because it is possible that an operation may not modify the database. For example, the statement:

UPDATE customer SET zipcode='3053' WHERE city = 'Carlton'

always executes but mysql_affected_rows( ) returns 0 if there are no customers who live in Carlton or if the Zip Code of the customers who live in Carlton is already 3053.

If all rows in a table are deleted using a DELETE statement without a WHERE clause, mysql_affected_rows( ) reports 0 rows were affected.

Examples using mysql_affected_rows( ) are in Chapter 8.


resource mysql_connect([string hostname[, string username [, string password [, bool new_connection [, int flags]]]]])

Establishes a connection to the MySQL server. The function returns a connection resource handle on success that can be used to access databases through subsequent commands. Returns false on failure.

The command has five optional parameters. In practice, the first three parameters hostname, username, and password are almost always used. The first permits both a hostname and an optional port number; the default port for MySQL is 3306 (ports are discussed in more detail in Appendix D). The value localhost is usually supplied as the hostname when the server runs on the same machine as the PHP scripting engine.

This function should be called once in a script, assuming you don't close the connection (see mysql_close( )) and you don't want a connection with different parameters. Indeed, subsequent calls to the function in the same script with the same parameters don't return a new connection: they return the same connection resource returned from the first successful call to the function. The exception is if the fourth parameter new_connection is supplied and set to true: if this is the case, a new connection is always opened. This parameter was added in PHP 4.2.

The fifth parameter flags was added in PHP 4.3, and doesn't work reliably at the time of writing. We don't discuss it here.

The mysql_pconnect( ) function is a performance-conscious alternative to mysql_connect( ), and it's discussed later in this section.


int mysql_errno([resource connection])

Returns the MySQL error number of the last error on the connection resource, or zero if no error occurred. If no connection is provided, the most recently opened connection is assumed. Any successful MySQL-related function call resets the value of this function to zero, with the exception of mysql_error( ) and mysql_errno( ), which do not change the value.


string mysql_error(resource connection)

Returns a descriptive string of the last error on the connection resource or an empty string if no error occurred. An optional connection can be supplied; otherwise the most-recently opened connection is assumed. Any successful MySQL-related function call resets the text to the empty string, with the exception of mysql_error( ) and mysql_errno( ), which do not change this value.


array mysql_fetch_array(resource result_set [, int result_type])

Fetches the result set data one row at a time. The first parameter is a result resource result_set that was returned from a mysql_query( ) function call. The results are returned as an array. The function returns false when no more rows are available.

The second parameter, result_type, controls whether the returned array can be accessed associatively by attribute name (MYSQL_ASSOC), numerically (MYSQL_NUM), or using both styles (MYSQL_BOTH). The default is MYSQL_BOTH, and changing the parameter won't improve the speed of your code.

The default second parameter to mysql_fetch_array( ) of MYSQL_BOTH works well, except when you plan to print out elements of a row with the foreach loop statement. Because the elements are referenced both numerically and associatively, each element prints out twice. If you plan to use foreach, set the second parameter to MYSQL_ASSOC for associative access or MYSQL_NUM for numeric access, and you'll get only one copy of the data in the array.


When associative access is used, values can be referenced in the array by their table attribute names. Consider an example query on the wine table using the mysql_query( ) function:

$result = mysql_query("SELECT * FROM wine", $connection)

A row can then be retrieved into the array $row using:

$row = mysql_fetch_array($result)

After retrieving the row, elements of the array $row can be accessed by their attribute names in the wine table. For example, print $row["wine_name"] prints the value of the wine_name attribute from the retrieved row. In this example, because the default second parameter is MYSQL_BOTH, you can still access attributes by their element numbers. For example, print $row[1] also works.

If more than one attribute has the same name in a SELECT clause, only the last-listed attribute is available via the associative array, and the other attributes with identical names must be accessed using another approach. The easiest technique is to avoid the problem altogether by making attribute names unique within the database. Another approach is to use numeric access instead, but this leads to hard to maintain, unreadable code. Yet another approach you can use is attribute aliases. Attribute aliases allow you rename an attribute to another name, and this name can be used instead throughout the query and in your PHP code; attribute aliases use the SQL AS clause and are discussed in Chapter 15.

Let's assume you're stuck with a query that has duplicate attribute names. Consider the following PHP fragment that deals with dates in the orders and items tables:

$result = mysql_query("SELECT orders.date AS odate, items.date AS idate,

                 FROM items, orders WHERE items.order_id = orders.order_id 

                 AND items.cust_id = orders.cust_id", $connection);



$row = mysql_fetch_array($result);



print "Order: {$row["cust_id"]}-{$row["order_id"]} ";

print "Created: {$row["odate"]} Item added: {$row["idate"]}\n";

In this example, the orders.date attribute is renamed to odate and items.date is renamed to idate. The new names can then be used to access the row values in the $row array that's returned from mysql_fetch_array( ). If you use attribute aliases, the alias must be used to access the data; access with the original attribute name won't work.

You can also use attribute aliases to alias functions in queries, and with mysql_fetch_array( ) this leads to easier to write and read code when accessing the result data. For example, the following fragment shows how the count of customers of the winestore can be aliased and used:

$result = mysql_query("SELECT count(cust_id) AS custcount FROM customer", 

$connection);



$row = mysql_fetch_array($result);



print "There are {$row["custcount"} customers";


int mysql_insert_id([resource connection])

Returns the AUTO_INCREMENT identifier value associated with the most recently executed SQL INSERT statement. The function returns 0 if the most recent query doesn't use AUTO_INCREMENT. The last connection opened is assumed if the connection resource is omitted. This function is discussed in more detail in Chapter 8.

This function should be called immediately after the insertion of a row and the result saved in a variable, because the function works for a connection and not on a per-query basis. Subsequent queries through the same connection make it impossible to retrieve previous key values using this function.

The mysql_insert_id( ) function doesn't work with the MySQL BIGINT attribute type. If you use BIGINT for an AUTO_INCREMENT attribute, use the MySQL function LAST_INSERT_ID( ) (that's discussed in Chapter 15) in an SQL SELECT statement to discover the value instead.


Consider an example where the AUTO_INCREMENT feature is used on the cust_id attribute of the customer table. The function can be used to find out which cust_id primary key value was assigned after a NULL or 0 was inserted into the attribute during an INSERT INTO customer operation.


int mysql_num_rows(resource result_set)

This function returns the number of rows associated with the result_set query result resource handle. Queries that modify a database should use mysql_affected_rows( ).

The function mysql_num_rows( ) works only for SELECT queries, and it doesn't work with mysql_unbuffered_query( ) until all rows have been retrieved from the result set. Unbuffered querying is discussed later in this section.


If the number of rows in a table is required but not the data itself, it is usually more efficient to run an SQL query of the form SELECT count(*) FROM table and retrieve the result, rather than running SELECT * FROM table and then using mysql_num_rows( ) to determine the number of rows in the table.


resource mysql_pconnect([string host[:port] [, string user [, string password [, int flags]]]])

This function is a performance-oriented alternative to mysql_connect( ) that reuses open connections to the MySQL server. The p in mysql_pconnect( ) stands for persistent, meaning that a connection to the server stays open after a script terminates.

This function opens a connection and returns the same results as its non-persistent sibling mysql_connect( ). It has the same first three optional parameters as mysql_connect( ), and since PHP 4.3, it has a fourth optional parameter that is the same as the fifth parameter of mysql_connect( ). This function, unlike it's non-persistent sibling mysql_connect( ), doesn't offer an argument that lets you force open a new connection.

Open connections are maintained as a pool that is available to PHP. When a call to mysql_pconnect( ) is made, a pooled connection is used in preference to creating a new connection. Using pooled connections saves the costs of opening and closing connections. Whether persistency is faster in practice depends on the server configuration and the application. However, in general, for web database applications with many users running on a server with plenty of main memory, persistency is likely to improve performance.

A connection opened with mysql_pconnect( ) can't be closed with mysql_close( ). It stays open until unused for a period of time. The timeout is a MySQL server parameter, not a PHP parameter, and is set by default to 28800 seconds! It can (and should) be adjusted with a command-line option to the MySQL server script mysqld_safe or by changing the MySQL configuration file. For example, to start your MySQL with the timeout set to a more realistic 10 seconds on the command line, on a Unix system use:

% /usr/local/mysql/bin/mysqld_safe --set-variable interactive_timeout=10

To set the parameter permanently in your global MySQL configuration file, add the following line under the [mysql] heading:

set-variable        = interactive_timeout = 10

If you followed the installation instructions in Appendix A through Appendix C, you'll find the file as /etc/my.cnf on a Unix system or C:\winnt\my.ini under Windows 2000/2003/NT and C:\windows\my.ini under Windows XP.


string mysql_real_escape_string (string query [, resource connection])

Escapes a query string so that it can be used as a parameter to mysql_query( ) or mysql_unbuffered_query( ). The function returns a copy of the input string that has any special characters escaped so that is safe to use in an SQL query. This is useful when querying with user data, or when loading data from an external source; we discuss processing user data later in this chapter.

To carry out the escaping, the function checks the character set associated with the optional connection. If no connection is provided, the most recently opened connection is assumed. As an example, for the ASCII character set, this function escapes single quote, double quote, NULL, carriage return, line feed, and SUB (substitute) characters by inserting a backslash character before them.

This function is available since PHP 4.3. If you're using an older version, use mysql_escape_string( ) which does not support the second parameter (and, therefore, does not take into account the character set of the connection).


resource mysql_query(string SQL [, resource connection [,int mode]])

Runs an SQL statement. The second argument is a connection resource returned from a call to mysql_connect( ). On success, the function never returns a false value. For SELECT, SHOW, EXPLAIN, or DESCRIBE queries, the function returns a query result resource that can be used to fetch data. For other SQL queries, the function returns true on success. The function returns false on failure.

The query string passed to mysql_query( ) doesn't need to be terminated with a semicolon.

If the second parameter to mysql_query( ) is omitted, PHP tries to use any open connection to the MySQL server starting with the most-recently opened. If no connections are open, a call to mysql_connect( ) with no parameters is issued. In practice, the second parameter should be supplied.

The third parameter defaults to MYSQL_STORE_RESULT, and we recommend not changing it. Use the mysql_unbuffered_query( ) function that's discussed later in this section if you don't want query results to be stored.


bool mysql_select_db (string database [, resource connection])

Uses the specified database on a connection. If the second parameter is omitted, the last connection opened is assumed, or an attempt is made to open a connection with mysql_connect( ) and no parameters. We caution against omitting the connection parameter. The function returns true on success and false on failure.


resource mysql_unbuffered_query(string query [, resource connection [, int mode]])

This function starts a query, but returns immediately without retrieving and buffering the whole result set. The parameters and return values are the same as mysql_query( ).

This function is useful for queries that return large result sets or that are slow to execute, as it allows the script to continue with the processing or formatting of data while the query runs. Another advantage is that no resources are required to store a large result set. In contrast, by default, the function mysql_query( ) doesn't return until the query is complete and the results have been buffered.

The third parameter defaults to MYSQL_USE_RESULT, and we recommend not changing it. Use the mysql_query( ) function that's discussed earlier in this section if you want query results to be buffered.

There are four important issues associated with the function:

  • The number of rows produced by the query can't be checked with mysql_num_rows( ) until the total number of rows are known after the query finishes.

  • Specific rows can't be retrieved with mysql_data_seek( ) because data is retrieved sequentially, and it's not possible to seek to a row until it has been retrieved.

  • You must completely process each query on a connection before you run another query. This means you have to retrieve all of the query results using, for example, a while loop, even if you don't need them. A workaround is to use two server connections to run two queries at the same time, or to better design your queries so that they only retrieve the data you really need.

  • A script won't finish until its server connections are no longer active. This behavior confuses new users: the function call will return immediately, but the script won't end and free its resources until all of its queries finish running.

  • The function is otherwise identical to mysql_query( ). It is available in PHP 4.0.6 or later.

6.3.2 Other Functions


string mysql_client_encoding([resource connection])

Returns the name of the character set in use on the connection. If a connection isn't provided, the most recently opened connection is assumed. Available since PHP 4.3.0.


bool mysql_close([resource connection])

Closes a MySQL connection that was opened with mysql_connect( ). The connection parameter is optional. If it is omitted, the most recently opened connection is closed. Returns true on success and false on failure.

The primary use of this function is to save resources when you don't want a connection to stay open while a script runs. Most programs do not need to call this function because they use the connection until shortly before they terminate, and their termination automatically cleans up open connections.

This function has no effect on persistent connections opened with mysql_pconnect( ).


bool mysql_data_seek(resource result, int row)

This function lets you retrieve only selected results from a query, which is useful to reduce processing in an application. For example, executing the function for a result with a row parameter of 10, and then issuing a mysql_fetch_array( ) retrieves the eleventh row of the result set; rows are numbered from zero.

The parameter result is a result resource returned from mysql_query( ). The function returns true on success and false on failure. A common source of failure is that there are no rows in the result set associated with the result resource. A prior call to mysql_num_rows( ) can be used to determine if results were returned from the query.

The mysql_data_seek( ) function cannot be used with mysql_unbuffered_query( ).



object mysql_fetch_field(resource result [, int attribute_number])

Returns the metadata for each attribute associated with a result resource returned from a query function call. An optional attribute_number can be specified to retrieve the metadata associated with a specific attribute. However, repeated calls process the attributes one by one.

The properties of the object returned by the function are:


name

The attribute name


table

The name of the table to which the attribute belongs.


max_length

The maximum length of the attribute.


not_null

Set to 1 if the attribute cannot be NULL.


primary_key

Set to 1 if the attribute forms part of a primary key.


unique_key

Set to 1 if the attribute is a unique key.


multiple_key

Set to 1 if the attribute is a non-unique key.


numeric

Set to 1 if the attribute is a numeric type.


blob

Set to 1 if the attribute is a BLOB type.


type

The type of the attribute.


unsigned

Set to 1 if the attribute is an unsigned numeric type.


zerofill

Set to 1 if the numeric column is zero-filled.


def

The default value of the attribute (if specified).


array mysql_fetch_lengths(qresource query)

Returns an array of attribute lengths associated with the most-recently retrieved row of data. The argument to the function is a query result resource that has been used to retrieve at least one row. The elements of the returned array correspond to the length of the values in the array returned from the most-recent call to mysql_fetch_array( ) or mysql_fetch_object( ). It returns false on error.

This function returns the length of a value within the query results, not the maximum length of an attribute as defined in the database table. Use the function mysql_fetch_field( ) to retrieve the maximum allowed length of an attribute.


object mysql_fetch_object(resource result)

This function is an alternative for returning results from a query. It returns an instance of an object that contains one row of results associated with the result resource, permitting access to values in an object by their table attribute names. It returns false when no more rows are available.

For example, after a query to SELECT * from wine, a row can be retrieved into the object $object using:

$object = mysql_fetch_object($result)

The attributes can then be accessed in $object by their attribute names. For example:

print $object->wine_name

prints the value of the wine_name attribute from the retrieved row.

It's hard work to use objects returned from mysql_fetch_object( ) to access aggregate functions, and sometimes you'll get into trouble with attribute names and corresponding variable name limitations. Also, attributes can't be accessed numerically. However, attribute aliases (which are discussed in Chapter 15) can help in most cases.

In our applications, we exclusively use mysql_fetch_array( ) instead.



bool mysql_free_result(resource result)

This function frees the resources associated with a query result resource. Resources are cleaned-up when a script finishes, so this function is only needed if a script repeatedly queries the server or if several large result sets are buffered. The function returns true on success and false on failure.


string mysql_get_client_info( )

Returns a string that describes the MySQL client library used by PHP. Available since PHP 4.0.5.


string mysql_get_host_info([resource connection])

Returns a string that describes a MySQL server connection. The string contains the type of connection (TCP or Unix socket) and the host name. An optional connection resource handle may be provided as the parameter; otherwise the most recently opened connection is assumed. Available since PHP 4.0.5.


int mysql_get_proto_info([resource connection])

Returns an integer that is the protocol version used in a MySQL server connection. An optional connection resource handle may be provided as the parameter; otherwise the most recently opened connection is assumed. Available since PHP 4.0.5.


string mysql_get_server_info([resource connection])

Returns as a string the version of the MySQL server. An optional connection resource handle may be provided as the parameter, otherwise the most recently opened connection is assumed. Available since PHP 4.0.5.


string mysql_info([resource connection])

Returns a descriptive string such as Records: 20 Duplicates: 0 Warnings: 0 that describes the results of the last INSERT, LOAD DATA INFILE, ALTER TABLE or UPDATE query; the ALTER TABLE and LOAD DATA INFILE statements are discussed in Chapter 15. The string is the same as returned in the MySQL command interpreter after running the query. This is useful to display to database administrators, or can be parsed and used in application logic when mysql_affected_rows( ) doesn't serve the purpose. Available since PHP 4.3.0.


resource mysql_list_processes([resource connection])

Returns a resource that can be used with mysql_fetch_array( ) to retrieve information about active processes running on the database server. An optional connection can be provided or the most recently opened connection is assumed. The data that is returned has the following array keys: Id (process ID), User, Host, db (currently selected database), Command (the currently running command in the process), Time (elapsed run time), State, and Info. Available since PHP 4.3.0.


int mysql_num_fields(resource result_set)

Returns the number of attributes associated with a result set handle result_set. The result set handle is returned from a prior call to mysql_query( ).

In practice, you probably don't need to use this function. If you use mysql_fetch_array( ), the count( ) function gives you the same result.


bool mysql_ping([resource connection])

Checks whether a connection is working. Returns true on success, and false on failure. If a connection isn't provided, the most recently opened connection is assumed. When the connection isn't working, an automatic attempt is made to reestablish the connection.

The function's primary use is checking if a remote connection is still working during a lengthy operation, and trying to recover if it isn't. Most of the time, in simple PHP scripts, you'll be able to detect errors with the MySQL error functions that are discussed in the next section. Available since PHP 4.3.0.


int mysql_thread_id([resource connection])

Returns the current thread or process identifier. An optional connection can be provided, or the last opened connection is assumed. It returns false on failure, although it very rarely fails. Even if a connection is not open, a sensible value is returned?the function will fail only if the MySQL server isn't running. Available since PHP 4.3.0.

6.3.3 Functions to Avoid

Several MySQL functions don't need to be used:

  • The functions of mysql_fetch_field( ) are also available in the non-object-based alternatives mysql_fetch_length( ), mysql_field_flags( ), mysql_field_name( ), mysql_field_len( ), mysql_field_table( ), and mysql_field_type( ); as these functions are almost a complete subset of mysql_fetch_field( ), we don't describe them here and we don't use them in our applications.

  • The function mysql_result( ) is a slower alternative to fetching and processing a row with mysql_fetch_array( ) and shouldn't be used in practice.

  • mysql_fetch_assoc( ) and mysql_fetch_row( ) retrieve one row of results from a query. Each provides half the functionality of mysql_fetch_array( ). Because mysql_fetch_array( ) provides both sets of functionality?or can provide the same functionality by passing through MYSQL_ASSOC or MYSQL_NUM as the second parameter?it can be used instead.

  • mysql_field_seek( ) can seek to a specific field for a subsequent call to mysql_fetch_field( ), but this is redundant because the field number can be supplied directly to mysql_fetch_field( ) as the optional second parameter.

  • mysql_db_query( ) was popular in PHP 3, and combines the functionality of mysql_select_db( ) and mysql_query( ). This function has been deprecated in recent releases of PHP because it is slower than selecting the database once with mysql_select_db( ), and then issuing queries.

  • mysql_change_user( ) is used to change the username associated with an open connection. This function is broken in PHP 4.

  • mysql_escape_string( ) is a deprecated version of mysql_real_escape_string( ) that ignores the current character set for a MySQL connection.

  • mysql_drop_db( ) has been deprecated because it's easy to issue a MySQL DROP DATABASE statement instead.

  • mysql_create_db( ) performs the same function as a MySQL CREATE DATABASE statement.

  • mysql_db_name( ) and mysql_list_dbs( ) perform the same function as a MySQL SHOW DATABASES, and then using mysql_fetch_array( ) to retrieve the database names.

  • mysql_tablename( ) and mysql_list_tables( ) perform the same function as a MySQL SHOW TABLES, and then using mysql_fetch_array( ) to retrieve the table names.

  • mysql_stat( ) performs a subset of the functions of the MySQL SHOW STATUS command.