Companies and Interests (Many-to-Many) GridPHP Sample Grid - gridphp.com

select2-tags-n2n.php

<?php
/**
 * PHP Grid Component
 *
 * @author Abu Ghufran <[email protected]> - http://www.phpgrid.org
 * @version 2.0.0
 * @license: see license.txt included in package
 */

// -- DB SCRIPT --
/*
CREATE TABLE `companies` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `createdAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `ref_interest` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `company_interest` (
  `company_id` int NOT NULL,
  `interest_id` int NOT NULL,
  PRIMARY KEY (`interest_id`,`company_id`)
) ENGINE=InnoDB;


INSERT INTO `companies` (`id`, `name`, `address`, `phone`) VALUES
(1, 'Tech Solutions', '123 Tech Street', '555-0101'),
(2, 'Innovate Inc.', '456 Innovation Ave', '555-0102'),
(3, 'Data Systems', '789 Data Drive', '555-0103'),
(4, 'Cloud Services', '101 Cloud Court', '555-0104'),
(5, 'Secure Networks', '212 Security Blvd', '555-0105');

INSERT INTO `ref_interest` (`id`, `name`) VALUES
(1, 'Software Development'),
(2, 'Data Analytics'),
(3, 'Cloud Computing'),
(4, 'Cybersecurity');

INSERT INTO `company_interest` (`company_id`, `interest_id`) VALUES
(1, 1),
(1, 2),
(2, 2),
(2, 3),
(3, 2),
(4, 3),
(5, 4);


*/

// include db config
include_once("../../config.php");

// include and create object
include(PHPGRID_LIBPATH."inc/jqgrid_dist.php");

// Database config file to be passed in phpgrid constructor
$db_conf = array(
    "type"     => PHPGRID_DBTYPE,
    "server"   => PHPGRID_DBHOST,
    "user"     => PHPGRID_DBUSER,
    "password" => PHPGRID_DBPASS,
    "database" => PHPGRID_DBNAME2
);

$g = new jqgrid($db_conf);

// Custom update function
function update_company_interest($data)
{
    global $g;
    $company_id = $data["id"];
    $name = $data["params"]["name"];
    $address = $data["params"]["address"];
    $phone = $data["params"]["phone"];
    $interest_ids_str = $data["params"]["interests"];

    // Update company
    $g->execute_query("UPDATE companies set name = ?, address = ?, phone = ? WHERE id = ?", array($name, $address, $phone, $company_id));
    
    // Delete existing interests for the company
    $g->execute_query("DELETE FROM company_interest WHERE company_id = ?", array($company_id));

    // Insert the new interests
    if (!empty($interest_ids_str)) {
        $interest_ids = explode(",", $interest_ids_str);
        foreach ($interest_ids as $interest_id) {
            if (!empty($interest_id)) {
                $g->execute_query("INSERT INTO company_interest (company_id, interest_id) VALUES (?, ?)", array($company_id, $interest_id));
            }
        }
    }
    
    echo json_encode(array("id" => $company_id, "success" => true));
    die;
}

// Custom insert function
function add_company_interest($data)
{
    global $g;
    $name = $data["params"]["name"];
    $address = $data["params"]["address"];
    $phone = $data["params"]["phone"];
    $interest_ids_str = $data["params"]["interests"];

    // Insert the new company
    $company_id = $g->execute_query("INSERT INTO companies (name, address, phone) VALUES (?, ?, ?)", array($name, $address, $phone), "insert_id");

    // Insert the new interests
    if (!empty($interest_ids_str)) {
        $interest_ids = explode(",", $interest_ids_str);
        foreach ($interest_ids as $interest_id) {
            if (!empty($interest_id)) {
                $g->execute_query("INSERT INTO company_interest (company_id, interest_id) VALUES (?, ?)", array($company_id, $interest_id));
            }
        }
    }

    echo json_encode(array("id" => $company_id, "success" => true));
    die;
}

function delete_company_interest($data)
{
    global $g;
    $company_id = $data["id"];

    // Delete from companies
    $g->execute_query("DELETE FROM companies WHERE id = ?", array($company_id));
    
    // Delete from company_interest
    $g->execute_query("DELETE FROM company_interest WHERE company_id = ?", array($company_id));
}

// Set the event handler
$e["on_update"] = array("update_company_interest", null, false);
$e["on_insert"] = array("add_company_interest", null, false);
$e["on_delete"] = array("delete_company_interest", null, false);
$g->set_events($e);

$g->set_actions(array(
    "add"=>true, // allow/disallow add
    "edit"=>true, // allow/disallow edit
    "delete"=>true, // allow/disallow delete
    "rowactions"=>true, // show/hide row wise edit/del/save option
    "autofilter" => true, // show/hide autofilter for search
    ) 
);

$opt["caption"] = "Companies and Interests (Many-to-Many)";
$opt["autowidth"] = true;
$g->set_options($opt);

$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";

$g->table = "companies";

$col = array();
$col["title"] = "Id";
$col["name"] = "id";
$col["width"] = "20";
$cols[] = $col;

$col = array();
$col["title"] = "Name";
$col["name"] = "name";
$col["width"] = "100";
$col["editable"] = true;
$cols[] = $col;

$col = array();
$col["title"] = "Address";
$col["name"] = "address";
$col["width"] = "50";
$col["editable"] = true;
$cols[] = $col;

$col = array();
$col["title"] = "Phone";
$col["name"] = "phone";
$col["width"] = "50";
$col["editable"] = true;
$cols[] = $col;

$col = array();
$col["title"] = "Interests";
$col["name"] = "interests";
$col["width"] = "250";
$col["editable"] = true;
$col["edittype"] = "select";
$col["formatter"] = "badge";

$str = $g->get_dropdown_values("SELECT id as k, name as v FROM ref_interest");
$col["editoptions"] = array(
                            "value" => $str,
                            "multiple" => true,
                            "dataInit" => "function(el){ setTimeout(function(){ jQuery(el).select2({tags:true, tokenSeparators: [',', ' ']}); },50); }"
                        );
$cols[] = $col;

$g->set_columns($cols);

$out = $g->render("list1");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html>
    <head>
        <link rel="stylesheet" type="text/css" media="screen" href="../../lib/js/themes/redmond/jquery-ui.custom.css"></link>
        <link rel="stylesheet" type="text/css" media="screen" href="../../lib/js/jqgrid/css/ui.jqgrid.css"></link>
        <link href="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.5/css/select2.min.css" rel="stylesheet" />

    <script src="../../lib/js/jquery.min.js" type="text/javascript"></script>
    <script src="../../lib/js/jqgrid/js/i18n/grid.locale-en.js" type="text/javascript"></script>
    <script src="../../lib/js/jqgrid/js/jquery.jqGrid.min.js" type="text/javascript"></script>
    <script src="../../lib/js/themes/jquery-ui.custom.min.js" type="text/javascript"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/select2/4.0.5/js/select2.min.js"></script>
</head>
<body>
    <div style="margin:10px">
    <?php echo $out?>
    </div>
</body>
</html>