Other functions

int mysql_change_user(string user, string password, [string database, [resource connection]])

Changes the logged-in MySQL user to another user, using that user's password for an optionally specified database and connection. If omitted, the current database and most recently opened connection are assumed. Returns false on failure and, if it does fail, the previous, successful connection stays current.

int mysql_create_db(string db, [resource connection])

Creates a database named db using the connection resource returned from a mysql_connect( ) function call or the last-opened connection if the parameter is omitted.

int mysql_drop_db(string db, [resource connection])

Drops a database named db using the connection resource returned from a mysql_connect( ) function call or the last-opened connection if the parameter is omitted.

object mysql_fetch_field(resource result_set, [int attribute_number])

Returns as an object the metadata for each attribute associated with a result_set 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 that the attribute belongs to
max_length: The maximum length of the attribute
not_null: Set to 1 if the attribute can't 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 nonunique 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

Example 4-3 is a script that uses the mysql_fetch_field() function to emulate most of the behavior of the SHOW COLUMNS or DESCRIBE commands discussed in Chapter 3. The code uses the same five-step query process discussed earlier, with the exception that mysql_fetch_field( ) is used in place of mysql_fetch_row( ). Sample output for the table wine is shown in Example 4-4. The same result could have been achieved by executing DESCRIBE WINE on the winestore database using mysql_query( ) and retrieving the results with mysql_fetch_object( ).

This function also has other uses. For example, it can be used in validation-the subject of Chapter 7-to check whether the data entered by a user is longer than the maximum length of the database attribute. Indeed, a script can be developed that automatically performs basic validation based on the table structure.

Example 4-3. Using mysql_fetch_field( ) to describe the structure of a table

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wine Table Structure</title>
</head>
<body><pre>
<?php
   // Open a connection to the DBMS
   $connection = mysql_connect("localhost","fred","shhh");

   mysql_select_db("winestore", $connection);

   // Run a query on the wine table in the
   // winestore database to retrieve one row
   $result = mysql_query ("SELECT * FROM wine LIMIT 1",
                         $connection);

   // Output a header, with headers spaced by padding
   print str_pad("Field", 20) .
         str_pad("Type", 14) .
         str_pad("Null", 6) .
         str_pad("Key", 5) .
         str_pad("Extra", 12) . "\n";

   // for each of the attributes in the result set
   for($i=0;$i<mysql_num_fields($result);$i++)
   {
      // Get the meta-data for the attribute
      $info = mysql_fetch_field ($result);

      // Print the attribute name
      print str_pad($info->name, 20);

      // Print the data type
      print str_pad($info->type, 6);

      // Print a "(", the field length, and a ")" e.g.(2)
      print str_pad("(" . $info->max_length . ")", 8);

      // Print out YES if attribute can be NULL
      if ($info->not_null != 1)
          print " YES ";
      else
         print  "     ";

      // Print out selected index information
      if ($info->primary_key == 1)
         print " PRI ";
      elseif ($info->multiple_key == 1)
         print " MUL ";
      elseif ($info->unique_key == 1)
         print " UNI ";

      // If zero-filled, print this
      if ($info->zerofill)
         print " Zero filled";

      // Start a new line
      print "\n";
   }

   // Close the database connection
   mysql_close($connection);
?>
</pre>
</body>
</html>

Example 4-4. HTML output of the DESCRIBE WINE emulation script in Example 4-1

<!DOCTYPE HTML PUBLIC
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
  <title>Wine Table Structure</title>
</head>
<body><pre>
Field               Type          Null  Key  Extra
wine_id             int   (1)           PRI
wine_name           string(9)           MUL
type                string(9)
year                int   (4)
winery_id           int   (1)           MUL
description         blob  (0)      YES
</pre>
</body>
</html>

resource mysql_list_tables(string database, [resource connection])

Returns a result set resource handle that can be used as input to mysql_tablename( ) to list the names of tables in a database accessed through a connection. If the connection is omitted, the last-opened connection is assumed.

string mysql_tablename(resource result, int table_number)

Used in combination with mysql_list_tables( ) to produce a list of tables in a database. Returns the name of the table indexed by the numeric value table_number using a result resource returned from the mysql_list_tables( ) function.

The number of tables in a database can be determined by calling mysql_num_rows( ) with the result resource handle returned from mysql_list_tables( ) as a parameter.

[Previous] [Contents] [Next]