Handle Many-to-Many relations in GridPHP

Hello,

In this post, we’re showing a practical example, handling data of companies with multiple business interests.

The Problem

You have companies. Each company has multiple interests (Software Development, Cloud Computing, etc.). Each interest can belong to multiple companies.

The traditional approach means:

• 3 tables with complex JOINs
• Custom forms for managing relationships
• Manual junction table handling
• Hours of debugging

A Simpler Way

Here’s a working example using GridPHP that handles all the complexity automatically.

Database Setup

-- Three simple tables
CREATE TABLE companies (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(255),
  address varchar(255),
  phone varchar(15)
);

CREATE TABLE ref_interest (
  id int PRIMARY KEY AUTO_INCREMENT,
  name varchar(45)
);

CREATE TABLE company_interest (
  company_id int,
  interest_id int,
  PRIMARY KEY (company_id, interest_id)
);

The Smart Query

Instead of showing relationships in separate rows, use GROUP_CONCAT:

$g->select_command = "SELECT c.id, c.name, c.address, c.phone, 
                      GROUP_CONCAT(i.id) as interests
                      FROM companies c 
                      LEFT JOIN company_interest ci ON c.id = ci.company_id 
                      LEFT JOIN ref_interest i ON ci.interest_id = i.id 
                      GROUP BY c.id";

This displays all interests for each company in a single row.

Auto-Handle Junction Table

The key is custom handlers that manage the junction table automatically:

function update_company_interest($data) {
    global $g;

    // Update company info
    $g->execute_query("UPDATE companies SET name=?, address=?, phone=? WHERE id=?", 
                      array($data["params"]["name"], 
                            $data["params"]["address"], 
                            $data["params"]["phone"], 
                            $data["id"]));

    // Delete old relationships
    $g->execute_query("DELETE FROM company_interest WHERE company_id=?", 
                      array($data["id"]));

    // Insert new relationships
    if (!empty($data["params"]["interests"])) {
        $interest_ids = explode(",", $data["params"]["interests"]);
        foreach ($interest_ids as $interest_id) {
            $g->execute_query("INSERT INTO company_interest VALUES (?, ?)", 
                            array($data["id"], $interest_id));
        }
    }
}

Multi-Select Interface

Add a searchable multi-select dropdown:

$col["edittype"] = "select";
$col["editoptions"] = array(
    "value" => $g->get_dropdown_values("SELECT id, name FROM ref_interest"),
    "multiple" => true,
    "dataInit" => "function(el){ 
        jQuery(el).select2({tags:true}); 
    }"
);

The Result

Users see a grid with companies and their interests. When editing:

1. Click edit on any row
2. Multi-select dropdown appears with search
3. Select/deselect interests
4. Save – junction table updates automatically

Time saved: 3 days → 1 hour

Use This Pattern For:

• Users ↔ Roles
• Products ↔ Categories
• Students ↔ Courses
• Posts ↔ Tags

Complete Code

Full working example: GitHub Gist

Bottom line: Many-to-many relationships don’t need to be complicated. Use GROUP_CONCAT for display, custom handlers for the junction table, and a multi-select UI for user-friendly management.

Have a many-to-many challenge? This pattern works for any scenario with 3 tables and complex relationships.

Next Actions

• Download Free version
• Buy Full Featured version

Active License Subscription customers can get free upgrade using this link.