SHARE
TWEET

Untitled

a guest Jun 24th, 2019 91 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2. require_once ("MySQLDB.php");
  3. include("connect.php");
  4.  
  5. $host = 'localhost';
  6. $dbUser = 'root';
  7. $dbPass = '';
  8. $dbName = 'ScoreboardDatabase';
  9.  
  10.  
  11. // create a new empty database
  12. $dbErrorHandling = new DBErrorHandling($host , $dbUser , $dbPass , $dbName );
  13. $dbErrorHandling->createDatabase();
  14. $dbErrorHandling->selectDatabase();
  15.  
  16. $sql = "drop table if exists leaderBoardGaming";
  17. $result = $dbErrorHandling->query($sql);
  18.  
  19. $sql = "drop table if exists userActive";
  20. $result = $dbErrorHandling->query($sql);
  21.  
  22. $sql = "drop table if exists userRegistry";
  23. $result = $dbErrorHandling->query($sql);
  24.  
  25. $sql = "drop procedure if exists passwordVerify";
  26. $result = $dbErrorHandling->query($sql);
  27. // create tables
  28. $table="userRegistry";
  29. $sql = "CREATE TABLE `ScoreboardDatabase`.`userRegistry` (
  30.   `userID` INT NOT NULL AUTO_INCREMENT,
  31.   `userName` VARCHAR(45) NOT NULL,
  32.   `userEmail` VARCHAR(45) NOT NULL,
  33.   `userPassword` VARCHAR(255) NOT NULL,
  34.   `userIDFriends` INT NULL,
  35.   `userCountry` VARCHAR(45) NOT NULL,
  36.   PRIMARY KEY (`userID`));"  ;
  37. $dbErrorHandling->createTable($table, $sql);
  38.  
  39. $table="userActiveTable";
  40. $sql = "CREATE TABLE `ScoreboardDatabase`.`userActive` (
  41.   `activeID` INT NOT NULL AUTO_INCREMENT,
  42.   `userID` INT NULL,
  43.   PRIMARY KEY (`activeID`),
  44.   UNIQUE INDEX `userID_UNIQUE` (`userID`),
  45.   CONSTRAINT `userID`
  46.     FOREIGN KEY (`userID`)
  47.     REFERENCES `scoreboarddatabase`.`userregistry` (`userID`)
  48.     ON DELETE NO ACTION
  49.     ON UPDATE NO ACTION);"  ;
  50. $dbErrorHandling->createTable($table, $sql);
  51.  
  52. $table="leaderBoardGamingTable";
  53. $sql = "CREATE TABLE `ScoreboardDatabase`.`leaderBoardGaming` (
  54.   `leaderBoardID` INT NOT NULL AUTO_INCREMENT,
  55.   `userIDL` INT NOT NULL,
  56.   `userWin` INT NULL,
  57.   `userLoss` INT NULL,
  58.   `userScore` INT NULL,
  59.   PRIMARY KEY (`leaderBoardID`),
  60.   UNIQUE INDEX `userID_LeaderBoardGaming` (`userIDL`),
  61.   CONSTRAINT `userIDL`
  62.     FOREIGN KEY (`userIDL`)
  63.     REFERENCES `scoreboarddatabase`.`userregistry` (`userID`)
  64.     ON DELETE NO ACTION
  65.     ON UPDATE NO ACTION);"  ;
  66. $dbErrorHandling->createTable($table, $sql);
  67.  
  68. // Hash passwords
  69. $clementsHashedPassword = password_hash('clementspassword123', PASSWORD_DEFAULT);
  70. $johnsHashedPassword = password_hash('Pa$$w0rd', PASSWORD_DEFAULT);
  71.  
  72. // insert data
  73. $sql = "INSERT INTO `userRegistry` (`userName`, `userEmail`, `userPassword`,  `userIDFriends`, `userCountry`)
  74. VALUES ('Clement', 'Testmail.com', '$clementsHashedPassword', '2', 'New Zealand'), ('John', 'Testmail.com', '$johnsHashedPassword', '1', 'America');";
  75. $dbErrorHandling->insertRow($sql);
  76.  
  77. $sql = "INSERT INTO `leaderBoardGaming` (`userIDL`, `userWin`, `userLoss`, `userScore`)
  78. VALUES ('1', '2', '1', '1'), ('2', '3', '1', '2');";
  79.  
  80. $dbErrorHandling->insertRow($sql);
  81.  
  82. // insert procedure
  83. $dbErrorHandling->query("CREATE PROCEDURE `passwordVerify` (IN `name` VARCHAR(45), IN `password` VARCHAR(45), OUT `passwordVerification` BOOLEAN)
  84. BEGIN
  85. SELECT IF(`userRegistry`.`userPassword` = `password`, TRUE, FALSE) AS 'verify'
  86. FROM `userRegistry`
  87. WHERE `userName` = `name`;
  88. END;");
  89.  
  90. // insert triggers
  91. $sql = "CREATE TRIGGER `ScoreboardDatabase`.`leaderBoardGaming_BEFORE_INSERT` BEFORE INSERT ON `leaderBoardGaming` FOR EACH ROW
  92. BEGIN
  93.     SET NEW.userScore = NEW.userWin / NEW.userLoss;
  94. END;";
  95.  
  96. $dbErrorHandling->insertRow($sql);
  97.  
  98. $sql = "CREATE TRIGGER `ScoreboardDatabase`.`leaderBoardGaming_BEFORE_UPDATE` BEFORE UPDATE ON `leaderBoardGaming` FOR EACH ROW
  99. BEGIN
  100.     SET NEW.userScore = NEW.userWin / NEW.userLoss;
  101. END;";
  102.  
  103. $dbErrorHandling->insertRow($sql);
  104. ?>
  105. <br><br>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top