How to dump mysql table relations in PHP

Question

I searched a lot but could not find a way to dump table relations like one-to-one, one-to-many vs in PHP.

Is there a way to handle this issue in PHP?

A result might be:

array(
   'tableA' => array(
                'one-to-one' => array('tableB', 'tableC'),
                'one-to-many' => array('tableD'),

   'tableB' => array(
                'one-to-one' => array('tableA')


    ...        
)

Any suggestions are much appreciated.

I found 'Describe tablename' which contains meta info about which columns are references to other tables but is not enough to determine relations.. — whoi
– whoi, Commented Oct 21, 2009 at 23:17

whoi · Accepted Answer · 2009-10-22 00:07:15Z

2

I found a script at http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html which describes parsing table info with regex. I had manipulated the code to work. here is the code.

<?php
$conn = mysql_connect('localhost', 'user', 'pass');
if (!$conn) {
    die('Could not connect: ' . mysql_error());
}

//DB connection already established
mysql_query("use db");
$res = mysql_query("SHOW CREATE TABLE tbl");
$row = mysql_fetch_assoc($res);
mysql_free_result($res);

//Only work on InnoDB foreign key info.
if(preg_match_all(
         '/FOREIGN KEY \(`(.*)`\) REFERENCES `(.*)` \(`(.*)`\)/',
         $row['Create Table'],
         $matchArr)) {
    print_r($matchArr); //which writes down the result

}


?>

answered Oct 22, 2009 at 0:07

whoi

3,4718 gold badges39 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Byron Whitlock · Accepted Answer · 2009-10-21 23:24:48Z

0

You have to do this yourself by parsing the output of the DESCRIBE command. You might consider using a ORM like doctrine. You tell doctrine to make a one to one relation on table a and table b and it handles the rest.

answered Oct 21, 2009 at 23:24

Byron Whitlock

54.2k29 gold badges129 silver badges170 bronze badges

1 Comment

whoi Over a year ago

I use code igniter and Datamapper for ORM, and I am creating a simple CRUD generator which needs this relations. I found a tmp solution which is below answer

Collectives™ on Stack Overflow

How to dump mysql table relations in PHP

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related