Indirect join

I have a table like this

scrum_team with fields scrum_masterID

CREATE TABLE `scrum_team` (
`scrum_teamID` int(11) NOT NULL AUTO_INCREMENT,
`team_name` varchar(45) DEFAULT NULL,
`date_started` datetime DEFAULT NULL,
`scrum_masterID` int(11) DEFAULT NULL,
`product_ownerID` int(11) DEFAULT NULL,
PRIMARY KEY (`scrum_teamID`),
KEY `fk_scrum_team_scrum_master1_idx` (`scrum_masterID`),
KEY `fk_scrum_team_product_owner1_idx` (`product_ownerID`),
CONSTRAINT `fk_scrum_team_product_owner1` FOREIGN KEY (`product_ownerID`) REFERENCES `product_owner` (`product_ownerID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_scrum_team_scrum_master1` FOREIGN KEY (`scrum_masterID`) REFERENCES `scrum_master` (`scrum_masterID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

the field scrum_masterID is primary key of the table scrum_master

scrum_master has a field personID

CREATE TABLE `scrum_master` (
`scrum_masterID` int(11) NOT NULL AUTO_INCREMENT,
`personID` int(11) DEFAULT NULL,
PRIMARY KEY (`scrum_masterID`),
KEY `fk_scrum_master_person1_idx` (`personID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

personID is linked to the person table having fields like first_name, christian name

CREATE TABLE `person` (
`personID` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) DEFAULT NULL,
`insertion` varchar(45) DEFAULT NULL,
`phone` varchar(45) DEFAULT NULL,
`email` varchar(45) DEFAULT NULL,
`location` varchar(45) DEFAULT NULL,
`christian_name` varchar(45) DEFAULT NULL,
`ittle` varchar(45) DEFAULT NULL,
`copafilth_itemID` int(11) DEFAULT NULL,
`gmt_offset` int(4) DEFAULT NULL,
`personcol` varchar(45) DEFAULT NULL,
PRIMARY KEY (`personID`),
UNIQUE KEY `email_UNIQUE` (`email`),
KEY `fk_person_copafilth_item1_idx` (`copafilth_itemID`),
CONSTRAINT `fk_person_copafilth_item1` FOREIGN KEY (`copafilth_itemID`) REFERENCES `copafilth_item` (`copafilth_itemID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

In the form of scrum team I want to pick people from the person table and fill in the proper fields (scrum_masterID and product_ownerID

I hope this is clear and one of you is more brilliant than me :-)

Tomorrow I have a demo so help will be great!

I've tried this

$crud->set_relation("scrum_masterID", "scrum_master", "personID");

$crud->set_relation("personID", "person", "christian_name");

but this was no good since personID is not a field of the scrum_team table.

I have created a view that will help to see my DB structure:

create view vw_scrum_team as
    select st.scrum_teamID, st.scrum_masterID, st.product_ownerID, st.team_name, st.date_started,
    ps_sm.first_name as "Scrum master first name",
    ps_sm.christian_name as "Scrum master christian name" ,
    ps_po.first_name as "Product owner first name",
    ps_po.christian_name as "Product owner christian name"
    from scrum_team st
    left outer join scrum_master sm on st.scrum_masterID = sm.scrum_masterID
    left outer join product_owner po on st.product_ownerID = po.product_ownerID
    left outer join person ps_sm on ps_sm.personID = sm.personID

left outer join person ps_po on ps_po.personID = po.personID

In my datamodel I have multiple constructs like this one.

Another related issue I have is the following. In my Database I want all tables to have a parent relationship with it self. So every entry can be put in a hierarchy for this my table setup is like this:

CREATE TABLE IF NOT EXISTS `application` (
`applicationID` INT(11) NOT NULL,
`name` VARCHAR(255) NULL,
`parentID` INT(11) NULL,
PRIMARY KEY (`applicationID`),
CONSTRAINT `parentID`
    FOREIGN KEY (`parentID`)
    REFERENCES `application` (`applicationID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

On my javascript console is this when I add an application entry

jquery-1.11.1.min.js:4 POST http://127.0.0.1/ezbuild30/scrumba.php/sprint/application/insert 500 (Internal Server Error)

This is he code for application in my controller:

function application() {
    $table = "application";
    $subject = "Application";
    $crud = new grocery_CRUD();
    $crud->set_theme('datatables');
    $crud->set_table($table);
    $crud->set_subject($subject);

    $crud->edit_fields('name', 'parentID');
    $crud->columns('name', 'parentID');
    $crud->add_fields('name', 'parentID');

    $crud->display_as('name', 'Application');
    $crud->display_as('parentID', 'Belongs to application');

    $crud->set_relation("parentID", "application", "name");

    $output = $crud->render();
    $this->_scrumba_output($output);
}

Many thanks in advance.

John

John Ophof