Doing More with phpMyAdmin (Part 2) - Tangled Relationships
( Page 4 of 7 )

A major cause for complaint amongst developers working with earlier versions of MySQL was the lack of support for foreign keys. This lack of support meant that developers needed to hard-wire additional safeguards into their code to maintain data integrity between tables. Newer versions of MySQL do include support for foreign keys, but this support is still fairly new and fails to address users still working with older versions of the software.

While phpMyAdmin cannot do much to solve this problem, the developers behind the application have tried to make things a little simpler by allowing developers to define foreign key relationships between tables at the phpMyAdmin level, if not the MySQL level. Doing this makes it possible to enforce the integrity constraints between tables when entering records, so long as phpMyAdmin is being used for data entry.

The best way to understand this is with a simple example. Navigate to the Structure option of the branches table created previously, and scroll down the page to location the Relation view hyperlink. Selecting this link will take you to the section that allows you to define relationships between tables, associate comments with columns (useful when creating a database dictionary, explained later in this tutorial) and specify the column to use in foreign key references. Here's what it looks like.

Set the relationship between theclients and branches tables in the Links To section, by using the drop-down list to associate the branches.cid field with the clients.cid field, as seen here.

Next, go to the Relation view for the clients table, and tell phpMyAdmin to show the cname field in the Choose Field to display box.

Now, if you try inserting some data into the branches table, phpMyAdmin will, instead of allowing you free-form entry into the cid field, provide you with a drop-down selection list of all the cname values from the clients table, thus making it impossible for you to enter an incorrect or non-existent client ID.

Exercising this option has other advantages too. Browse the branches table, and you'll see that the values in the cid column are clickable; Just click a value and you'll be transported to the corresponding record in the clients table. In fact, just hold your mouse over any of the cid values and you will see the corresponding cname value from the clients table as a neat little tool tip. Cool, huh?

Once you set up the remaining relationships between the tables, you'll have a ready-to-use administration module in a fraction of the time it would have taken you to code it in regular PHP!

>>> More PHP Articles          >>> More By Harish Kamath, (c) Melonfire