[PHP] PHP, MySQL and Foreign Keys - relations()

[Richard A. DeVenezia]

Here is a helpful function for making sense of
- foreign keys: which columns of this table refer to primary key of another
table
- referential: which tables columns refer to the primary key of this table

Presumes you are running MySQL with innodb tables.  Tested with MySQL
4.1.1-alpha.

function relations ()
{
  $statement = "SHOW TABLE STATUS ";
  $res = mysql_query ($statement) or croak (mysql_error());

  $meta = array();

  while ($row = mysql_fetch_array($res)) {
    $bits = array_map ('trim', explode (';', $row['Comment']));
    $name = $row['Name'];
    foreach ($bits as $bit)
    {
      if (!preg_match ('/\((.*)\) REFER (.*)/', $bit, $ab)) continue;

      $fk = array();
      $fk ['columns'] = preg_split ('/\(|\)|,/', $ab[1]);

      $refers = array();

      preg_match ('/(.+)\/(.+)\((.+)\)/', $ab[2], $x);
      $y = preg_split ('/,/', $x[3]);

      $refers['database'] = $x[1];
      $refers['table'] = $x[2];
      $refers['columns'] = $y;
      $fk ['refer'] = $refers;

      $meta[$x[1]][$name]['foreign_key'][] = $fk;
      $meta[$x[1]][$x[2]]['referential'][$x[3]][] = $name;
    }
  }

  return $meta;

print '<pre>';
print_r ($meta);
print '</pre>';

}

The multi-level meta hash might look something like this:

[ticketing] => Array (  <-- data base
  [event] => Array (  <-- table
    [foreign_key] => Array (
      [0] => Array ( <-- first foreign key
        [columns] => Array (
          [0] => VenueId
        )
        [refer] => Array (
          [database] => ticketing
          [table] => venue
          [columns] => Array (
                  [0] => Id
            )
          )
        )
      )
      [referential] => Array (
        [Id] => Array ( <-- what tables refer to columns of this table
(keyvalue will be comma separated string of column names if composite
foreign key)
          [0] => eventseatclass
          [1] => price
          [2] => reserved
          [3] => ticket
        )
      )
    )

  [venue] => Array ( <-- table, no foreign keys
    [referential] => Array (
      [Id] => Array (  <-- column id of this table is referred to by 5 other
tables
        [0] => event
        [1] => paymenttype
        [2] => seat
        [3] => seatclass
        [4] => venueuser
      )
    )
  )
  ...

-- 
Richard A. DeVenezia

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php