Home arrow PHP arrow Page 3 - Doing More with phpMyAdmin (Part 2)

Total Recall - PHP

phpIn the first segment of this two-part tutorial, I gave you a quick overview of some of the interesting new features available in phpMyAdmin. In this concluding segment, find out how to use phpMyAdmin to define relationships between tables, maintain a log of commonly-used queries and create entity-relationship diagrams.

TABLE OF CONTENTS:
  1. Doing More with phpMyAdmin (Part 2)
  2. The Ground Work
  3. Total Recall
  4. Tangled Relationships
  5. Bookmark Bandit
  6. Looking Up the Dictionary
  7. History Lesson
By: Harish Kamath, (c) Melonfire
Rating: starstarstarstarstar / 41
December 08, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
Before I begin, a quick introduction to the tables I'll be using throughout this tutorial seems to be in order. In order to explain the new features of phpMyAdmin, I'll be using a fictitious company's accounting database, which consists of the following four tables:
  1. The services table: The fictitious system under discussion consists of a service company's accounting database. This company offers customers a number of outsourced services, each of which is associated with a fee and has a unique service ID. This information is stored in a services table, which looks like this:

     CREATE TABLE `services` (
    `sid` tinyint(4) NOT NULL default '0',
    `sname` varchar(255) NOT NULL default '',
    `sfee` float(6,2) NOT NULL default '0.00',
    PRIMARY KEY  (`sid`)
    ) TYPE=MyISAM;
    INSERT INTO `services` VALUES 
    (1, 'Accounting', '1500.00');
    INSERT INTO `services` VALUES 
    (2, 'Recruitment', '500.00');
    INSERT INTO `services` VALUES 
    (3, 'Data Management', '300.00');
    INSERT INTO `services` VALUES 
    (4, 'Administration', '500.00');
    INSERT INTO `services` VALUES 
    (5, 'Customer Support', '2500.00');
    INSERT INTO `services` VALUES 
    (6, 'Security', '600.00'); 
    


  2. The clients table: The company also has a list of its current clients stored in a separate clients table. Each client is identified with a unique customer ID.

     CREATE TABLE `clients` (
    `cid` tinyint(4) NOT NULL default '0',
    `cname` varchar(255) NOT NULL default '',
    PRIMARY KEY  (`cid`)
    ) TYPE=MyISAM;
    INSERT INTO `clients` VALUES (101, 'JV Real Estate');
    INSERT INTO `clients` VALUES (102, 'ABC Talent Agency');
    INSERT INTO `clients` VALUES (103, 'DMW Trading');
    INSERT INTO `clients` VALUES (104, 'Rabbit Foods Inc');
    INSERT INTO `clients` VALUES (110, 'Sharp Eyes Detective Agency'); 
    


  3. The branches table: Each customer may have one or more branch offices. The branches table lists the branch offices per customer, together with each branch's location. Each branch has a description, a unique branch ID, and a foreign key reference to the customer ID.

     CREATE TABLE `branches` (
    `bid` int(8) NOT NULL default '0',
    `cid` tinyint(4) NOT NULL default '0',
    `bdesc` varchar(255) NOT NULL default '',
    `bloc` char(3) NOT NULL default ''
    ) TYPE=MyISAM;
    INSERT INTO `branches` 
    VALUES (1011, 101, 'Corporate HQ', 'CA');
    INSERT INTO `branches` 
    VALUES (1012, 101, 'Accounting Department', 'NY');
    INSERT INTO `branches` 
    VALUES (1013, 101, 'Customer Grievances Department', 'KA');
    INSERT INTO `branches` 
    VALUES (1041, 104, 'Branch Office (East)', 'MA');
    INSERT INTO `branches` 
    VALUES (1042, 104, 'Branch Office (West)', 'CA');
    INSERT INTO `branches` 
    VALUES (1101, 110, 'Head Office', 'CA');
    INSERT INTO `branches` 
    VALUES (1031, 103, 'N Region HO', 'ME');
    INSERT INTO `branches` 
    VALUES (1032, 103, 'NE Region HO', 'CT');
    INSERT INTO `branches` 
    VALUES (1033, 103, 'NW Region HO', 'NY'); 
    


  4. The branches_services table: Services supplied to each branch office are listed in this table, which contains pairs of branch IDs and service IDs (foreign keys into the branches and services table respectively).

     CREATE TABLE `branches_services` (
    `bid` int(8) NOT NULL default '0',
    `sid` tinyint(4) NOT NULL default '0'
    ) TYPE=MyISAM;
    INSERT INTO `branches_services` VALUES (1011, 1);
    INSERT INTO `branches_services` VALUES (1011, 2);
    INSERT INTO `branches_services` VALUES (1011, 3);
    INSERT INTO `branches_services` VALUES (1011, 6);
    INSERT INTO `branches_services` VALUES (1012, 1);
    INSERT INTO `branches_services` VALUES (1013, 5);
    INSERT INTO `branches_services` VALUES (1041, 1);
    INSERT INTO `branches_services` VALUES (1041, 4);
    INSERT INTO `branches_services` VALUES (1042, 1);
    INSERT INTO `branches_services` VALUES (1042, 6);
    INSERT INTO `branches_services` VALUES (1101, 1);
    INSERT INTO `branches_services` VALUES (1031, 2);
    INSERT INTO `branches_services` VALUES (1031, 3);
    INSERT INTO `branches_services` VALUES (1031, 4);
    INSERT INTO `branches_services` VALUES (1032, 3);
    INSERT INTO `branches_services` VALUES (1033, 4);
    


    Now, let's see what we can do with this.

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

    blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: