PHP DatabaseManager::getConnection方法代碼示例

 public static function getTestCases($qId)
 {
     $db = DatabaseManager::getConnection();
     $query = 'SELECT inputCase,outputCase,isSample FROM TestCases WHERE qid=:qid';
     $bindings = array('qid' => $qId);
     return $db->select($query, $bindings);
 }

 public static function viewScoreboard($questionId)
 {
     $db = DatabaseManager::getConnection();
     $query = 'SELECT Scoreboard.status as Status,UserDetails.Name as Name
               FROM Scoreboard join UserDetails
               ON Scoreboard.UserId = UserDetails.UserId
               where Scoreboard.questionId=:qid';
     $bindings = array('qid' => $questionId);
     return $db->select($query, $bindings);
 }

 public static function disconnectServiceType($serviceType)
 {
     try {
         $query = "DELETE\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tWHERE `" . self::DB_COLUMN_SERVICE_TYPE . "` = :service_type";
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         $query->bindParam(':service_type', $serviceType, PDO::PARAM_STR);
         $query->execute();
         return true;
     } catch (Exception $e) {
         Mailer::sendDevelopers($e->getMessage(), __FILE__);
         throw new Exception("Could not access database. <br/>Please try again.");
     }
 }

 /**
  * @param $db
  * @throws Exception
  */
 public static function retrieve()
 {
     $query = "SELECT id, email, f_name, l_name, mobile, ci, credits\n\t\t         FROM `" . App::getDbName() . "`.student";
     try {
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         $query->execute();
         $rows = $query->fetchAll(PDO::FETCH_ASSOC);
         return $rows;
     } catch (PDOException $e) {
         throw new Exception("Something terrible happened. Could not retrieve students data from database.: ");
     }
     // end catch
 }

 public static function login($useremail, $password)
 {
     $db = DatabaseManager::getConnection();
     $queryString = 'SELECT * FROM UserDetails WHERE EmailId = :useremail AND Password = :password';
     $bindings = array('useremail' => $useremail, 'password' => $password);
     $result = $db->select($queryString, $bindings);
     if ($result != false) {
         $_SESSION['username'] = $result[0]['Name'];
         $_SESSION['emailid'] = $result[0]['EmailId'];
         $_SESSION['department'] = $result[0]['Department'];
         $_SESSION['userid'] = $result[0]['UserId'];
         self::setUserType($result[0]['Type']);
         return isset($_SESSION['username']);
     }
     return false;
 }

 public static function updateMailSent()
 {
     date_default_timezone_set('Europe/Athens');
     $dateNow = new DateTime();
     $dateNow = $dateNow->format(Dates::DATE_FORMAT_IN);
     try {
         $query = "INSERT INTO `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\t\tVALUES(\n\t\t\t\t\t:now\n\t\t\t\t)";
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         $query->bindParam(':now', $dateNow, PDO::PARAM_STR);
         $query->execute();
         return true;
     } catch (Exception $e) {
         throw new Exception("Could not data into database.");
     }
 }

 public static function retrieveCurrTermAllTeachingCourses()
 {
     $query = "SELECT `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_FIRST_NAME . "`,\n\t\t\t\t\t\t `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_LAST_NAME . "`,\n\t\t\t\t\t\t `" . CourseFetcher::DB_TABLE . "`.`" . CourseFetcher::DB_COLUMN_CODE . "`,\n\t\t\t\t\t\t `" . CourseFetcher::DB_TABLE . "`.`" . CourseFetcher::DB_COLUMN_NAME . "`,\n\t\t\t\t\t\t `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_NAME . "` AS\n\t\t\t\t\t\t" . TermFetcher::DB_TABLE . "_" . TermFetcher::DB_COLUMN_NAME . "\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tINNER JOIN `" . App::getDbName() . "`.`" . UserFetcher::DB_TABLE . "`\n\t\t\t\tON `" . Tutor_has_course_has_termFetcher::DB_TABLE . "`.`" . Tutor_has_course_has_termFetcher::DB_COLUMN_TUTOR_USER_ID . "` = `" . UserFetcher::DB_TABLE . "`.`" . UserFetcher::DB_COLUMN_ID . "`\n\t\t\tINNER JOIN `" . TermFetcher::DB_TABLE . "`\n\t\t\t\tON `" . Tutor_has_course_has_termFetcher::DB_TABLE . "`.`" . Tutor_has_course_has_termFetcher::DB_COLUMN_TERM_ID . "` = `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_ID . "`\n\t\t\tINNER JOIN `" . CourseFetcher::DB_TABLE . "`\n\t\t\t\tON `" . Tutor_has_course_has_termFetcher::DB_TABLE . "`.`" . Tutor_has_course_has_termFetcher::DB_COLUMN_COURSE_ID . "` = `" . CourseFetcher::DB_TABLE . "`.`" . CourseFetcher::DB_COLUMN_ID . "`\n\t\t\tWHERE (:now BETWEEN `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_START_DATE . "` AND `" . TermFetcher::DB_TABLE . "`.`" . TermFetcher::DB_COLUMN_END_DATE . "`)";
     try {
         date_default_timezone_set('Europe/Athens');
         $now = new DateTime();
         $now = $now->format(Dates::DATE_FORMAT_IN);
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         $query->bindParam(':now', $now, PDO::PARAM_STR);
         $query->execute();
         return $query->fetchAll(PDO::FETCH_ASSOC);
     } catch (PDOException $e) {
         Mailer::sendDevelopers($e->getMessage(), __FILE__);
         throw new Exception("Could not retrieve teaching courses from current terms from database.");
     }
 }

 /**
  * Returns a single column from the next row of a result set or FALSE if there are no more rows.
  *
  * @param $what
  * @param $field
  * @param $value
  * @param $id
  * @return mixed
  * @throws Exception
  */
 public function updateInfo($what, $field, $value, $id)
 {
     // I have only added few, but you can add more. However do not add 'password' even though the parameters will only be given by you and not the user, in our system.
     $allowed = ['id', 'username', 'f_name', 'l_name', 'email', 'COUNT(mobile)', 'mobile', 'user', 'gen_string', 'COUNT(gen_string)', 'COUNT(id)', 'img_loc'];
     if (!in_array($what, $allowed, true) || !in_array($field, $allowed, true)) {
         throw new InvalidArgumentException();
     } else {
         try {
             $query = "UPDATE `" . App::getDbName() . "`.`" . $field . "` SET `{$what}` = ? WHERE `id`= ?";
             $dbConnection = DatabaseManager::getConnection();
             $query = $dbConnection->prepare($query);
             $query->bindValue(1, $value, PDO::PARAM_STR);
             $query->bindValue(2, $id, PDO::PARAM_INT);
             $query->execute();
             return true;
         } catch (Exception $e) {
             throw new Exception($e->getMessage());
         }
     }
 }

 /**
  * NEEDS TESTING
  * @param $dateStart
  * @param $dateEnd
  * @param $tutorId
  * @throws Exception
  * @internal param $db
  * @return bool
  */
 public static function existDatesBetween($dateStart, $dateEnd, $tutorId)
 {
     date_default_timezone_set('Europe/Athens');
     $dateStart = $dateStart->format(Dates::DATE_FORMAT_IN);
     $dateEnd = $dateEnd->format(Dates::DATE_FORMAT_IN);
     $query = "SELECT COUNT(`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_ID . "`),`" . CourseFetcher::DB_TABLE . "`\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n\t\t\tWHERE `" . self::DB_COLUMN_TUTOR_USER_ID . "` = :tutor_id\n\t\t\tAND(`" . self::DB_TABLE . "`.`" . self::DB_COLUMN_START_TIME . "`  BETWEEN {$dateStart} AND {$dateEnd})";
     try {
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         $query->bindParam(':tutor_id', $tutorId, PDO::PARAM_INT);
         $query->execute();
         if ($query->fetchColumn() === '0') {
             return false;
         }
         return $query->fetchAll(PDO::FETCH_ASSOC);
     } catch (PDOException $e) {
         Mailer::sendDevelopers($e->getMessage(), __FILE__);
         throw new Exception("Could not retrieve teaching courses data from database.");
     }
     return true;
 }

 public static function existsUserId($id)
 {
     try {
         $sql = "SELECT COUNT(" . self::DB_COLUMN_USER_ID . ") FROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "` WHERE `" . self::DB_COLUMN_USER_ID . "` = :user_id";
         $dbConnection = DatabaseManager::getConnection();
         $dbConnection = $dbConnection->prepare($sql);
         $dbConnection->bindParam(':user_id', $id, PDO::PARAM_INT);
         $dbConnection->execute();
         if ($dbConnection->fetchColumn() === '0') {
             return false;
         }
     } catch (Exception $e) {
         Mailer::sendDevelopers($e->getMessage(), __FILE__);
         throw new Exception("Could not check if tutor id already exists on database.");
     }
     return true;
 }

 public static function delete($id)
 {
     try {
         $query = "DELETE FROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "` WHERE `" . self::DB_COLUMN_ID . "` = :id";
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         $query->bindParam(':id', $id, PDO::PARAM_INT);
         $query->execute();
         return true;
     } catch (Exception $e) {
         Mailer::sendDevelopers($e->getMessage(), __FILE__);
         throw new Exception("Could not delete instructor from database.");
     }
 }

 public static function countForTermIds($termIds, $labels = [])
 {
     if (empty($labels)) {
         $labels = self::$labels;
     }
     foreach ($termIds as $key => $termId) {
         $termBindParams[] = ':term_id_' . $key;
     }
     $termBindParams = implode(', ', $termBindParams);
     $labelBindParams = "'" . implode("', '", $labels) . "'";
     $query = "SELECT COUNT(" . self::DB_COLUMN_ID . ") AS total\n\t\t\tFROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "`\n            WHERE `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_TERM_ID . "` in ({$termBindParams})\n            AND `" . self::DB_TABLE . "`.`" . self::DB_COLUMN_LABEL_MESSAGE . "` in ({$labelBindParams})";
     try {
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         foreach ($termIds as $key => $termId) {
             $query->bindValue(":term_id_{$key}", $termId, PDO::PARAM_INT);
         }
         $query->execute();
         return $query->fetch(PDO::FETCH_ASSOC)['total'];
     } catch (PDOException $e) {
         Mailer::sendDevelopers($e->getMessage(), __FILE__);
         throw new Exception("Could not retrieve data from database.");
     }
 }

 public function deleteTeachingCourse($courseId)
 {
     if (!preg_match('/^[0-9]+$/', $courseId)) {
         throw new Exception("Data tempering detected.\n\t\t\t<br/>You&#39;re trying to hack this app.<br/>Developers are being notified about this.<br/>Expect Us.");
     }
     $tutorId = $this->getId();
     try {
         $query = "DELETE FROM `" . App::getDbName() . "`.`" . self::DB_TABLE_TUTOR_HAS_COURSE_HAS_TERM . "` WHERE `tutor_user_id`=:id AND`course_id`=:courseId;";
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         $query->bindParam(':id', $tutorId, PDO::PARAM_INT);
         $query->bindParam(':courseId', $courseId, PDO::PARAM_INT);
         $query->execute();
         return true;
     } catch (Exception $e) {
         throw new Exception("Could not delete course from database.");
     }
 }

 /**
  *
  * @param string $group The database group to connect to
  */
 function __construct($connection = 'default')
 {
     Console::debugEx(LOG_DEBUG1, __CLASS__, "Initializing connection for %s.", $connection);
     $this->conn = DatabaseManager::getConnection($connection);
 }

 public static function existsName($name)
 {
     try {
         $query = "SELECT COUNT(" . self::DB_COLUMN_NAME . ") FROM `" . App::getDbName() . "`.`" . self::DB_TABLE . "` WHERE `" . self::DB_COLUMN_NAME . "` = :name";
         $dbConnection = DatabaseManager::getConnection();
         $query = $dbConnection->prepare($query);
         $query->bindParam(':name', $name, PDO::PARAM_STR);
         $query->execute();
         if ($query->fetchColumn() === '0') {
             return false;
         }
     } catch (Exception $e) {
         Mailer::sendDevelopers($e->getMessage(), __FILE__);
         throw new Exception("Could not check if term name already exists on database. <br/> Aborting process.");
     }
     return true;
 }