Limiting Response by ID (MySQL / CakePHP)_问答_开发者

What is the best practice for handing the following sequence?

I have a table of many lessons (Private, Semi-Private, Group). These lessons have a set time range (IE. 6-11am) on any day of the week. Students can pick a lesson from an increment of 30 minutes from the set time range (Private Lesson on Monday @ 9:00-9:30am)

I am using CakePHP and have setup most of the tables but I am getting lost within the relationships.

Basically, How do i limit the results of the time and length based upon the type of lesson there is?

Im still scaffolding.. .and im not sure if there is a way to limit the results whilst in scaffolding mode :)

-- -----------------------------------------------------
-- Table `test_db`.`address`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`address` (
  `addressline1` VARCHAR(45) NOT NULL ,
  `addressline2` VARCHAR(45) NULL DEFAULT NULL ,
  `city` VARCHAR(45) NOT NULL ,
  `state_id` INT(2) NOT NULL ,
  `zipcode` MEDIUMINT(9) NOT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `state_id` (`state_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`groups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`groups` (
  `name` VARCHAR(10) NOT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `roletype_UNIQUE` (`name` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`lesson_type`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`lesson_type` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `type_id` SMALLINT(6) NOT NULL ,
  `length` VARCHAR(10) NOT NULL ,
  `cost` INT(11) NOT NULL ,
  `maxstudent` TINYINT(4) NOT NULL ,
  `location_id` INT(11) NOT NULL ,
  `typename` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `ID` (`id` ASC) ,
  UNIQUE INDEX `typename_UNIQUE` (`typename` ASC) ,
  INDEX `LessonCost` (`cost` ASC) ,
  INDEX `LessonLocation` (`location_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`lessons`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`lessons` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `Student_id` INT(11) NOT NULL ,
  `weekday_id` TINYINT(4) NOT NULL ,
  `Time_id` INT(11) NOT NULL ,
  `trainer_id` INT(11) NOT NULL ,
  `Notes` VARCHAR(250) NULL DEFAULT NULL ,
  `lessontype_id` INT(11) NOT NULL ,
  `transaction_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `type_id` (`lessontype_id` ASC) ,
  INDEX `transfer_id_UNIQUE` (`transaction_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 17
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`lgroups`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`lgroups` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `type` CHAR(15) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `type_UNIQUE` (`type` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1
COMMENT = '     ' ;


-- -----------------------------------------------------
-- Table `test_db`.`location`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`location` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `locationname` VARCHAR(45) NOT NULL ,
  `locationaddress1` VARCHAR(80) NOT NULL ,
  `locationaddress2` VARCHAR(80) NULL DEFAULT NULL ,
  `locationcity` VARCHAR(45) NOT NULL ,
  `locationZip` VARCHAR(5) NOT NULL ,
  `locationphone` VARCHAR(12) NOT NULL ,
  `state_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `state_id` (`state_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`lochours`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`lochours` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `location_id` INT(3) NOT NULL ,
  `ltype_id` INT(3) NOT NULL ,
  `isMonday` TINYINT(1) NULL DEFAULT '0' ,
  `isTuesday` TINYINT(1) NULL DEFAULT '0' ,
  `isWednesday` TINYINT(1) NULL DEFAULT '0' ,
  `isThursday` TINYINT(1) NULL DEFAULT '0' ,
  `isFriday` TINYINT(1) NULL DEFAULT '0' ,
  `isSaturday` TINYINT(1) NULL DEFAULT '0' ,
  `isSunday` TINYINT(1) NULL DEFAULT '0' ,
  `to_id` INT(3) NOT NULL ,
  `from_id` INT(3) NOT NULL ,
  `hours` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `location_id_UNIQUE` (`location_id` ASC) ,
  UNIQUE INDEX `ltype_id_UNIQUE` (`ltype_id` ASC) ,
  UNIQUE INDEX `locationhours` (`hours` ASC) ,
  INDEX `starttime` (`to_id` ASC) ,
  INDEX `endtime` (`from_id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`members`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`members` (
  `firstname` VARCHAR(60) NOT NULL ,
  `lastname` VARCHAR(60) NOT NULL ,
  `address_id` INT(11) NOT NULL ,
  `phone1` VARCHAR(45) NOT NULL ,
  `phone2` VARCHAR(45) NULL DEFAULT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `user_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) ,
  UNIQUE INDEX `address_id_UNIQUE` (`address_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 7
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`state`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`state` (
  `id` SMALLINT(5) NOT NULL AUTO_INCREMENT COMMENT 'PK: Unique state ID' ,
  `state` VARCHAR(32) NOT NULL COMMENT 'State name with first letter capital' ,
  `state_abbr` VARCHAR(8) NULL DEFAULT NULL COMMENT 'Optional state abbreviation (US is 2 capital letters)' ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_unique` (`id` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 52
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`students`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`students` (
  `member_id` INT(11) NOT NULL ,
  `firstname` VARCHAR(60) NOT NULL ,
  `lastname` VARCHAR(60) NOT NULL ,
  `nickname` VARCHAR(45) NULL DEFAULT NULL ,
  `birthday` DATE NOT NULL ,
  `gender` VARCHAR(15) NOT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 4
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`time`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`time` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `timeslot` TIME NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `timeslot_UNIQUE` (`timeslot` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 35
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`trainer`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`trainer` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `trainer_firstname` VARCHAR(45) NOT NULL ,
  `trainer_lastname` VARCHAR(45) NOT NULL ,
  `trainer_email` VARCHAR(45) NOT NULL ,
  `trainer_phone` VARCHAR(45) NOT NULL ,
  `address_id` INT(11) NOT NULL ,
  `user_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `user_id_UNIQUE` (`user_id` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `address_id_UNIQUE` (`address_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`transaction_details`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`transaction_details` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `depositamount` INT(11) NOT NULL ,
  `depositdate` DATE NOT NULL ,
  `balance` INT(11) NULL DEFAULT NULL ,
  `paymenttype` VARCHAR(10) NOT NULL DEFAULT 'cash' ,
  `createdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
  `transaction_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `createdate_UNIQUE` (`createdate` ASC) ,
  UNIQUE INDEX `transaction_id_UNIQUE` (`transaction_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 2
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`transactions`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`transactions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `user_id` INT(11) NOT NULL ,
  `details_id` INT(11) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `details_id_UNIQUE` (`details_id` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  INDEX `user_id` (`user_id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 3
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`users` (
  `username` CHAR(45) NOT NULL ,
  `password` CHAR(45) NOT NULL ,
  `id` INT(11) NOT NULL AUTO_INCREMENT ,
  `email` VARCHAR(255) NOT NULL ,
  `group_id` INT(11) NOT NULL DEFAULT '1' ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `username_UNIQUE` (`username` ASC) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB
AUTO_INCREMENT = 11
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `test_db`.`weekday`
-- ------------------开发者_开发技巧-----------------------------------
CREATE  TABLE IF NOT EXISTS `test_db`.`weekday` (
  `id` TINYINT(4) NOT NULL AUTO_INCREMENT ,
  `day` CHAR(9) NOT NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) ,
  UNIQUE INDEX `day_UNIQUE` (`day` ASC) )
ENGINE = MyISAM
AUTO_INCREMENT = 8
DEFAULT CHARACTER SET = latin1;

If you want to change what is specifically displayed, you'll need to either modify a model, view, or controller. This can only be done after you have taken out the scaffolding. You can use "bake" from the command line to replace the scaffolding with editable code. There is a tutorial for using bake here:

Code-Generation-with-Bake