Jump to content


Athar

Member Since 12 Jan 2017
Offline Last Active Feb 06 2017 03:15 PM
-----

Posts I've Made

In Topic: How display in date format in the read view

25 January 2017 - 02:26 PM

try this:

http://www.grocerycr...e-time-formats/


In Topic: What am I missing here?

24 January 2017 - 01:49 PM

Agreed: Views won't allow you to operate CRUD, however still can be handy sometimes.

 

I got it worked though. Simply put, for SQL VIEWS, you have to have a PK listed in the query. and then set the: 

$crud->set_table('HRLIST);
$crud->set_primary_key('ID);

15ev5ll.png


In Topic: Database Error \ SQL Syntax Error

18 January 2017 - 07:36 PM

There could be many reasons, need some detail to help to put you on the right path EFFECTIVELY. 


In Topic: How to show third table data

18 January 2017 - 04:55 PM

@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 ?
 
v8o8eg.png
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:

n5sswm.png

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!


In Topic: JOINING Three (or more) Tables in Grocery CRUD

18 January 2017 - 03:44 PM

@Amit, Thanks a lot. 

 
You actually did help clearing up some smoke I had in my coconut. I was under the impression that it was me who was dropping the ball somewhere. Your proposals do make sense and worth to explore further. However, during the process of trial and run, I discovered something that may intrigue others. 
2u8cl0y.png
If the table in the middle layer has a Composite KEY consisting of PK and FK from the 1st table, we CAN pull the value from 1st table in the list of 3rd table. I was successfully able to do that.
 
However, that lead me to hitch. It does not retain the dependency in third table. or at least its not logical in some scenerios. 
 
Whats your say on this?