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 the
clients 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!
Next: Bookmark Bandit >>
More PHP Articles
More By Harish Kamath, (c) Melonfire
