Functions

The following descriptions discuss each of PHP's MySQL-related functions. Optional parameters are indicated by square brackets ([]).

Many functions take an optional link_id (link identifier) parameter that indicates a connection to a MySQL server. If the link identifier is missing from a function call, the most recently opened connection is used. Some 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, typically returned by mysql_db_query() or mysql_query().

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. A function name may be preceded by '@' to suppress the (possibly cryptic) error message the function would normally produce. For example, to suppress the error message from a mysql_pconnect() call so that you can report failure in a more suitable manner, you might do something like this:

<?php
    $link = @mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
or die ("Could not connect");
print ("Connected successfully");
?>

Another way to suppress error messages is to use the error_reporting() function:

<?php
    error_reporting (0); # suppress all error messages
$link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
or die ("Could not connect");
print ("Connected successfully");
?>

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 produces no output triggers a "page contains no data" warning in some browsers.

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.

• int

  
  mysql_close ([int link_id]);
  

  Closes the connection to the MySQL server identified by link_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() ignores the close request but returns true. If you are going to close a connection, you should open it using mysql_connect() rather than mysql_pconnect().

  
  <?php
      $link = mysql_connect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      print ("Connected successfully");
  mysql_close ($link);
  ?>
  

• int

  
  mysql_connect ([string host_name] [, string user_name [, string password]]);
  

  Opens a connection to the MySQL server on host host_name for user user_name with the given password. Returns the link identifier (a positivenumber) associated with the new connection; returns false if an error occurred.

  As of PHP 3.0B4, the hostname parameter may be specified with an optional port number, in "host_name:port_num" form. As of PHP 3.0.10, if the hostname is localhost, the hostname parameter may 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.

  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. (This is the name of the user the Web server is running as if PHP is running as an Apache module, or the name of the user PHP is running as if PHP is running as a standalone program.) If the password parameter is missing or empty, the empty password is sent.

  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 link identifier instead.

  The connection may be closed by calling mysql_close(). If the connection is open when the script terminates, the connection is closed automatically.

  
  <?php
      $link = mysql_connect ("pit-viper.snake.net", "paul", "secret")
           or die ("Could not connect");
  print ("Connected successfully");
  mysql_close ($link);
  ?>
  

• int

  
  mysql_pconnect ([string host_name] [, string user_name [, string password]]);
  

  mysql_pconnect() is like 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 that continues to run after the PHP script terminates. 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; in this case, mysql_close() returns true but leaves the connection open.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
  or die ("Could not connect");
  print ("Connected successfully");
  ?>
  

Status- and Error-Reporting Routines

The mysql_errno() and mysql_error() functions return error number and message information for MySQL-related PHP functions. However, no error information is available from either function without a valid link identifier. This means that they are not useful for reporting the result of failed mysql_connect() or mysql_pconnect() calls because no valid link identifier is available until a connection has been established successfully. If you want to get the MySQL error message for failed connection attempts, 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 the error string for failed connection attempts by referring to the $php_errormsg variable:

<?php
    $link = @mysql_connect("badhost","baduser", "badpass")
        or die ("Could not connect: " . $php_errormsg);
   print ("Connected successfully");
?>

• int

  
  mysql_errno ([int link_id]);
  

  For the given connection, returns the error number for the MySQL-related function that most recently returned a status. A value of zero means no error occurred.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
  print ("Connected successfully");
  $query = "SELECT * FROM president";
  $result = mysql_query ($query)
      or die (query failed, error code = " . mysql_errno ());
  ?>
  

• string

  
  mysql_error ([int link_id]);
  

  For the given connection, returns a string containing the error message for MySQL-related function that most recently returned a status. An empty string means no error occurred.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      print ("Connected successfully");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
          or die ("query failed, error message = " . mysql_error ());
  ?>
  

Query Construction and Execution Routines

The routines in this section are used to issue queries to the MySQL server.

• int

  
  mysql_db_query (string db_name, string query [, int link_id]);
  

  mysql_db_query() is like mysql_query() except that it takes an additional database name argument and makes it the default database before executing the query. (Compare the following example to the example for mysql_query().)

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
  print ("Connected successfully");
  $query = "SELECT * FROM president";
  $result = mysql_db_query (samp_db, $query)
      or die ("Query failed");
  ?>
  

• int

  
  mysql_list_dbs ([int link_id]);
  

  Returns a result identifier for a result set consisting of the names of the databases the server knows about on the given connection, one database name per row of the result set. Returns false if an error occurs. A default database does not need to be selected. The result set may be processed by any of the usual row-fetching functions or by mysql_tablename().

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      $result = mysql_list_dbs ()
  or die ("Query failed");
      print ("Databases (using mysql_fetch_row()):<BR>
  ");
      while ($row = mysql_fetch_row ($result))
       printf ("%s<BR>
  ", $row[0]);
      $result = mysql_list_dbs ()or die ("Query failed");
  print ("Databases (using mysql_tablename()):<BR>
  ");
      for ($i = 0; $i < mysql_num_rows ($result); $i++)
          printf ("%s<BR>
  ", mysql_tablename ($result, $i));
  ?>
  

• int

  
  mysql_list_fields (string db_name, string tbl_name [, int link_id]);
  

  Returns a result identifier for a result set containing information about the columns in a table; returns false if an error occurs. A default database does not need to be selected. The db_nameand 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().

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
         or die ("Could not connect");
  $result = mysql_list_fields ("samp_db", "member")
  or die ("Query failed");
  print ("member table column information:<BR>
  ");
  for ($i = 0; $i < mysql_num_fields ($result); $i++)
  {
  printf ("column %d:", $i);
  printf (" name %s,
  ", mysql_field_name ($result, $i));
  printf (" len %d,
  ", mysql_field_len ($result, $i));
  printf (" type %s,
  ", mysql_field_type ($result, $i));
  printf (" flags %s
  ", mysql_field_flags ($result, $i));
  print "<BR>
  ";
  }
  
  ?>
  

• int

  
  mysql_list_tables (string db_name [, int link_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 occurs. A default database does not need to be selected. The result set may be processed by any of the usual row-fetching functions or by mysql_tablename().

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
  or die ("Could not connect");
  $result = mysql_list_tables ("samp_db")
  or die ("Query failed");
  print ("samp_db tables (using mysql_fetch_row()):<BR>
  ");
  while ($row = mysql_fetch_row ($result))
  printf ("%s<BR>
  ", $row[0]);
  $result = mysql_list_tables ("samp_db")
  
  or die ("Query failed");
  print ("samp_db tables (using mysql_tablename()):<BR>
  ");
  for ($i = 0; $i < mysql_num_rows ($result); $i++)
  printf ("%s<BR>
  ", mysql_tablename ($result, $i));
  ?>
  

• int

  
  mysql_query (string query [, int link_id]);

  Sends the query string to the MySQL server on the given connection. For DELETE, INSERT, REPLACE, and UPDATE statements, mysql_query() returns true for success, and false if an error occurs. For a successful query, you can call mysql_affected_rows() to find out how many rows were modified.

  For SELECT statements, mysql_query() returns a positive result set identifier for success, and false if an error occurs. 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 may 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 or not the query returns any rows. The following query is perfectly legal but returns no rows:

  
  SELECT * FROM president WHERE 1 = 0
  

  A query may 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.

  If no link identifier is specified, the most recently opened connection is used. If there is no current connection, mysql_query() attempts to open a connection as if mysql_connect() were called with no arguments. mysql_query() fails if the connection attempt fails.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
       or die ("Could not connect");
      print ("Connected successfully");
      mysql_select_db ("samp_db")
          or die ("Could not select database");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
           or die ("Query failed");
  ?>
  

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 ([int link_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 use mysql_num_rows() with SELECT statements.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db">)
          or die ("Could not select database");
      $query = "INSERT INTO member (last_name,first_name,expiration)"
  .         "VALUES('Brown','Marcia','2002-6-3')";
      $result = mysql_query ($query)
          or die (Query failed);
  printf ("%d row%s inserted
  ",
         mysql_affected_rows (),
         mysql_affected_rows () == 1 ? "" : "s");
  ?>
  

• int

  
  mysql_data_seek (int result_id, int row_num);.

  Each result set returned by a SELECT query has a row cursor to indicate which row should be returned by the next call to the row-fetching functions (mysql_fetch_array(), mysql_fetch_object(), or mysql_fetch_row(). mysql_data_seek() sets the cursor for the given 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
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
          or die ("Could not select database");
      $query = "SELECT last_name, first_name FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      # fetch rows in reverse order
  for ($i = mysql_num_rows ($result) - 1; $i >=0; $i—)
  {
       if (!mysql_data_seek ($result, $i))
  {
           printf ("Cannot seek to row %d
  ", $i);
          continue;
  }
  if(!($row = mysql_fetch_object ($result)))
       continue;
  printf ("%s %s<BR>
  ", $row->last_name, $row->first_name);
  }
  mysql_free_result ($result);
  ?>
  

• array

  
  mysql_fetch_array (int result_id [, int result_type]);
  

  Returns the next row of the given result set as an array. Returns false if there are no more rows. The array contains values stored both by numeric column indices and associatively keyed by column names. In other words, each column value may 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 may 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 write the query to provide an alias for the column.

  The result_type parameter may 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.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
  or die ("Could not select database");
      $query = "SELECT last_name, first_name FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
  while ($row = mysql_fetch_array ($result))
  {
     # print each name twice, once using numeric indices,
     # once using associative (name) indices,
     printf ("%s %s<BR>
  ", $row[0], $row[1]);
     printf ("%s %s<BR>
  ", $row["last_name"], $row["first_name"]);
  }
  mysql_free_result ($result);
  ?> 
  

• object

  
  mysql_fetch_field (int result_id [, int col_num]);

  Returns metadata information about the given column in the result set; returns 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; returns false if col_num is out of range.

  The information is returned as an object that has the properties shown inTable H.1.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
  or die ("Could not select database");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
  or die ("Query failed");
  # get column metadata
  for ($i = 0; $i < mysql_num_fields ($result); $i++)
  {
      printf ("Information for column %d:<BR>
  ", $i);
      $meta = mysql_fetch_field ($result);
      if (!$meta)
  {
              print ("No information available<BR>
  ");
              continue;
  }
  print ("<PRE>
  ");
  printf ("max_length:   %s
  ", $meta->max_length);
  printf ("multiple_key: %s
  ", $meta->multiple_key);
  printf ("name:         %s
  ", $meta->name);
  printf ("blob:         %s
  ", $meta->blob);
  printf ("not_null:     %s
  ", $meta->not_null);
  printf ("numeric:      %s
  ", $meta->numeric);
  printf ("primary_key:  %s
  ", $meta->primary_key);
  printf ("table:        %s
  ", $meta->table);
  printf ("type:         %s
  ", $meta->type);
  printf ("unique_key:   %s
  ", $meta->unique_key);
  printf ("unsigned:     %s
  ", $meta->unsigned);
  printf ("zerofill:     %s
  ", $meta->zerofill);
  print ("</PRE>
  ");
  }
  ?>
  

  Table H.1. mysql_fetch_field() Properties

  Property	Meaning
  blob	1 if the column is a BLOB type, 0 otherwise
  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

  
  

• array

  
  mysql_fetch_lengths (int 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_object(), or mysql_fetch_row(). Returns false if no row has yet been fetched or if an error occurred.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
           or die ("Could not connect");
      mysql_select_db ("samp_db")
          or die ("Could not select database");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      $row_num = 0;
  while (mysql_fetch_row ($result))
  {
      ++$row_num;
      # get lengths of column values
      printf ("Lengths of values in row %d:<BR>
  ", $row_num);
      $len = mysql_fetch_lengths ($result);
      if (!$len)
      {
         print ("No information available<BR>
  ");
         break;
  }
  print ("<PRE>
  ");
  for ($i = 0; $i < mysql_num_fields ($result); $i++)
      printf ("column %d: %s
  ", $i, $len[$i]);
  print ("</PRE>
  ");
  }
  ?> 
  

• object

  
  mysql_fetch_object (int result_id [, int result_type]);
  

  Returns the next row of the given result set as an object; returns false if there are no more rows. Column values may be accessed as properties of the object. The property names are the names of the columns selected in the query from which the result set was generated.

  The result_type parameter may 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. (It's not clear to me what the utility of specifying MYSQL_NUM is, given that numbers are not legal property names.)

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
         or die ("Could not select database");
      $query = "SELECT last_name, first_name FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      while ($row = mysql_fetch_object ($result))
            printf ("%s %s<BR>
  ", $row->last_name, $row->first_name);
      mysql_free_result ($result);
  ?>
  

• array

  
  mysql_fetch_row (int result_id);
  

  Returns the next row of the given result set as an array; returns false if there are no more rows.

  Column values may be accessed as array elements, using column indices in the range from 0 to mysql_num_fields()–1.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
         or die ("Could not select database");
      $query = "SELECT last_name, first_name FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      while ($row = mysql_fetch_row ($result))
              printf ("%s %s<BR>
  ", $row[0], $row[1]);
       mysql_free_result ($result);
  ?>
  

• string

  
  mysql_field_name (int 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
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
          or die ("Could not select database");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      # get column names
      for ($i = 0; $i < mysql_num_fields ($result); $i++)
  {
          printf ("Name of column %d: ", $i);
          $name = mysql_field_name ($result, $i);
          if (!$name)
             print ("No name available<BR>
  ");
  else
                print ("$name<BR>
  ");
  }
  ?>
  

• int

  
  mysql_field_seek (int 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
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
         or die ("Could not select database");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      # get column metadata
      for ($i = 0; $i < mysql_num_fields ($result); $i++)
  {
          printf ("Information for column %d:<BR>
  ", $i);
  if (!mysql_field_seek ($result, $i))
  {
          print ("Cannot seek to column<BR>
  ");
          continue;
  }
  $meta = mysql_fetch_field ($result, $i);
  if (!$meta)
  {
        print ("No information available<BR>
  ");
       continue;
  }
  print ("<PRE>
  ");
  printf ("blob:         %s
  ", $meta->blob);
  printf ("max_length:   %s
  ", $meta->max_length);
  printf ("multiple_key: %s
  ", $meta->multiple_key);
  printf ("name:         %s
  ", $meta->name);
  printf ("not_null:     %s
  ", $meta->not_null);
  printf ("numeric:      %s
  ", $meta->numeric);
  printf ("primary_key:  %s
  ", $meta->primary_key);"
  printf ("table:        %s
  ", $meta->table);
  printf ("type:         %s
  ", $meta->type);
  printf ("unique_key:   %s
  ", $meta->unique_key);
  printf ("unsigned:     %s
  ", $meta->unsigned);
  printf ("zerofill:     %s
  ", $meta->zerofill);
  print ("</PRE>
  ");
  }
  ?>
  

• string

  
  mysql_field_table (int result_id, int col_num);
  

  Returns the name of the table that contains the given column of the result set.

  For calculated columns, the name is empty.

  col_num should be in the range from 0 to mysql_num_fields()–1.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      for ($i = 0; $i < mysql_num_fields ($result); $i++)
  {
          printf ("column %d:", $i);
          printf (" name %s,
  ", mysql_field_name ($result, $i));
          printf (" table %s
  ", mysql_field_table ($result, $i));
  print "<BR>
  ";
  }
  ?>
  

• string

  
  mysql_field_type (int result_id, int col_num);
  

  Returns the name of the type for the given column of the result set. Type names are listed in Appendix B, "Column Type Reference."

  col_num should be in the range from 0 to mysql_num_fields()–1.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      for ($i = 0; $i < mysql_num_fields ($result); $i++)
  {
          printf ("column %d:", $i);
          printf (" name %s,
  ", mysql_field_name ($result, $i));
          printf (" type %s
  ", mysql_field_type ($result, $i));
          print "<BR>
  ";
  }
  ?>
  

• string

  
  mysql_field_flags (int result_id, int col_num);
  

  Returns metadata information about the given column in the result set as a string; returns 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.2 lists the words that may be present in the string.

  col_num should be in the range from 0 to mysql_num_fields()–1.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db");
      $query = "SELECT * FROM member";
      $result = mysql_query ($query)
          or die ("Query failed");
      for ($i = 0; $i < mysql_num_fields ($result); $i++)
  {
          printf ("column %d:", $i);
          printf (" name %s,
  ", mysql_field_name ($result, $i));
          printf (" flags %s
  ", mysql_field_flags ($result, $i));
          print "<BR>
  ";
  }
  ?>
  

  Table H.2. 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 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

  
  

• int

  
  mysql_field_len (int 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
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db");
      $query = "SELECT * FROM member";
      $result = mysql_query ($query)
          or die ("Query failed");
      for ($i = 0; $i < mysql_num_fields ($result); $i++)
      {
          printf ("column %d:", $i);
          printf (" name %s,
  ", mysql_field_name ($result, $i));
          printf (" len %d
  ", mysql_field_len ($result, $i));
  print "<BR>
  ";
  }
  ?>
  

• int

  
  mysql_free_result (int result_id);
  

  Frees any resources associated with the given result set. Result sets are freed automatically when a script terminates, but you may wish to call this function explicitly in a script that generates many result sets. For example, this script will use a considerable amount of memory:

  
  <?php
      $link = mysql_connect ("localhost", "paul", "secret");
      mysql_select_db ("samp_db");
      for ($i = 0; $i < 10000; $i++)
  {
          $result = 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
      $link = mysql_connect ("localhost", "paul", "secret");
      mysql_select_db ("samp_db");
      for ($i = 0; $i < 10000; $i++)
  {
            $result = mysql_query ("SELECT * from president");
            mysql_free_result ($result);
  }
  ?>
  

• int

  
  mysql_insert_id ([int link_id])
  

  Returns the AUTO_INCREMENT value generated by the most recently executed query on the given connection. Returns zero if no such value has been generated during the life of the connection. Generally, you should call mysql_insert_id() immediately after a query that you expect to generate a new value; if any other query intervenes between that query and the point at which you want to use the value, the value of mysql_insert_id() may be reset to zero by the intervening query.

  Note that the behavior of mysql_insert_id() differs from that of the SQL function LAST_INSERT_ID(). mysql_insert_id() is maintained in the client and is set for each query. The value of LAST_INSERT_ID() is maintained in the server and persists from query to query.

  The values returned by mysql_insert_id() are connection-specific and are not affected by AUTO_INCREMENT activity on other connections.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
         or die ("Could not select database");
  $query = "INSERT INTO member (last_name,first_name,expiration)"
  .     "VALUES('Brown','Marcia','2002-6-3')";
      $result = mysql_query ($query)
  or die ("Query failed");
      printf ("membership number for new member: %d
  ", mysql_insert_id());
  ?>
  

• int

  
  mysql_num_fields (int result_id);
  

  Returns the number of columns in the given result set.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
  
          or die ("Could not select database");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      printf ("Number of columns: %d
  ", mysql_num_fields ($result));
  ?>
  

• int

  
  mysql_num_rows (int result_id);
  

  Returns the number of rows in the given result set.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
  
          or die ("Could not connect");
      mysql_select_db ("samp_db")
  or die ("Could not select database");
      $query = "SELECT * FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      printf ("Number of rows: %d
  ", mysql_num_rows ($result));
  ?>
  

• int

  
  mysql_result (int result_id, int row, mixed field);
  

  Returns a value from the given row of a result set. The column is identified by the field parameter, which may be either a numeric column index or the column name specified in the query.

  This function is slow; it's preferable to use mysql_fetch_array(), mysql_fetch_object(), or mysql_fetch_row() instead.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      mysql_select_db ("samp_db")
  or die ("Could not select database");
      $query = "SELECT last_name, first_name FROM president";
      $result = mysql_query ($query)
          or die ("Query failed");
      for ($i = 0; $i < mysql_num_rows ($result); $i++)
  {
          for ($j = 0; $j < mysql_num_fields ($result); $j++)
  {
          if ($j > 0)
             print (" ");
          print (mysql_result ($result, $i, $j));
  }
          print "<BR>
  ";
  }
  mysql_free_result ($result);
  ?>
  

• string

  
  mysql_tablename (int result_id, int row_num);

  Given a result identifier returned by mysql_list_dbs() or mysql_list_tables() and a row index row_num, returns the name stored in the given row of the result set.

  The row index should be in the range from 0 to mysql_num_rows()–1.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      $result = mysql_list_tables ("samp_db")
          or die ("Query failed");
      print ("samp_db tables:<BR>
  ");
      for ($i = 0; $i < mysql_num_rows ($result); $i++)
           printf ("%s<BR>
  ", mysql_tablename ($result, $i));
  ?>
  

Database Routines

The routines in this section allow you to create and destroy databases. They also allow you to select a default database for the current server session.

• int

  
  mysql_create_db (string db_name [, int link_id]);
  

  Tells the MySQL server identified by link_id to create the database with the given name. Returns true if the database was created successfully; returns false if an error occurred. You must have the CREATE privilege on the database to create it.

  It is preferable to use mysql_query() to issue a CREATE DATABASE statement than to use mysql_create_db().

  
  
  <?php
  $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
      or die ("Could not connect");
      if (mysql_create_db ("my_db"))
  
         print ("Database created successfully
  ");
  
  else
         printf ("Error creating database: %s
  ", mysql_error ());
  ?>
  

• int

  
  mysql_drop_db (string db_name [, int link_id]);

  Tells the MySQL server identified by link_id to drop (remove) the database with the given name. Returns true if the database was removed successfully; returns false if an error occurred. You must have the DROP privilege on the database to remove it.

  Be careful with this function; if you drop a database, it's gone. You can't get it back.

  It is preferable to use mysql_query() to issue a DROP DATABASE statement than to use mysql_drop_db().

  
  <?php
  
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      if (mysql_drop_db ("my_db"))
         print ("Database dropped successfully
  ");
  else
         printf ("Error dropping database: %s
  ", mysql_error ());
  ?>
  

• int

  
  mysql_select_db (string db_name [, int link_id]);
  

  Selects the given database to make it the default database for the given connection. Returns true for success; returns false if an error occurs.

  If no connection is specified, the current connection is used. If there is no current connection, mysql_select_db() attempts to open a connection as if mysql_connect() were called with no arguments. mysql_select_db() fails if the connection attempt fails.

  
  <?php
      $link = mysql_pconnect ("pit-viper.snake.net", "paul", "secret")
          or die ("Could not connect");
      print ("Connected successfully");
      mysql_select_db ("samp_db")
         or die ("Could not select database");
  ?>