A PHP database metadata example

PHP database FAQ: How do I access the metadata for a database in PHP? That is, for a given database, how do I list the database tables, and fields for those tables?

Solution: I just started this script a little while ago, but even with my limited knowledge of PHP it was very easy to get access to the database metadata, including the database tables and the fields in each table.

I've added some comments for what I had to do to see better debug error messages during the process, and I also had a problem getting things to work with MAMP, but everything after the "$dsn" line is related to getting the PHP database metadata.

Here's the source code for my PHP database metadata script:

#!/Applications/MAMP/bin/php5/bin/php -q
<?php

  # created by alvin alexander, http://devdaily.com
  
  # need this to really see error messages
  # http://php.net/manual/en/ref.errorfunc.php
  # error_reporting(E_ALL | E_STRICT); # this prints a *lot* of errors in pear
  error_reporting(E_ALL);

  # ini_set — sets the value of a configuration option
  ini_set("display_errors", 'On'); 

  # had to do this on my laptop to get mamp/php to work with my 'normal' mysql db
  # sudo ln -s /private/tmp/mysql.sock /Applications/MAMP/tmp/mysql/mysql.sock

  require_once 'MDB2.php';

  $dsn = array(
    'phptype'  => 'mysql',
    'username' => 'root',
    'password' => 'foo',
    'hostspec' => 'localhost',
    'database' => 'drupal',
  );

  $options = array(
    'debug'       => 2,
    'portability' => MDB2_PORTABILITY_ALL,
  );

  # use Pear MDB2
  # @see http://pear.php.net/package/MDB2/docs/latest/MDB2/MDB2.html
  $mdb =& MDB2::connect($dsn, $options);
  if (PEAR::isError($mdb)) {
    die($mdb->getMessage());
  }

  # get the database name
  $dbname = $mdb->getDatabase();
  echo "$dbname\n";

  echo "\n=== TABLES ===\n";

  # need the Manager module to do our magic
  # @see http://pear.php.net/package/MDB2/docs/latest/MDB2/MDB2_Driver_Manager_Common.html
  $mdb->loadModule('Manager');

  # (1) get a list of the tables
  #$tables = $mdb->manager->listTables();
  $tables = $mdb->listTables();

  foreach ($tables as $table) {
    echo "$table\n";
  }

  # (2) TABLE FIELDS
  echo "\n=== FIELDS ===\n";
  $fields = $mdb->listTableFields('users');
  foreach ($fields as $field) {
    echo "$field\n";
  }

  $mdb->disconnect();

?>

Really, the biggest problems in creating this script were:

• Learning that PEAR DB had been replaced by PEAR MDB2.
• Reconfiguring my MAMP environment to work so I could install MDB2.
• Fixing the MAMP MySQL socket problem. (My problem, in that I have two MySQL installations going on the same computer.)
• Working through the MDB2 documentation to figure out how to get to the listTables() method. These lines were really key:

$mdb->loadModule('Manager');
$tables = $mdb->listTables();

Once I learned how to perform this loadModule trick, everything just started working, and I got a list of the database tables with the listTables method, and got a list of fields for one database table (which I hard-coded) with the listTableFields method.