I am quiet new to grocery crud for CI and I wanted to use it for my latest project that I am working on, however I am having some issues in grasping the knowledge on
set_relation_n_n
function.
Below is my ERD and I would like to add multiple Classes (crn) per Student ID as a student can be enrolled in many classes.
I did try to use
set_relation_n_n
function, however, I was not able to save when trying to add record. Below is the code and the result I get:
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); class Main extends CI_Controller { function __construct() { parent::__construct(); /* Standard Libraries of codeigniter are required */ $this->load->database(); $this->load->helper('url'); /* ------------------ */ $this->load->library('grocery_CRUD'); } public function index() { echo "<h1>Login Form Goes here</h1>";//Just an example to ensure that we get into the function die(); } public function registration() { $this->grocery_crud->set_theme('datatables'); $this->grocery_crud->set_table('reg') ->set_relation('stuid','student','{fname} {lname}') ->set_relation_n_n('class', 'reg','class', 'stuid', 'crn', '{year} {form} {sbjcode}','crn'); $this->grocery_crud->unset_columns('crn')->fields('stuid','class'); $output = $this->grocery_crud->render(); $this->_example_output($output); } function _example_output($output = null) { $this->load->view('main.php',$output); } } /* End of file Main.php */ /* Location: ./application/controllers/Main.php */
The Result Output
Below is also the SQL for the project:
-- ----------------------------------------------------- -- Schema schassess -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `schassess` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ; USE `schassess` ; -- ----------------------------------------------------- -- Table `schassess`.`student` -- ----------------------------------------------------- DROP TABLE IF EXISTS `schassess`.`student` ; CREATE TABLE IF NOT EXISTS `schassess`.`student` ( `stuid` INT NOT NULL AUTO_INCREMENT, `fname` VARCHAR(100) NULL, `lname` VARCHAR(100) NULL, PRIMARY KEY (`stuid`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `schassess`.`subject` -- ----------------------------------------------------- DROP TABLE IF EXISTS `schassess`.`subject` ; CREATE TABLE IF NOT EXISTS `schassess`.`subject` ( `sbjcode` VARCHAR(5) NOT NULL, `sbjdesc` VARCHAR(45) NOT NULL, PRIMARY KEY (`sbjcode`)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `schassess`.`class` -- ----------------------------------------------------- DROP TABLE IF EXISTS `schassess`.`class` ; CREATE TABLE IF NOT EXISTS `schassess`.`class` ( `crn` INT NOT NULL AUTO_INCREMENT, `year` VARCHAR(4) NOT NULL, `form` VARCHAR(45) NOT NULL, `sbjcode` VARCHAR(5) NOT NULL, PRIMARY KEY (`crn`), INDEX `fk_class_subject_idx` (`sbjcode` ASC), CONSTRAINT `fk_class_subject` FOREIGN KEY (`sbjcode`) REFERENCES `schassess`.`subject` (`sbjcode`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `schassess`.`assessment` -- ----------------------------------------------------- DROP TABLE IF EXISTS `schassess`.`assessment` ; CREATE TABLE IF NOT EXISTS `schassess`.`assessment` ( `a_id` INT NOT NULL AUTO_INCREMENT, `a_desc` VARCHAR(100) NOT NULL, `a_maxmark` INT(3) NOT NULL DEFAULT 0, `crn` INT NOT NULL, PRIMARY KEY (`a_id`), INDEX `fk_assessment_class1_idx` (`crn` ASC), CONSTRAINT `fk_assessment_class1` FOREIGN KEY (`crn`) REFERENCES `schassess`.`class` (`crn`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `schassess`.`reg` -- ----------------------------------------------------- DROP TABLE IF EXISTS `schassess`.`reg` ; CREATE TABLE IF NOT EXISTS `schassess`.`reg` ( `idreg` INT NOT NULL AUTO_INCREMENT, `stuid` INT NOT NULL, `crn` INT NOT NULL, PRIMARY KEY (`idreg`), INDEX `fk_reg_student1_idx` (`stuid` ASC), INDEX `fk_reg_class1_idx` (`crn` ASC), CONSTRAINT `fk_reg_student1` FOREIGN KEY (`stuid`) REFERENCES `schassess`.`student` (`stuid`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_reg_class1` FOREIGN KEY (`crn`) REFERENCES `schassess`.`class` (`crn`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `schassess`.`stuassess` -- ----------------------------------------------------- DROP TABLE IF EXISTS `schassess`.`stuassess` ; CREATE TABLE IF NOT EXISTS `schassess`.`stuassess` ( `stuassessid` INT NOT NULL AUTO_INCREMENT, `mark` INT(3) NOT NULL DEFAULT 0, `abs` BINARY NULL DEFAULT 0, `idreg` INT NOT NULL, `a_id` INT NOT NULL, PRIMARY KEY (`stuassessid`), INDEX `fk_stuassess_reg1_idx` (`idreg` ASC), INDEX `fk_stuassess_assessment1_idx` (`a_id` ASC), CONSTRAINT `fk_stuassess_reg1` FOREIGN KEY (`idreg`) REFERENCES `schassess`.`reg` (`idreg`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_stuassess_assessment1` FOREIGN KEY (`a_id`) REFERENCES `schassess`.`assessment` (`a_id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; USE `schassess`; DELIMITER $ USE `schassess`$ DROP TRIGGER IF EXISTS `schassess`.`reg_AINS` $ USE `schassess`$ CREATE TRIGGER `reg_AINS` AFTER INSERT ON `reg` FOR EACH ROW begin insert into stuassess (idreg,a_id) SELECT (SELECT idreg FROM reg r WHERE r.crn = new.crn and r.stuid = new.stuid), a.a_id FROM `assessment` a WHERE a.crn = new.crn; end;$ DELIMITER ;
Thanks in advance.