⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Grocery CRUD set_relation_n_n To add multiple rows of data



Avinesh

Avinesh
  • profile picture
  • Member

Posted 04 August 2016 - 21:05 PM

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. 

llZpU.png

 

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 

xhWdR.png

 

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.