Choose your database:
AnySQL
MySQL
MS SQL Server
PostgreSQL
SQLite
Firebird
Oracle
SQL Anywhere
DB2
MaxDB

Subscribe to our news:
Partners
Testimonials
Johnattan Badden, Manager: "The software is easy to work with and most importantly, report management is extremely easy, even for a novice like me! I am able to create and edit my databases with a few clicks and easily maintain them afterwards. I highly recommend SQLite Maestro to educational institutions: it's easy and flexible, it is exactly what can be good for using in schools and colleges".
Mark Worsnop: "By the way I wanted to say thank you for a good software package. It's a pleasure to work with Maestro as it "just works" and makes my job that much easier".

More

Add your opinion

SQL Maestro Group / Products / SQLite / Resources / many-to-many relationship

How to handle a many-to-many relationship with PHP Generator?

Last modified: Jul 19, 2010

Prev Next

Abstract

Below is a step-by-step explanation of how to create a master-detail presentation for relationships that are multivalued in both directions. The information represented in this article describes the usage of PHP Generators for: MySQL, Microsoft SQL Server, PostgreSQL, Oracle, SQLite, Firebird, IBM DB2, SQL Anywhere, MaxDB.

Problem

Detail presentations for tables linked by one-to-many relationships are created with PHP Generator with a couple of clicks. Let's see the implementation of such presentation for two entities 'employee' and 'office' where one employee can work at more than one office and an office can have more than one employee assigned (a many-to-many relationship).

Diagram of the many-to-many relationshipPicture 1. Diagram of the many-to-many relationship

In database design, the standard decision of the problem is the breaking of such relationship into two one-to-many relationships and creating a new (junction/link) table to stand between the two existing tables.

Diagram with the junction tablePicture 2. Diagram with the junction table

SQL definitions of the tables are as follows:

Listing 1. Initial table definitions
CREATE TABLE employee (
  id    int AUTO_INCREMENT NOT NULL,
  name  varchar(50) NOT NULL,
  occupation varchar(20),
  phone varchar(15),
  /* Keys */
  PRIMARY KEY (id)
) ENGINE = InnoDB;
 
CREATE TABLE office (
  id       int AUTO_INCREMENT NOT NULL,
  address  varchar(200) NOT NULL,
  /* Keys */
  PRIMARY KEY (id)
) ENGINE = InnoDB;
Listing 2. SQL definition of the junction table
CREATE TABLE employee_office (
  employee_id  int NOT NULL,
  office_id    int NOT NULL,
  /* Keys */
  PRIMARY KEY (employee_id, office_id),
  /* Foreign keys */
  CONSTRAINT employee_office_relation_ibfk_1
    FOREIGN KEY (employee_id)
    REFERENCES employee(id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT, 
  CONSTRAINT employee_office_relation_ibfk_2
    FOREIGN KEY (office_id)
    REFERENCES office(id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

To see the list of offices and lists of employees assigned to selected offices on one web page, create a PHP script with a master-detail presentation.

Solution

Here are the steps involved in the implementation:

  • Configuring datasources
  • Specifying a master-detail presentation for the 'office' table

Each step will be covered in detail below. To work with the prepared script, you need to have Linux/Unix or Windows Web Server installed and PHP 5.

Step 1

Add the 'office' table as a datasource using the Add table or view button.

Adding tablePicture 3. Adding table

Specify a query to be a datasource for the master-detail presentation. For this purpose use the Create query button and enter the following query text:

Listing 3. Query
SELECT
  eo.office_id,
  e.* 
FROM employee e
INNER JOIN employee_office eo 
  ON eo.employee_id = e.id

To allow users to insert, update and delete an employee to/from the employee list of the selected office, check the Updatable query box and set update statements:

Listing 4. Update statements
INSERT INTO employee_office
VALUES
(:id, :office_id)
 
UPDATE employee_office
SET employee_id=:id
WHERE employee_id=:OLD_id AND office_id=:OLD_office_id
 
DELETE FROM employee_office
WHERE employee_id=:id AND office_id=:office_id

Adding an updatable queryPicture 4. Adding an updatable query

Step 2

To specify a master-detail presentation to be used to display a list of employees related to a selected office, open the Page Editor of the 'office' table at the Details tab and follow the Add... link of the popup menu. Set up the Detail relation editor properties: select the created query as Detail data source, 'id' as Master field and 'office_id' as Detail field.

Setting a master-detail presentationPicture 5. Setting a master-detail presentation

You can also adjust the appearance of the employee lists (details pages) with the Detail page editor. We disallowed the content of 'office_id' and 'id' columns to be represented on the prepared web page (see the screen below).

Setting a master-detail presentationPicture 6. Setting a master-detail presentation

The result web page is shown below:

Result scriptPicture 8. Result script

Prev Next