How to show third table data

@Amit, Not sure if the poster is still interested in his Question. BUT anyways for others:

"I have a table called: User, that table are related with table city and city with table estate and state with country."

Question:

a- How do i show all those 3 ?

b- in a view ?

Step 1. Create Tables using this SQL Script:

- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`STATE`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`STATE` ;

CREATE TABLE IF NOT EXISTS `mydb`.`STATE` (
  `ID_STATE` INT NOT NULL AUTO_INCREMENT,
  `ATTRIBUTES` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_STATE`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`CITY`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`CITY` ;

CREATE TABLE IF NOT EXISTS `mydb`.`CITY` (
  `ID_CITY` INT NOT NULL AUTO_INCREMENT,
  `ID_STATE` INT NOT NULL,
  `ATTRIBUTES` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_CITY`),
  INDEX `table2 [X] table1_idx` (`ID_STATE` ASC),
  CONSTRAINT `table2 [X] table1`
    FOREIGN KEY (`ID_STATE`)
    REFERENCES `mydb`.`STATE` (`ID_STATE`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`USER`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`USER` ;

CREATE TABLE IF NOT EXISTS `mydb`.`USER` (
  `ID_USER` INT NOT NULL AUTO_INCREMENT,
  `ID_CITY` INT NOT NULL,
  `ATTRIBUTES` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_USER`),
  INDEX `table3 [X] table21_idx` (`ID_CITY` ASC),
  CONSTRAINT `table3 [X] table21`
    FOREIGN KEY (`ID_CITY`)
    REFERENCES `mydb`.`CITY` (`ID_CITY`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Step 2. Insert Data using this script:

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `mydb`.`STATE`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`STATE` (`ID_STATE`, `ATTRIBUTES`) VALUES (1, 'DELHI');
INSERT INTO `mydb`.`STATE` (`ID_STATE`, `ATTRIBUTES`) VALUES (2, 'NEW YORK');
INSERT INTO `mydb`.`STATE` (`ID_STATE`, `ATTRIBUTES`) VALUES (3, 'Rio De Janeiro');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`CITY`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`CITY` (`ID_CITY`, `ID_STATE`, `ATTRIBUTES`) VALUES (1, 1, 'DELHI CITY');
INSERT INTO `mydb`.`CITY` (`ID_CITY`, `ID_STATE`, `ATTRIBUTES`) VALUES (2, 2, 'NEW YORK CITY');
INSERT INTO `mydb`.`CITY` (`ID_CITY`, `ID_STATE`, `ATTRIBUTES`) VALUES (3, 3, 'Rio De Janeiro');

COMMIT;


-- -----------------------------------------------------
-- Data for table `mydb`.`USER`
-- -----------------------------------------------------
START TRANSACTION;
USE `mydb`;
INSERT INTO `mydb`.`USER` (`ID_USER`, `ID_CITY`, `ATTRIBUTES`) VALUES (1, 3, 'ERICH');
INSERT INTO `mydb`.`USER` (`ID_USER`, `ID_CITY`, `ATTRIBUTES`) VALUES (2, 1, 'AMIT');
INSERT INTO `mydb`.`USER` (`ID_USER`, `ID_CITY`, `ATTRIBUTES`) VALUES (3, 2, 'ATHAR');

COMMIT;

Step 3. Create VIEW using this script:

CREATE VIEW `ALL_IN_ONE` AS
    SELECT 
        U.ATTRIBUTES AS USER,
        T.ATTRIBUTES AS CITY,
        S.ATTRIBUTES AS STATE
    FROM
        mydb.state S
            INNER JOIN
        city T ON s.id_state = T.ID_STATE
            INNER JOIN
        user U ON U.id_city = T.ID_city;

Step 4. Verify the view gives you want you're looking for:

Step 5. Use this SQL VIEW in your GC Controller. (DON'T GET CONFUSED WITH MVC VIEWS)

public function View()
{
	$crud = new grocery_CRUD();
	$crud->set_table('ALL_IN_ONE');
	$crud->set_subject('List of Users');
	//$crud->columns('',''); ---// you don't need this to have as columns are defined inside the VIEW
	//$crud->set_relation('','',''); ---// you don't need this to have as JOINS are inside the view
	$output = $crud->render();
	$this->_example_output($output);
}

Steps 1-4 should be done on database, and last is for GC. Hope this Helps!

Erich Casagrande

Athar

Amit Shah

Athar

Amit Shah