[eluser]ibnclaudius[/eluser]
I have two tables:
Code:
CREATE TABLE IF NOT EXISTS `schools` (
`id` INT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
`active` TINYINT(1) NOT NULL ,
`created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`) )
ENGINE = InnoDB;
Code:
CREATE TABLE IF NOT EXISTS `users_schools` (
`users_id` INT NOT NULL ,
`schools_id` INT NOT NULL ,
`active` TINYINT(1) NOT NULL ,
`created` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY (`users_id`, `schools_id`) ,
INDEX `fk_users_has_schools_schools1` (`schools_id` ASC) ,
INDEX `fk_users_has_schools_users1` (`users_id` ASC) ,
CONSTRAINT `fk_users_has_schools_users1`
FOREIGN KEY (`users_id` )
REFERENCES `meuboletim`.`users` (`id` )
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_users_has_schools_schools1`
FOREIGN KEY (`schools_id` )
REFERENCES `meuboletim`.`schools` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
I want to select every school from 'schools' where 'schools'.'id' = 'users_schools'.'schools_id' and 'users_schools'.'users_id' = $this->session->userdata('id')
I'm trying to do like this:
Code:
$query = $this->db->select('*')->from($this->schools_table)
->join($this->users_schools_table, $this->users_schools_table . 'users_id = ' . $this->session->userdata('id'))
->join($this->users_schools_table, $this->users_schools_table . 'schools_id = ' . $this->schools_table . 'id')
->get();
What am I doing wrong?