Using mysql_fetch_field() to describe the structure of a table : PHP

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

Functions to avoid

Several MySQL functions shouldn't be used in practice:

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.
The function mysql_result( ) is a slower alternative to fetching and processing a row with mysql_fetch_row( ) or mysql_fetch_array( ) and shouldn't be used in practice.
mysql_fetch_assoc( ) fetches a row of results as an associative array only, providing half the functionality of mysql_fetch_array( ). The other half-fetching into an array accessed by numeric index-is provided by mysql_fetch_row( ). Since mysql_fetch_array( ) provides both sets of functionality-or can provide the same functionality by passing through MYSQL_ASSOC as the second parameter-it should 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( ) combines the functionality of mysql_select_db( ) and mysql_query( ). This function has been deprecated in recent releases of PHP.