Bourdeau Strength

Create Table Statements

Create the Inquiry Form's Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingCoachingInquiryForm`( `pmkFormID` INT NOT NULL AUTO_INCREMENT, `fldName` VARCHAR(255) NOT NULL, `fldEmailAddress` VARCHAR(255) NOT NULL, `fldPhoneNumber` VARCHAR(25) NOT NULL, `fldInstagramUsername` VARCHAR(50) NOT NULL, `fldUnits` VARCHAR(15) NOT NULL, `fldAgeYears` INT NOT NULL, `fldHeight` VARCHAR(50) NOT NULL, `fldWeight` VARCHAR(50) NOT NULL, `fldPowerliftingCoaching` BOOLEAN NOT NULL, `fldHypertrophyCoaching` BOOLEAN NOT NULL, `fldTechniqueAnalysis` BOOLEAN NOT NULL, `fldMeetDayHandling` BOOLEAN NOT NULL, `fldCompetedBefore` BOOLEAN NOT NULL, `fldCoachedBefore` BOOLEAN NOT NULL, `fldSquatMax` INT NOT NULL, `fldBenchMax` INT NOT NULL, `fldDeadliftMax` INT NOT NULL, `fldPastInjuries` TEXT NOT NULL, `fldCurrentTraining` TEXT NOT NULL, `fldPastTraining` TEXT NOT NULL, `fldPastSports` TEXT NOT NULL, `fldAdditionalComments` TEXT NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkFormID`) ) ENGINE = InnoDB;

Create the User Account Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingUserAccount`( `pmkUserID` INT NOT NULL AUTO_INCREMENT, `fldUsername` VARCHAR(40) NOT NULL, `fldPassword` VARCHAR(400) NOT NULL, PRIMARY KEY(`pmkUserID`) ) ENGINE = InnoDB;

Create the User Info Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingUserInfo`( `pmkUserID` INT NOT NULL AUTO_INCREMENT, `fldName` VARCHAR(50) NOT NULL, `fldEmail` VARCHAR(75) NOT NULL, `fldPhoneNumber` VARCHAR(25) NOT NULL, PRIMARY KEY(`pmkUserID`), FOREIGN KEY(`pmkUserID`) REFERENCES `tblTrainingUserAccount`(`pmkUserID`) ) ENGINE = InnoDB;

Create the Muscles Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingMuscles`( `pmkMuscleID` INT NOT NULL AUTO_INCREMENT, `fldMuscleName` VARCHAR(40) NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkMuscleID`) ) ENGINE = InnoDB;

Create the Exercises Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingExercises`( `pmkExerciseID` INT NOT NULL AUTO_INCREMENT, `fldExerciseName` VARCHAR(100) NOT NULL, `fldLiftType` VARCHAR(40) NOT NULL, `fldExerciseNotes` VARCHAR(500) NOT NULL, `fldCreatedBy` VARCHAR(40) NOT NULL DEFAULT 'Coach', `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkExerciseID`) ) ENGINE = InnoDB;

Create the ExerciseMuscles Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingExerciseMuscles`( `fpkExerciseID` INT NOT NULL, `fpkMuscleID` INT NOT NULL, `fldPrimaryMuscle` BOOLEAN NOT NULL, `fldSecondaryMuscle` BOOLEAN NOT NULL, PRIMARY KEY(`fpkExerciseID`, `fpkMuscleID`), FOREIGN KEY(`fpkExerciseID`) REFERENCES `tblTrainingExercises`(`pmkExerciseID`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(`fpkMuscleID`) REFERENCES `tblTrainingMuscles`(`pmkMuscleID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Coaches Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingCoaches`( `pmkCoachID` INT NOT NULL AUTO_INCREMENT, `fnkUserID` INT NOT NULL, `fldCreatedBy` VARCHAR(50) NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkCoachID`), FOREIGN KEY(`fnkUserID`) REFERENCES `tblTrainingUserInfo`(`pmkUserID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Clients Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingClients`( `pmkClientID` INT NOT NULL AUTO_INCREMENT, `fnkUserID` INT NOT NULL, `fnkCoachID` INT NOT NULL , `fldCreatedBy` VARCHAR(50) NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkClientID`), FOREIGN KEY(`fnkUserID`) REFERENCES `tblTrainingUserInfo`(`pmkUserID`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(`fnkCoachID`) REFERENCES `tblTrainingCoaches`(`pmkCoachID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Admins Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingAdmins`( `pmkAdminID` INT NOT NULL AUTO_INCREMENT, `fnkUserID` INT NOT NULL, `fldCreatedBy` VARCHAR(50) NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkAdminID`), FOREIGN KEY(`fnkUserID`) REFERENCES `tblTrainingUserInfo`(`pmkUserID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Blocks Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingBlocks`( `pmkBlockID` INT NOT NULL AUTO_INCREMENT, `fnkCoachID` INT NOT NULL, `fnkClientID` INT NOT NULL, `fldBlockName` VARCHAR(100) NOT NULL, `fldBlockLengthWeeks` TINYINT NOT NULL, `fldDaysPerWeek` TINYINT NOT NULL, `fldStartDate` DATE NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkBlockID`), FOREIGN KEY(`fnkCoachID`) REFERENCES `tblTrainingCoaches`(`pmkCoachID`) ON DELETE NO ACTION ON UPDATE NO ACTION, FOREIGN KEY(`fnkClientID`) REFERENCES `tblTrainingClients`(`pmkClientID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = InnoDB;

Create the Workouts Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingWorkouts`( `pmkWorkoutID` INT NOT NULL AUTO_INCREMENT, `fnkBlockID` INT NOT NULL, `fldBlockWeek` TINYINT NOT NULL, `fldSequence` TINYINT NOT NULL, `fldWorkoutName` VARCHAR(50) NOT NULL, `fldDate` DATE NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkWorkoutID`), FOREIGN KEY(`fnkBlockID`) REFERENCES `tblTrainingBlocks`(`pmkBlockID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Prescribed Exercises Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingPrescribedExercises`( `pmkPrescribedExerciseID` INT NOT NULL AUTO_INCREMENT, `fnkExerciseID` INT NOT NULL, `fnkWorkoutID` INT NOT NULL, `fldSequence` TINYINT NOT NULL, `fldSets` TINYINT NOT NULL, `fldReps` TINYINT NOT NULL, `fldLoad` VARCHAR(25) NOT NULL, `fldCappedLoad` VARCHAR(25) NOT NULL, `fldNotes` VARCHAR(255) NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkPrescribedExerciseID`), FOREIGN KEY(`fnkExerciseID`) REFERENCES `tblTrainingExercises`(`pmkExerciseID`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(`fnkWorkoutID`) REFERENCES `tblTrainingWorkouts`(`pmkWorkoutID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Performed Sets Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingPerformedSets`( `pmkPerformedSetID` INT NOT NULL AUTO_INCREMENT, `fnkPrescribedExerciseID` INT NOT NULL, `fldSetNumber` INT NOT NULL, `fldUnits` VARCHAR(25) NOT NULL, `fldWeight` INT NOT NULL, `fldReps` TINYINT NOT NULL, `fldPerceivedLoad` VARCHAR(25) NOT NULL, `fldNotes` VARCHAR(255) NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkPerformedSetID`), FOREIGN KEY(`fnkPrescribedExerciseID`) REFERENCES `tblTrainingPrescribedExercises`(`pmkPrescribedExerciseID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Recovery Factors Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingRecoveryFactors`( `pmkRecoveryFactorsID` INT NOT NULL AUTO_INCREMENT, `fnkClientID` INT NOT NULL, `fldBodyweightUnits` VARCHAR(15) NOT NULL, `fldBodyweight` DOUBLE NOT NULL, `fldCalories` INT NOT NULL, `fldProtein` INT NOT NULL, `fldCarbs` INT NOT NULL, `fldFats` INT NOT NULL, `fldHoursSlept` DOUBLE NOT NULL, `fldNotes` VARCHAR(500) NOT NULL, `fldCreatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, `fldLastEdited` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(`pmkRecoveryFactorsID`), FOREIGN KEY(`fnkClientID`) REFERENCES `tblTrainingClients`(`pmkClientID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Forgot Password Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingForgotPassword`( `pmkTempPasswordID` INT NOT NULL AUTO_INCREMENT, `fnkUserID` INT NOT NULL, `fldTempPassword` VARCHAR(64) NOT NULL, PRIMARY KEY(`pmkTempPasswordID`), FOREIGN KEY(`fnkUserID`) REFERENCES `tblTrainingUserInfo`(`pmkUserID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Create the Forgot Username Table

CREATE TABLE `JBOURDE2_Final`.`tblTrainingForgotUsername`( `pmkTempUsernameID` INT NOT NULL AUTO_INCREMENT, `fnkUserID` INT NOT NULL, `fldTempUsername` VARCHAR(64) NOT NULL, PRIMARY KEY(`pmkTempUsernameID`), FOREIGN KEY(`fnkUserID`) REFERENCES `tblTrainingUserInfo`(`pmkUserID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB;

Other Statements

Select statement with join statement user in coachClientRoster.php

Note: This statement is used for paging a table and uses some local variables and constants (table names)

SELECT pmkUserID, pmkClientID, fnkCoachID, fldName, fldEmail, fldPhoneNumber FROM ' . INFO_TABLE . ' LEFT JOIN ' . CLIENTS_TABLE . ' ON pmkUserID = fnkUserID WHERE pmkUserID IN (SELECT fnkUserID FROM ' . CLIENTS_TABLE . ') AND fnkCoachID = ? ORDER BY fldName ' . $coachClientRosterSort . ' LIMIT ' . $displayNCoachClientRecords . ' OFFSET ' . $coachClientRosterStart;