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