private static function clearTables(\Core\Database $db) { $clearScript = new \Helpers\TablesClearScript(); foreach ($clearScript as $statement) { $db->exec($statement, array()); } }
/** * @param float $latitude * @param float $longitude * @param $radio */ public static function getRepublicas($latitude, $longitude, $radius, Database &$database) { $latitude = filter_var($latitude, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION); $longitude = filter_var($longitude, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION); $radius = filter_var($radius, FILTER_SANITIZE_NUMBER_FLOAT, FILTER_FLAG_ALLOW_FRACTION); //Haversine formula $query = $database->prepare(' SELECT *, (6371 * acos( cos(radians( :latitude )) * cos(radians(latitude)) * cos(radians(longitude) - radians( :longitude )) + sin(radians( :latitude )) * sin(radians(latitude)) )) AS distance FROM republicas HAVING distance < :radius ORDER BY distance '); $query->bindParam(':latitude', $latitude); $query->bindParam(':longitude', $longitude); $query->bindParam(':radius', $radius); $query->execute(); $json = array(); while ($item = $query->fetch(Database::FETCH_ASSOC)) { $json[] = $item; } return json_encode($json); }
/** * Static method get * * @param array $group * @return \helpers\database */ public static function get($group = null) { // Determining if exists or it's not empty, then use default group defined in config $group = !$group ? array('type' => DB_TYPE, 'host' => DB_HOST, 'name' => DB_NAME, 'user' => DB_USER, 'pass' => DB_PASS, 'port' => DB_PORT) : $group; // Group information $type = $group['type']; $host = $group['host']; $name = $group['name']; $user = $group['user']; $pass = $group['pass']; $port = $group['port']; // ID for database based on the group information $id = "{$type}.{$host}.{$port}.{$name}.{$user}.{$pass}"; // Checking if the same if (isset(self::$instances[$id])) { return self::$instances[$id]; } try { // I've run into problem where // SET NAMES "UTF8" not working on some hostings. // Specifiying charset in DSN fixes the charset problem perfectly! $instance = new Database("{$type}:host={$host};port={$port};dbname={$name};charset=utf8", $user, $pass); $instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Setting Database into $instances to avoid duplication self::$instances[$id] = $instance; return $instance; } catch (PDOException $e) { //in the event of an error record the error to ErrorLog.html Logger::newMessage($e); Logger::customErrorMsg(); } }
public function getAll(\Core\Database $db) { $recs = $db->fetchAllAssoc("select * from rooms", array()); $result = array(); for ($i = 0; $i < count($recs); $i++) { $result[] = new \Application\RoomItem($recs[$i]); } return $result; }
protected function makeUpdateQuery($table_name, $fields_to_save, array $where_condition, \Core\Database $db) { $value_equation_list_imploded = $this->makeEquationString(',', $fields_to_save); $value_list = $this->makeValueVarArray($fields_to_save); $sql = "update {$table_name} set {$value_equation_list_imploded} where " . $this->makeEquationString(' and ', $where_condition); $value_list = array_merge($value_list, $this->makeValueVarArray($where_condition)); //error_log("\nSQL:" . print_r($sql, true) . "\nvalues:" . print_r($value_list, true), 3, "my_errors.txt"); return $db->exec($sql, $value_list); }
public static function getUserId($username, $password) { $result = Database::getInstance()->prepare("SELECT id FROM `users` where `username` = :username AND `password`=:password OR `email`=:username AND `password`=:password"); $result->execute(array(':username' => $username, ':password' => $password)); $row = $result->fetch(PDO::FETCH_ASSOC); return $row['id']; }
/** * Get a skin by payer name * @param $playerName String * @return Skin */ public static function getSkinByPlayerName($playerName) { $stn = DB::sql("SELECT * FROM mc_skin WHERE player_name=?"); $stn->bindValue(1, $playerName, DB::PARAM_STR); $stn->execute(); return $stn->fetchObject(__CLASS__); }
public static function getByUserId($userid) { $stm = DB::sql("SELECT * FROM `orders` WHERE `uid`=? AND status = 0"); $stm->bindValue(1, $userid, DB::PARAM_INT); $stm->execute(); return $stm->fetchAll(DB::FETCH_CLASS, __CLASS__); }
/** * Get use user count * @return int */ public static function GetUseUserCount() { $statement = Database::prepare("SELECT count(*) FROM member WHERE lastConnTime > 0"); $statement->execute(); $count = $statement->fetch(\PDO::FETCH_NUM); return $count[0] == null ? 0 : $count[0]; }
/** * Create a new instance of the database helper. */ public function __construct() { /** * connect to PDO here. */ $this->db = \Core\Database::get(); }
public static function queryUrl($url) { $stm = Database::sql('SELECT `id`, `alias`, `url`, `status`, `add_time`, `click_num` FROM `url_list` WHERE `url`=?'); $stm->bindValue(1, $url, Database::PARAM_STR); $stm->execute(); return $stm->fetchObject(__CLASS__); }
public static function getByTrade($trade) { $stm = DB::sql('SELECT id, trade, has_notify FROM trade WHERE trade=?'); $stm->bindValue(1, $trade); $stm->execute(); return $stm->fetchObject(__CLASS__); }
/** * Create a new PageData object. * @param string $tableName Target table name * @param string $extras Such as where statement or order statement * @param array $column Column names needs to be fetch */ public function __construct($tableName, $extras = '', $column = array('*')) { $columns = '`' . implode('`, `', $column) . '`'; $this->countQuery = Database::getInstance()->prepare("SELECT COUNT(*) FROM `{$tableName}` {$extras}"); $this->query = Database::getInstance()->prepare("SELECT {$columns} FROM `{$tableName}` {$extras} LIMIT :pageDataStart,:pageDataRPP"); if ($_GET['page']) { $this->setPage($_GET['page']); } }
public static function init() { $stn = DB::getInstance()->prepare("SELECT k, v FROM options"); $stn->execute(); $opt = $stn->fetchAll(DB::FETCH_UNIQUE | DB::FETCH_COLUMN); // $GLOBALS['OPTIONS'] = $opt; self::$list = $opt; return $opt; }
public function save($mode = self::SAVE_AUTO) { $map = array(); $reflection = new ReflectionObject($this); $reflectionProp = $reflection->getProperties(ReflectionProperty::IS_PROTECTED | ReflectionProperty::IS_PUBLIC); foreach ($reflectionProp as $property) { if (strpos($property->getDocComment(), '@ignore')) { continue; } $propertyName = $property->getName(); if ($propertyName == 'primaryKey') { continue; } if ($property->isProtected()) { $property->setAccessible(true); } $propertyValue = $property->getValue($this); $map[$propertyName] = $propertyValue; } $primaryKey = $this->getPrimaryKeyName($reflection); $identifier = $map[$primaryKey]; unset($map[$primaryKey]); $tableName = $this->getTableName($reflection); if ($mode == self::SAVE_UPDATE || $identifier && $mode != self::SAVE_INSERT) { $sql = "UPDATE `{$tableName}` SET "; foreach ($map as $key => $value) { $sql .= "`{$key}` = :{$key},"; } $sql = rtrim($sql, ','); $sql .= " WHERE {$primaryKey} = :id"; $statement = Database::getInstance()->prepare($sql); $statement->bindValue(':id', $identifier); foreach ($map as $key => $value) { $statement->bindValue(":{$key}", $value); } } else { $sql = "INSERT INTO `{$tableName}` SET "; foreach ($map as $key => $value) { $sql .= "`{$key}` = :{$key},"; } $sql = rtrim($sql, ','); $statement = Database::getInstance()->prepare($sql); foreach ($map as $key => $value) { $statement->bindValue(":{$key}", $value); } } $statement->execute(); if (!$identifier) { $insertId = Database::getInstance()->lastInsertId(); if ($insertId) { $reflection->getProperty($primaryKey)->setValue($this, $insertId); } } }
/** * @param (int|string)[] $answers * * @return boolean */ public static function save(array $answers, Database &$database) { $options = array('dificuldade' => FILTER_SANITIZE_STRING, 'explicacao_dificuldade' => FILTER_SANITIZE_STRING, 'encontrou' => FILTER_SANITIZE_STRING, 'aluno_EACH' => FILTER_SANITIZE_STRING, 'indicaria' => FILTER_SANITIZE_STRING, 'referencia' => FILTER_SANITIZE_STRING, 'nota_design' => FILTER_SANITIZE_NUMBER_INT, 'nota_funcionalidades' => FILTER_SANITIZE_NUMBER_INT, 'nota_acessibilidade' => FILTER_SANITIZE_NUMBER_INT, 'nota_insercao_reps' => FILTER_SANITIZE_NUMBER_INT, 'info_adicional' => FILTER_SANITIZE_STRING); $answers = filter_var_array($answers, $options); $query = $database->prepare(' INSERT INTO feedback ( dificuldade, explicacao_dificuldade, encontrou, aluno_EACH, indicaria, referencia, nota_design, nota_funcionalidades, nota_acessibilidade, nota_insercao_reps, info_adicional ) VALUES ( :dificuldade, :explicacao_dificuldade, :encontrou, :aluno_EACH, :indicaria, :referencia, :nota_design, :nota_funcionalidades, :nota_acessibilidade, :nota_insercao_reps, :info_adicional ) '); do { $query->bindParam(':' . key($answers), current($answers)); } while (next($answers) !== false); return $query->execute(); }
public function run() { // 清理一个月前的数据 $mon = time() - 2592000; $stn = Database::sql('DELETE FROM `card` WHERE add_time<? AND status=0'); $stn->bindValue(1, $mon, Database::PARAM_INT); $stn->execute(); $stn = Database::sql("DELETE FROM `invite` WHERE dateLine<? AND status=1"); $stn->bindValue(1, $mon, Database::PARAM_INT); $stn->execute(); }
/** * Migrate current database * @param $dropTable bool drop the table */ public function execute($dropTable = false) { $this->database = Database::getInstance(); $modelDir = "Application/Model"; $file = opendir($modelDir); // there is fileName while (($fileName = readdir($file)) !== false) { if (substr($fileName, -4) == ".php") { $this->migrateTable($modelDir . "/" . $fileName, $dropTable); } } }
public static function getById($id) { try { $connection = Database::instance(); $sql = "SELECT * from usuarios WHERE id = ?"; $query = $connection->prepare($sql); $query->bindParam(1, $id, \PDO::PARAM_INT); $query->execute(); return $query->fetch(); } catch (\PDOException $e) { print "Error!: " . $e->getMessage(); } }
public static function getUserPassword($user_name, $password) { try { $connection = Database::instance(); $sql = "SELECT * from usuarios WHERE nombre_usuario = ? AND clave_usuario = ?"; $query = $connection->prepare($sql); $query->bindParam(2, $user_name, $password, \PDO::PARAM_INT); $query->execute(); return $query->fetch(); } catch (\PDOException $e) { print "Error!: " . $e->getMessage(); } }
/** * @static * @param string $email * @param string $password * @param string $location URL you want to redirect user to * * @return boolean */ public static function login($email, $password, Database &$database) { $email = filter_var($email, FILTER_SANITIZE_EMAIL); $validEmail = (bool) filter_var($email, FILTER_VALIDATE_EMAIL); if (!$validEmail) { return false; } $query = $database->prepare(' SELECT id, password, salt FROM users WHERE email = :email '); $query->bindParam(':email', $email, Database::PARAM_STR); $query->execute(); $success = false; if ($query->rowCount() == 1) { $result = $query->fetch(Database::FETCH_ASSOC); $passwordHash = hash('sha512', $result['salt'] . $password); $success = $result['password'] == $passwordHash; if ($success) { $_SESSION['user_id'] = $result['id']; } } return $success; }
public static function setNextRun($cronId, $step) { $inTransaction = DB::getInstance()->inTransaction(); if (!$inTransaction) { DB::getInstance()->beginTransaction(); } $st = DB::sql("UPDATE cron SET nextrun=? WHERE id=?"); $st->bindValue(1, $step, DB::PARAM_INT); $st->bindValue(2, $cronId, DB::PARAM_STR); $st->execute(); if (!$inTransaction) { DB::getInstance()->commit(); } }
public function destroy() { $inTransaction = DB::getInstance()->inTransaction(); if (!$inTransaction) { DB::getInstance()->beginTransaction(); } $st = DB::sql("UPDATE card SET status=0 WHERE card=:card"); // 失效卡 $st->bindValue(":card", $this->card, DB::PARAM_STR); $flag = $st->execute(); if (!$inTransaction) { DB::getInstance()->commit(); } return $flag; }
public static function emailFind($email) { $db = Database::connect(); //TODO: Change to prepared statement if ($stmt = $db->query("SELECT id FROM Users WHERE email = '{$email}'")) { if ($data = $stmt->fetch(\PDO::FETCH_ASSOC)) { return new self($data["id"]); } else { //No user with that email return false; } } else { throw \Exception\DatabaseError::build($db); } }
/** * Execute a PDO prepare with execute * * @param $query * The query to execute * @param array $params * The query parameters * @return An array with the results */ public static function execute($query, $params = []) { // Preparing the query with the database instance $results = Database::getInstance()->prepare($query); // Executing the query with the given parameters $results->execute($params); // Fetching the results $results = $results->fetchAll(\PDO::FETCH_OBJ); // Checking them if (count($results) < 2) { $results = current($results); } // Returning them return $results; }
function validate($username, $password) { $session = Node::getOne(array(Node::FIELD_COLLECTION => FRAMEWORK_COLLECTION_SESSION, 'username' => Database::escapeValue($username))); $res = Session::validate($username, $password, $this->request()->fingerprint()); if (is_int($res)) { switch ($res) { case Session::ERR_MISMATCH: throw new ServiceException('Username and password mismatch.', $res); break; case Session::ERR_EXISTS: throw new ServiceException('Session already exists.', $res); break; } } return $res; }
public function update() { $inTransaction = Database::inTransaction(); if (!$inTransaction) { Database::beginTransaction(); } $statement = Database::prepare("UPDATE user_power SET `uid`=:uid WHERE id=:id"); $statement->bindValue(':uid', $this->uid, \PDO::PARAM_INT); $statement->bindValue(':id', $this->id, \PDO::PARAM_INT); $statement->execute(); $this->uid = Database::lastInsertId(); $statement->execute(); if (!$inTransaction) { Database::commit(); } }
public function delete() { $db = Database::connect(); if ($stmt = $db->prepare("DELETE FROM {$this->table} WHERE id=:id LIMIT 1;")) { $stmt->bindParam(":id", $this->id); if ($stmt->execute()) { foreach ($this->getPublicProperties() as $p) { $this->{$p} = null; } return true; } else { throw \Exception\DatabaseError::build($stmt); } } else { throw \Exception\DatabaseError::build($db); } }
public function run() { $resetDate = '1'; $date = date("d", time()); if ($date == $resetDate) { $inTransaction = DB::getInstance()->inTransaction(); if (!$inTransaction) { DB::getInstance()->beginTransaction(); } $st = DB::sql("UPDATE member SET flow_up=0, flow_down=0 WHERE `enable`=1 AND `plan`!='Z'"); $st->execute(); if (!$inTransaction) { DB::getInstance()->commit(); } } return false; }
/** * * @return array ["userCount", "checkCount", "connCount"] */ public static function getAnaCount() { $data = array(); // user count $selectSQL = "SELECT count(*) FROM member"; $statement = Database::prepare($selectSQL); $statement->execute(); $userCount = $statement->fetch(\PDO::FETCH_NUM); $data['userCount'] = $userCount[0]; // check user $statement = Database::prepare("SELECT count(*) FROM member WHERE lastCheckinTime > " . date('Y-m-d 00:00:00', time())); $statement->execute(); $checkCount = $statement->fetch(\PDO::FETCH_NUM); $data['checkCount'] = $checkCount[0]; $statement = Database::prepare("SELECT count(*) FROM member WHERE lastConnTime > " . time() - 600); $statement->execute(); $connCount = $statement->fetch(\PDO::FETCH_NUM); $data['connCount'] = $connCount[0]; return $data; }