Functions
The following descriptions discuss each of PHP's MySQL-related functions. Certain parameter names recur throughout the function descriptions and have the following conventional meanings:
Most functions take a conn_id (connection identifier) parameter. This is a resource that indicates a connection to a MySQL server, typically obtained by calling mysql_connect() or mysql_pconnect(). If the connection identifier is optional and is missing from a function call, PHP uses the most recently opened connection. In this case, many functions will try to establish a connection if no connection is specified and there isn't one open.
A result_id parameter indicates a result set identifier. This is a resource containing information about the rows in a query result. Result sets typically are obtained by calling mysql_query(), although other functions create them as well.
row_num and col_num indicate row and column numbers within a result set. Both are numbered starting from 0.
Optional parameters are indicated by square brackets ([]).
Some functions produce an error message if an error occurs, in addition to returning a status value. In Web contexts, this message appears in the output sent to the client browser, which may not be what you want. To suppress the (possibly cryptic) error message a function normally would produce, precede the function name by the @ operator. For example, to suppress the error message from a mysql_connect() call so that you can report failure in a more suitable manner, you might do something like the following:
<?php
$conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret")
or die ("Could not connect\n");
print ("Connected successfully\n");
?>
The examples in this appendix use the @ operator for connect calls and print their own message if a connection error occurs.
Another way to suppress error messages is to use the error_reporting() function:
<?php
error_reporting (0); # suppress all error messages
$conn_id = mysql_connect ("cobra.snake.net", "sampadm", "secret")
or die ("Could not connect\n");
print ("Connected successfully\n");
?>
Many of the example scripts in this appendix print "Connected successfully" after establishing a connection to the MySQL server. The reason they do this is to make sure the script prints some output, in case you try the script for yourself. (A PHP script that runs successfully but produces no output can trigger a "page contains no data" warning in some browsers, which gives the misleading impression that the script failed somehow.)
The examples also print messages and query results as plain text for the most part. This is done to make the scripts easier to read. However, for scripts intended for execution in a Web environment, you generally should encode output with htmlspecialchars() if it may contain characters that are special in HTML, such as '<', '>', or '&'.
In the descriptions that follow, the term "SELECT query" should be taken to mean a SELECT query or any other query that returns rows, such as DESCRIBE, EXPLAIN, or SHOW.
Connection Management Routines
The routines in this section allow you to open and close connections to the MySQL server or to change the current MySQL username.
int mysql_change_user (string user_name, string password [, string db_name [, resource conn_id]]);Changes the user and the default database for the connection specified by conn_id or for the current connection if no conn_id parameter is specified. The database becomes the default for table references that do not include a database specifier. If db_name is not specified, no default database is selected.
mysql_change_user() returns true if the user is allowed to connect to the server and, if a database was specified, has permission to access the database. Otherwise, the function fails and the current user and database remain unchanged.
mysql_change_user() requires MySQL 3.23.3 or later. It was introduced in PHP 3.0.13 but is unavailable in PHP 4.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_change_user ("unknown", "public") or die ("Could not change user\n"); print ("User changed successfully\n"); ?>bool mysql_close ([resource conn_id]);Closes the connection to the MySQL server identified by conn_id. If no connection is specified, mysql_close() closes the most recently opened connection.
mysql_close() returns true for success or false for an error. For persistent connections opened with mysql_pconnect(), mysql_close() returns true but ignores the close request and leaves the connection open. If you are going to close a connection, you should open it using mysql_connect() rather than mysql_pconnect().
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); print ("Connected successfully\n"); mysql_close ($conn_id); ?>It's not required that you close a non-persistent connection explicitly; PHP will close it automatically when the script terminates. On the other hand, if a script will execute for a while after it has finished accessing the MySQL server, closing the connection when you no longer need it is friendlier to the server, which then can reuse the connection for another client more quickly.
resource mysql_connect ([string host_name] [, string user_name [, string password [, new_conn]]]);Opens a connection to the MySQL server on host host_name for user user_name with the given password. Returns the connection identifier associated with the new connection or returns false if an error occurred.
As of PHP 3.0B4, the hostname parameter can be specified with an optional port number in "host_name:port_num" format. As of PHP 3.0.10, if the hostname is "localhost" the hostname parameter can be specified with an optional pathname to specify the UNIX domain socket path, in "localhost:socket_name" form. The socket should be specified as a full pathname. To connect to the local host but use a TCP/IP connection rather than a UNIX socket, specify a hostname value of "127.0.0.1".
If the hostname parameter is missing, the default is "localhost"., If the username parameter is missing or empty, the default is the username that PHP is running as. (If PHP is running as an Apache module, this is the username for the account the Web server is running as. If PHP is executing as a standalone program, this is the name of the user running the PHP script.) If the password parameter is missing or empty, the empty password is sent.
In PHP 4, you can change the default values that are used for missing connection parameter arguments by specifying configuration directives in the PHP initialization file, php.ini. The directives are listed in Table H.1.
Table H.1. PHP 4 Configuration Directives for Setting Default Connection Parameters
Connection Parameter Configuration Directive Hostname mysql.default_host Username mysql.default_user Password mysql.default_password Port number mysql.default_port Socket pathname mysql.default_socket While the connection is open, if mysql_connect() is called with the same connection parameters (hostname, username, password), no new connection is generated; mysql_connect() returns the existing connection identifier instead. As of PHP 4.2.0, you can override this behavior and force mysql_connect() to open a new connection by passing a true value for the new_conn parameter.
The connection can be closed by calling mysql_close(). If the connection is open when the script terminates, the connection is closed automatically.
PHP functions that take an optional connection identifier argument use the most recently open connection if the argument is omitted. If no connection is open, several functions will attempt to open one. These include functions that issue queries, functions that select, create, or drop databases, and functions that retrieve information about the connection. In all such cases, the result is as though mysql_connect() is called with no arguments, thus using the default connection parameters. Should the implicit connection attempt fail, the calling function fails as well.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); print ("Connected successfully\n"); mysql_close ($conn_id); ?>resource mysql_pconnect ([string host_name] [, string user_name [, string password]]);mysql_pconnect() is similar to mysql_connect() except that it opens a persistent connection. That is, the connection stays open when the script terminates. If another call is made to mysql_pconnect() with the same connection parameters (hostname, username, password) while the connection is open, the connection will be reused. This avoids the overhead of tearing down and reopening connections and is more efficient than non-persistent connections.
Persistent connections only make sense when PHP is executing as a module within a Web server process that continues to run after the PHP script terminates. (The connection can be reused when the server receives another request for a PHP script that connects to MySQL with the same parameters.) In a script executed by a standalone version of PHP, the connection is closed when the script terminates because the PHP process terminates as well.
Calling mysql_close() on a persistent connection is nonsensical; if you do this, mysql_close() returns true but leaves the connection open.
<?php $conn_id = @mysql_pconnect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); print ("Connected successfully\n"); ?>bool mysql_ping ([resource conn_id]);Re-establishes the connection to the server if the connection has timed out. Returns true if the connection was still active or was re-established successfully, false otherwise.
mysql_ping() was introduced in PHP 4.3.0.
Error-Reporting Routines
The mysql_errno() and mysql_error() functions return error number and message information for MySQL-related PHP functions. However, prior to PHP 4.0.6, no error information is available from either function without a valid connection identifier. This means that they are not useful for reporting the result of failed mysql_connect() or mysql_pconnect() calls because no such identifier is available until a connection has been established successfully. If you want to get the MySQL error message for failed connection attempts under these circumstances, enable the track_errors variable with a directive in the PHP initialization file:
track_errors = On;
Then restart your Web server if you're running PHP as an Apache module. After you've done that, you can obtain an error string for failed connection attempts by referring to the $php_errormsg variable:
<?php
$conn_id = @mysql_connect("badhost", "baduser", "badpass")
or die ("Could not connect: $php_errormsg\n");
print ("Connected successfully\n");
?>
You can adopt a combined approach that uses the error functions if they return information for a failed connection attempt and falls back to $php_errormsg if they do not. The following example shows one way to do this. If mysql_error() returns non-zero, that means the error routines can be used. Otherwise, the code uses $php_errormsg to get the error message:
<?php
if (!($conn_id = @mysql_connect("badhost", "baduser", "badpass")))
{
# mysql_errno() returns non-zero if it works for connect errors
if (mysql_errno ())
{
$msg = sprintf ("Could not connect: %s (%d)\n",
mysql_error (), mysql_errno ());
}
else # fall back to $php_errormsg
{
$msg = "Could not connect: $php_errormsg\n";
}
die ($msg);
}
print ("Connected successfully\n");
?>
int mysql_errno ([resource conn_id]);For the given connection (or the current connection if none is speci- fied), returns the error number for the MySQL-related function that most recently returned a status. A value of zero means no error occurred.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect, error=" . mysql_errno () . "\n"); print ("Connected successfully\n"); mysql_select_db ("sampdb") or die ("Could not select database, error=" . mysql_errno () . "\n"); $query = "SELECT * FROM president"; $result_id = mysql_query ($query) or die ("query failed, error=" . mysql_errno () . "\n"); mysql_free_result ($result_id); ?>string mysql_error ([resource conn_id]);For the given connection (or the current connection if none is specified), returns a string containing the error message for the MySQL-related function that most recently returned a status. An empty string means no error occurred.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect, error=" . mysql_error () . "\n"); print ("Connected successfully\n"); mysql_select_db ("sampdb") or die ("Could not select database, error=" . mysql_error () . "\n"); $query = "SELECT * FROM president"; $result_id = mysql_query ($query) or die ("query failed, error=" . mysql_error () . "\n"); mysql_free_result ($result_id); ?>
Query Construction and Execution Routines
The routines in this section are used to issue queries to the MySQL server. Each query string must consist of a single SQL statement, and should not end with a semicolon character (';') or a \g sequence. ';' and \g are conventions of the mysql client program and are not used when issuing queries through PHP.
string mysql_escape_string (string str);Escapes any quotes and other special characters in the str argument by preceding each with a backslash, returning the result as a new string. This is used to make data values safe for insertion into SQL queries. PHP 4.3.0 and later supports a mysql_real_escape_string() function as well, which is similar to mysql_escape_string() but takes a connection identifier argument and takes into account the current character set when performing encoding. See the entry for mysql_real_escape_string() elsewhere in this appendix.
The list of the characters escaped by these functions is given in the description for the mysql_real_escape_string() function described in Appendix F, "C API Reference."
mysql_escape_string() was introduced in PHP 4.0.3. Prior to that, you can use addslashes() to achieve much the same result.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $last_name = mysql_escape_string ("O'Malley"); $first_name = mysql_escape_string ("Brian"); $query = "INSERT INTO member (last_name,first_name,expiration)" . " VALUES('$last_name','$first_name','2002-6-3')"; $result_id = mysql_query ($query) or die ("Query failed\n"); printf ("membership number for new member: %d\n", mysql_insert_id()); ?>resource mysql_list_dbs ([resource conn_id]);Returns a result identifier for a result set consisting of the names of the databases the server knows about, one database name per row of the result set. Returns false if an error occurred. No default database need be selected before calling this routine. The result set can be processed by any of the usual row-fetching functions or by mysql_db_name(). For any such function that takes a row number parameter, the number should be in the range from 0 to mysql_num_rows()?1. The result identifier can be passed to mysql_free_result() to free any resources associated with it.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); $result_id = mysql_list_dbs () or die ("Query failed\n"); print ("Databases (using mysql_fetch_row()):<br />\n"); while ($row = mysql_fetch_row ($result_id)) printf ("%s<br />\n", $row[0]); mysql_free_result ($result_id); $result_id = mysql_list_dbs () or die ("Query failed\n"); print ("Databases (using mysql_db_name()):<br />\n"); for ($i = 0; $i < mysql_num_rows ($result_id); $i++) printf ("%s<br />\n", mysql_db_name ($result_id, $i)); mysql_free_result ($result_id); ?>resource mysql_list_fields (string db_name, string tbl_name [, resource conn_id]);Returns a result identifier for a result set containing information about the columns in a table or false if an error occurred. No default database need be selected before calling this routine. The db_name and tbl_name parameters identify the database and table in which you're interested. The result identifier can be used with the functions mysql_field_flags()mysql_field_len(), mysql_field_name(), and mysql_field_type()., The result identifier can be passed to mysql_free_result() to free any resources associated with it.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); $result_id = mysql_list_fields ("sampdb", "member") or die ("Query failed\n"); print ("member table column information:<br />\n"); for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { printf ("column %d:", $i); printf (" name %s,", mysql_field_name ($result_id, $i)); printf (" len %d,", mysql_field_len ($result_id, $i)); printf (" type %s,", mysql_field_type ($result_id, $i)); printf (" flags %s\n", mysql_field_flags ($result_id, $i)); print ("<br />\n"); } mysql_free_result ($result_id); ?>resource mysql_list_processes ([resource conn_id]);Returns a result identifier for a result set containing information about the processes currently running in the server or false if an error occurred. If you have the PROCESS privilege, the list contains all server processes. If you do not, the list contains only your own processes.
The result set identifier can be passed to any of the usual row-fetching functions and to mysql_free_result() to free any resources associated with the result set.
mysql_list_processes() was introduced in PHP 4.3.0.
resource mysql_list_tables (string db_name [, resource conn_id]);Returns a result identifier for a result set consisting of the names of the tables in the given database name, one table name per row of the result set. Returns false if an error occurred. No default database need be selected before calling this routine. The result set can be processed by any of the usual row-fetching functions or by mysql_tablename(). For any such function that takes a row number parameter, the number should be in the range from 0 to mysql_num_rows()?1. The result identifier can be passed to mysql_free_result() to free any resources associated with it.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); $result_id = mysql_list_tables ("sampdb") or die ("Query failed\n"); print ("sampdb tables (using mysql_fetch_row()):<br />\n"); while ($row = mysql_fetch_row ($result_id)) printf ("%s<br />\n", $row[0]); mysql_free_result ($result_id); $result_id = mysql_list_tables ("sampdb") or die ("Query failed\n"); print ("sampdb tables (using mysql_tablename()):<br />\n"); for ($i = 0; $i < mysql_num_rows ($result_id); $i++) printf ("%s<br />\n", mysql_tablename ($result_id, $i)); mysql_free_result ($result_id); ?>int mysql_query (string query [, resource conn_id [, int fetch_mode]]);Sends the query string to the MySQL server to be executed. For DELETEINSERT, REPLACE, and UPDATE statements, mysql_query() returns true for success and false if an error occurred. For a successful query, you can call mysql_affected_rows() to find out how many rows were modified.
As of PHP 4.2.0, an optional fetch_mode parameter can be given to indicate whether mysql_query() should fetch and buffer the result set immediately or defer row fetching until later. The possible fetch_mode values are MYSQL_STORE_RESULT (buffered) and MYSQL_USE_RESULT (unbuffered), with the default being MYSQL_STORE_RESULT., Using MYSQL_USE_RESULT causes mysql_query() to act like mysql_unbuffered_query(); see the description of that function for more information.
For SELECT statements, mysql_query() returns a positive result set identifier for success and false if an error occurred. For a successful query, the result identifier can be used with the various result set processing functions that take a result_id argument. The identifier can be passed to mysql_free_result() to free any resources associated with the result set.
A "successful" query is one that executes without error, but success implies nothing about whether the query returns any rows. The following query is perfectly legal but returns no rows:
SELECT * FROM president WHERE 1 = 0
A query can fail for any of several reasons. For example, it may be syntactically malformed, semantically invalid, or illegal because you don't have permission to access the tables named in the query.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); print ("Connected successfully\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT * FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); mysql_free_result ($result_id); ?>string mysql_real_escape_string (string str [, resource conn_id]);Escapes any quotes and other special characters in the str argument by preceding each with a backslash, returning the result as a new string. This is used to make data values safe for insertion into SQL queries. mysql_real_escape_string() differs from the mysql_escape_string() function in that it takes a connection identifier argument and takes into account the current character set when performing encoding. mysql_escape_string() does not. See the entry for mysql_escape_string() elsewhere in this appendix.
The list of the characters escaped by these functions is given in the description for the mysql_real_escape_string() function described in Appendix F.
mysql_real_escape_string() requires MySQL 3.23.14 or later. It was introduced in PHP 4.3.0. Prior to that, you can use mysql_escape_string() or addslashes() to achieve much the same result.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $last_name = mysql_real_escape_string ("O'Malley"); $first_name = mysql_real_escape_string ("Brian"); $query = "INSERT INTO member (last_name,first_name,expiration)" . " VALUES('$last_name','$first_name','2002-6-3')"; $result_id = mysql_query ($query) or die ("Query failed\n"); printf ("membership number for new member: %d\n", mysql_insert_id()); ?>bool mysql_select_db (string db_name [, resource conn_id]);Selects the given database to make it the default database for the given connection. Tables referenced in subsequent queries are assumed to be in that database unless they are explicitly qualified with a database name. Returns true for success and false if an error occurred.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); print ("Database selected successfully\n"); ?>int mysql_unbuffered_query (string query [, resource conn_id [, int fetch_mode]]);mysql_unbuffered_query() is similar to mysql_query() except that by default it does not immediately fetch the rows of the result set into memory. The differences between the two functions correspond to the differences between the C API functions mysql_store_result() and mysql_use_result(). See Appendix F for a comparison of the tradeoffs between those two functions and a description of when each is appropriate. The same considerations apply to the PHP functions mysql_query() and mysql_unbuffered_query(). In particular, some PHP functions work properly only for queries issued in buffered mode. These include mysql_data_seek(), mysql_num_rows(), and mysql_result() and its aliases.
As of PHP 4.2.0, an optional fetch_mode parameter can be given to indicate whether mysql_unbuffered_query() should fetch and buffer the result set immediately or defer row fetching until later. The possible fetch_mode values are MYSQL_STORE_RESULT (buffered) and MYSQL_USE_RESULT (unbuffered), with the default being MYSQL_USE_RESULT. In unbuffered fetch mode, you must fetch all the rows returned by the query before issuing another query. The result identifier can be passed to mysql_free_result() to free any resources associated with the result set. This will automatically retrieve and discard any unfetched rows for you.
mysql_unbuffered_query() is available as of PHP 4.0.6.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT first_name, last_name FROM member"; $result_id = mysql_unbuffered_query ($query) or die ("Query failed\n"); while (list ($first_name, $last_name) = mysql_fetch_row ($result_id)) print ("$first_name $last_name\n"); mysql_free_result ($result_id); ?>
Result Set Processing Routines
The routines in this section are used to retrieve the results of queries. They also provide access to information about the result, such as how many rows were affected or the metadata for result set columns.
int mysql_affected_rows ([resource conn_id]);Returns the number of rows affected (modified) by the most recent DELETE, INSERT, REPLACE, or UPDATE statement on the given connection. mysql_affected_rows() returns 0 if no rows were changed and ?1 if an error occurred.
After a SELECT query, mysql_affected_rows() returns the number of rows selected. However, normally you should use mysql_num_rows() with SELECT statements.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "INSERT INTO member (last_name,first_name,expiration)" . " VALUES('Brown','Marcia','2002-6-3')"; $result_id = mysql_query ($query) or die ("Query failed\n"); $count = mysql_affected_rows (); printf ("%d row%s inserted\n", $count, $count == 1 ? "" : "s"); ?>bool mysql_data_seek (resource result_id, int row_num);Each result set returned by a SELECT query has an internal row cursor to indicate which row should be returned by the next call to the row-fetching functions mysql_fetch_array()mysql_fetch_assoc(), mysql_fetch_object(), or mysql_fetch_row()., Call mysql_data_seek() to set the cursor for a result set to the given row. The row number should be in the range from 0 to mysql_num_rows()?1. mysql_data_seek() returns true if the row number is legal and false otherwise.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT last_name, first_name FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); # fetch rows in reverse order for ($i = mysql_num_rows ($result_id) - 1; $i >= 0; $i--) { if (!mysql_data_seek ($result_id, $i)) { printf ("Cannot seek to row %d\n", $i); continue; } if(!($row = mysql_fetch_object ($result_id))) continue; printf ("%s %s<br />\n", $row->last_name, $row->first_name); } mysql_free_result ($result_id); ?>string mysql_db_name (resource result_id, int row_num [, mixed field]);Given a result identifier returned by mysql_list_dbs() and a row number within the result, mysql_db_name() returns the database name stored in the given row of the set, or it returns false if an error occurred. The row number should be in the range from 0 to mysql_num_rows()?1.
mysql_db_name() was introduced in PHP 3.0.6. It is actually an alias for mysql_result().
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); $result_id = mysql_list_dbs () or die ("Query failed\n"); print ("Databases:<br />\n"); for ($i = 0; $i < mysql_num_rows ($result_id); $i++) printf ("%s<br />\n", mysql_db_name ($result_id, $i)); mysql_free_result ($result_id); ?>array mysql_fetch_array (resource result_id [, int result_type]);Returns the next row of the given result set as an array or false if there are no more rows. The array contains values stored both by numeric column indices (beginning at 0) and associatively keyed by column names. In other words, each column value can be accessed using either its numeric column index or its name. Associative indices are case sensitive and must be given in the same case that was used to name columns in the query. Suppose you issue the following query:
SELECT last_name, first_name FROM president
If you fetch rows from the result set into an array named $row array elements can be accessed as follows:
$row[0] Holds last_name value $row[1] Holds first_name value $row["last_name"] Holds last_name value $row["first_name"] Holds first_name value
Keys are not qualified by the table names of the corresponding columns, so if you select columns with the same name from different tables, a name clash results. Precedence is given to the column named last in the list of columns selected by the query. To access the hidden column, use its numeric index or rewrite the query to provide a unique alias for the column.
Prior to PHP 3.0.7, mysql_fetch_array() always returns values both by numeric and associative indices. As of PHP 3.0.7, a result_type parameter can be given to indicate what kinds of values to return. result_type should be MYSQL_ASSOC (return values by name indices only), MYSQL_NUM (return values by numeric indices only), or MYSQL_BOTH (return values by both types of indices). The default if result_type is missing is MYSQL_BOTH., Note that calling mysql_fetch_array() with a result_type of MYSQL_ASSOC or MYSQL_NUM is equivalent to calling mysql_fetch_assoc() or mysql_fetch_row().
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT last_name, first_name FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); while ($row = mysql_fetch_array ($result_id)) { # print each name twice, once using numeric indices, # once using associative (name) indices printf ("%s %s<br />\n", $row[0], $row[1]); printf ("%s %s<br />\n", $row["last_name"], $row["first_name"]); } mysql_free_result ($result_id); ?>array mysql_fetch_assoc (resource result_id);Returns the next row of the given result set as an associative array or false if there are no more rows. Column values can be accessed using associative indices corresponding to the names of the columns selected by the query from which the result set was generated.
Calling mysql_fetch_assoc() is the same as calling mysql_fetch_array() with a second argument of MYSQL_ASSOC. Column values with numeric indices are not returned.
mysql_fetch_assoc() was introduced in PHP 4.0.3.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT last_name, first_name FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); while ($row = mysql_fetch_assoc ($result_id)) { printf ("%s %s<br />\n", $row["last_name"], $row["first_name"]); } mysql_free_result ($result_id); ?>object mysql_fetch_field (resource result_id [, int col_num]);Returns metadata information about the given column in the result set or false if there is no such column. If col_num is omitted, successive calls to mysql_fetch_field() return information about successive columns of the result set. The return value is false if no more columns remain. If col_num is specified, it should be in the range from 0 to mysql_num_fields()?1 In this case, mysql_fetch_field() returns information about the given column, or it returns false if col_num is out of range.
The information is returned as an object that has the properties shown in Table H.2.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); $result_id = mysql_list_fields ("sampdb", "president") or die ("Query failed\n"); print ("Table: sampdb.president<br />\n"); # get column metadata for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { printf ("Information for column %d:<br />\n", $i); $meta = mysql_fetch_field ($result_id); if (!$meta) { print ("No information available<br />\n"); continue; } print ("<pre>\n"); printf ("blob: %s\n", $meta->blob); printf ("def: %s\n", $meta->def); printf ("max_length: %s\n", $meta->max_length); printf ("multiple_key: %s\n", $meta->multiple_key); printf ("name: %s\n", $meta->name); printf ("not_null: %s\n", $meta->not_null); printf ("numeric: %s\n", $meta->numeric); printf ("primary_key: %s\n", $meta->primary_key); printf ("table: %s\n", $meta->table); printf ("type: %s\n", $meta->type); printf ("unique_key: %s\n", $meta->unique_key); printf ("unsigned: %s\n", $meta->unsigned); printf ("zerofill: %s\n", $meta->zerofill); print ("</pre>\n"); } mysql_free_result ($result_id); ?>Table H.2. mysql_fetch_field() Properties.
Property Meaning blob 1 if the column is a BLOB (or TEXT) type, 0 otherwise def Default value of column max_length The length of the largest column value in the result set multiple_key 1 if the column is a part of a non-unique index, 0 otherwise name The column name not_null 1 if the column cannot contain NULL values, 0 otherwise numeric 1 if the column has a numeric type, 0 otherwise primary_key 1 if the column is a part of a PRIMARY KEY, 0 otherwise table The name of the table containing the column (empty for calculated columns) type The name of the type of the column unique_key 1 if the column is a part of a UNIQUE index, 0 otherwise unsigned 1 if the column has the UNSIGNED attribute, 0 otherwise zerofill 1 if the column has the ZEROFILL attribute, 0 otherwise The def member of the object returned by mysql_fetch_field() is valid only if the result set was obtained by calling the mysql_list_fields() function. Otherwise, it will be the empty string.
The type member will have one of the values listed in Table H.3. For a column calculated from an expression, the type value will reflect the expression type. A type value of "unknown" most likely indicates that the MySQL server is newer than the version of the MySQL client library PHP is using and that the server knows about a new type the library doesn't recognize.
Table H.3. mysql_fetch_field() type Member Values
Value Type of Column blob BLOB (or TEXT) column date DATE column datetime DATETIME column int Integer numeric column null Column containing only NULL values real Floating-point numeric column string String column other than BLOB or TEXT time TIME columns timestamp TIMESTAMP column unknown Unknown column type year YEAR column array mysql_fetch_lengths (resource result_id);Returns an array containing the lengths of the column values in the row most recently fetched by any of the functions mysql_fetch_array()mysql_fetch_assoc(), mysql_fetch_object(), or mysql_fetch_row()., Array element indices range from 0 to mysql_num_fields()?1. Returns false if no row has yet been fetched or if an error occurred.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT * FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); $row_num = 0; while (mysql_fetch_row ($result_id)) { ++$row_num; # get lengths of column values printf ("Lengths of values in row %d:<br />\n", $row_num); $len = mysql_fetch_lengths ($result_id); if (!$len) { print ("No information available<br />\n"); break; } print ("<pre>\n"); for ($i = 0; $i < mysql_num_fields ($result_id); $i++) printf ("column %d: %s\n", $i, $len[$i]); print ("</pre>\n"); } mysql_free_result ($result_id); ?>object mysql_fetch_object (resource result_id [, int result_type]);Returns the next row of the given result set as an object or false if there are no more rows. Column values can be accessed as properties of the object. The property names are the names of the columns selected by the query from which the result set was generated.
The result_type parameter can be MYSQL_ASSOC (return values by name indices only), MYSQL_NUM (return values by numeric indices only), or MYSQL_BOTH (return values by both types of indices). The default if result_type is missing is MYSQL_BOTH. Numbers are not legal property names, so you can access values by the numeric indices only by playing tricks, such as treating the object as an array.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT last_name, first_name FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); while ($row = mysql_fetch_object ($result_id)) printf ("%s %s<br />\n", $row->last_name, $row->first_name); mysql_free_result ($result_id); ?>array mysql_fetch_row (resource result_id);Returns the next row of the given result set as an array or false if there are no more rows. Column values can be accessed as array elements, using column indices in the range from 0 to mysql_num_fields()?1
Calling mysql_fetch_row() is the same as calling mysql_fetch_array() with a second argument of MYSQL_NUM.. Column values with associative indices are not returned.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT last_name, first_name FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); while ($row = mysql_fetch_row ($result_id)) printf ("%s %s<br />\n", $row[0], $row[1]); mysql_free_result ($result_id); ?>string mysql_field_flags (resource result_id, int col_num);Returns metadata information about the given column in the result set as a string or false if an error occurred. The string consists of space-separated words indicating which of a column's flag values are true For flags that are false, the corresponding word is not present in the string. Table H.4 lists the words that can be present in the string. col_num should be in the range from 0 to mysql_num_fields()?1.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT * FROM member"; $result_id = mysql_query ($query) or die ("Query failed\n"); for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { printf ("column %d:", $i); printf (" name %s,", mysql_field_name ($result_id, $i)); printf (" flags %s\n", mysql_field_flags ($result_id, $i)); print ("<br />\n"); } mysql_free_result ($result_id); ?>Table H.4. mysql_field_flags() Values.
Property Meaning auto_increment Column has the AUTO_INCREMENT attribute binary Column has the BINARY attribute blob Column is a BLOB or TEXT type enum Column is an ENUM multiple_key Column is a part of a non-unique index not_null Column cannot contain NULL values primary_key Column is a part of a PRIMARY KEY timestamp Column is a TIMESTAMP unique_key Column is a part of a UNIQUE index unsigned Column has the UNSIGNED attribute zerofill Column has the ZEROFILL attribute The "binary" property is set for case sensitive string columns. This includes columns for which the BINARY keyword is specified explicitly (such as CHAR BINARY), as well as BLOB columns.
To access the individual words of a string returned by mysql_field_flags(), split it with explode():
$words = explode (" ", mysql_field_flags ($result_id, $i));int mysql_field_len (resource result_id, int col_num);Returns the maximum possible length of values in the given column of the result set. col_num should be in the range from 0 to mysql_num_fields()?1.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT * FROM member"; $result_id = mysql_query ($query) or die ("Query failed\n"); for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { printf ("column %d:", $i); printf (" name %s,", mysql_field_name ($result_id, $i)); printf (" len %d\n", mysql_field_len ($result_id, $i)); print ("<br />\n"); } mysql_free_result ($result_id); ?>string mysql_field_name (resource result_id, int col_num);Returns the name of the given column of the result set. col_num should be in the range from 0 to mysql_num_fields()?1.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT * FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); # get column names for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { printf ("Name of column %d: ", $i); $name = mysql_field_name ($result_id, $i); if (!$name) print ("No name available<br />\n"); else print ("$name<br />\n"); } mysql_free_result ($result_id); ?>int mysql_field_seek (resource result_id, int col_num);Sets the index for subsequent calls to mysql_fetch_field(). The next call to mysql_fetch_field() that is issued without an explicit column number will return information for column col_num. Returns true if the seek succeeds and false otherwise. col_num should be in the range from 0 to mysql_num_fields()?1.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT * FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); # get column metadata for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { printf ("Information for column %d:<br />\n", $i); if (!mysql_field_seek ($result_id, $i)) { print ("Cannot seek to column<br />\n"); continue; } $meta = mysql_fetch_field ($result_id, $i); if (!$meta) { print ("No information available<br />\n"); continue; } print ("<pre>\n"); printf ("blob: %s\n", $meta->blob); printf ("max_length: %s\n", $meta->max_length); printf ("multiple_key: %s\n", $meta->multiple_key); printf ("name: %s\n", $meta->name); printf ("not_null: %s\n", $meta->not_null); printf ("numeric: %s\n", $meta->numeric); printf ("primary_key: %s\n", $meta->primary_key); printf ("table: %s\n", $meta->table); printf ("type: %s\n", $meta->type); printf ("unique_key: %s\n", $meta->unique_key); printf ("unsigned: %s\n", $meta->unsigned); printf ("zerofill: %s\n", $meta->zerofill); print ("</pre>\n"); } mysql_free_result ($result_id); ?>string mysql_field_table (resource result_id, int col_num);Returns the name of the table that contains the given column of the result set. For columns that are calculated as the result of an expression, the name is empty. col_num should be in the range from 0 to mysql_num_fields()?1.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT * FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { printf ("column %d:", $i); printf (" name %s,", mysql_field_name ($result_id, $i)); printf (" table %s\n", mysql_field_table ($result_id, $i)); print ("<br />\n"); } mysql_free_result ($result_id); ?>string mysql_field_type (resource result_id, int col_num);Returns the type name for the given column of the result set. The return value will be one of those described for the type value returned by the mysql_field_flags() function; see the description of that function for a list of these values. col_num should be in the range from 0 to mysql_num_fields()?1.
<?php $conn_id = @mysql_connect ("cobra.snake.net", "sampadm", "secret") or die ("Could not connect\n"); mysql_select_db ("sampdb") or die ("Could not select database\n"); $query = "SELECT * FROM president"; $result_id = mysql_query ($query) or die ("Query failed\n"); for ($i = 0; $i < mysql_num_fields ($result_id); $i++) { printf ("column %d:", $i); printf (" name %s,", mysql_field_name ($result_id, $i)); printf (" type %s\n", mysql_field_type ($result_id, $i)); print ("<br />\n"); } mysql_free_result ($result_id); ?>bool mysql_free_result (resource result_id);Frees any resources associated with the given result set. Result sets are returned by mysql_query(), mysql_unbuffered_query(), mysql_list_dbs(), mysql_list_fields(), mysql_list_processes(), and mysql_list_tables().
Result sets are freed automatically when a script terminates, but you may want to call this function explicitly in a script that generates many result sets. For example, executing the following loop in a PHP script will use a considerable amount of memory:
<?php for ($i = 0; $i < 10000; $i++) { $result_id = mysql_query ("SELECT * from president"); } ?>Adding a mysql_free_result() call after the mysql_query() call will reduce the amount of result set memory used to almost nothing:
<?php for ($i = 0; $i < 10000; $i++) { $result_id = mysql_query ("SELECT * from president"); mysql_free_result ($result_id); } ?>You should also call mysql_free_result() explicitly if you terminate your fetch loop early after issuing a query with mysql_unbuffered_query(). mysql_free_result() will take care of fetching and discarding the remaining rows automatically. If you fail to do this, an "out of sync" error will occur when you issue the next query.
int mysql_insert_id ([resource conn_id]);Returns
