PHP db::query方法代码示例

 /**
  * Send key to db
  */
 public function send_key_to_db()
 {
     if (!self::$flag_key_sent_to_db) {
         $db = new db($this->db_link);
         // todo: disable logging in db
         $db->query("SELECT set_config('sm.numbers.crypt.key', '" . $db->escape($this->key) . "', false)");
         $db->query("SELECT set_config('sm.numbers.crypt.options', '" . $db->escape($this->cipher) . "', false)");
         // todo: enable logging in db
         self::$flag_key_sent_to_db = true;
     }
     return true;
 }

 public function actionIndex()
 {
     $model = new Install();
     $file = base_path() . 'config/database.php';
     if (!is_writable($file)) {
         $data['error'] = __('config/database.php is not writable');
     }
     $model->host = "localhost";
     if ($model->load($_POST)) {
         if (!$data['error']) {
             $dsn = "mysql:host=" . trim($model->host) . ";dbname=" . trim($model->host_db);
             $username = trim($model->host_user);
             $model->host_pwd = $password = trim($_POST['Install']['host_pwd']);
             $db = new \db();
             $db->connect($dsn, $username, $password);
             if (!$db->id) {
                 $data['error'] = __('connect host failed');
             } else {
                 $content = \Yii::getAlias('@application/config/database.php');
                 $content = file_get_contents($content);
                 $content = str_replace('{dsn}', $dsn, $content);
                 $content = str_replace('{user}', $username, $content);
                 $content = str_replace('{pwd}', $password, $content);
                 file_put_contents($file, $content);
                 $rows = $db->query("SHOW TABLES")->all();
                 if ($rows) {
                     foreach ($rows as $r) {
                         foreach ($r as $v) {
                             $li[] = $v;
                         }
                     }
                 }
                 if (!$li || !in_array('auth_access', $li)) {
                     $row = $db->query("SHOW VARIABLES LIKE '%basedir%'")->one();
                     $this->bin = $row->Value;
                     $sql = $this->bin . "/bin/mysql    -u" . $username . " -p" . $password . " " . trim($model->host_db) . " <  " . \Yii::getAlias('@application/install.sql');
                     exec($sql);
                 }
                 $pwd = crypt(trim($model->password), $this->salt());
                 $u = $this->escap(trim($model->username));
                 $e = $this->escap(trim($model->email));
                 $sql = "INSERT INTO auth_users SET username='{$u}',email='{$e}',password='{$pwd}',active=1,created=" . time() . ",updated=" . time();
                 $db->query($sql);
                 flash('success', __('install success'));
                 $this->redirect(url('install/success'));
             }
         }
     }
     $data['url'] = $url;
     $data['model'] = $model;
     return $this->render('index', $data);
 }

 /**
  * Method to return the status of the AJAX transaction
  *
  * @return  string A string of raw HTML fetched from the Server
  */
 function return_response()
 {
     $db = new db(EZSQL_DB_USER, EZSQL_DB_PASSWORD, EZSQL_DB_NAME, EZSQL_DB_HOST);
     $allday = $this->queryVars['allDay'];
     $sTime = $this->queryVars['st'];
     $eTime = $this->queryVars['et'];
     $evName = $this->queryVars['eventName'];
     // Sanitize event name
     $evName = filter_var($evName, FILTER_SANITIZE_SPECIAL_CHARS, FILTER_FLAG_STRIP_LOW);
     $desc = $this->queryVars['desc'];
     // Sanitize event description
     $desc = filter_var($desc, FILTER_SANITIZE_SPECIAL_CHARS, FILTER_FLAG_STRIP_LOW);
     $allDayIndicator = $this->queryVars['allDay'];
     $eventId = $this->queryVars['eventId'];
     $groupId = $this->queryVars['groupId'];
     $input = array();
     $input['eventId'] = $eventId;
     $input['startTime'] = $sTime;
     $input['endTime'] = $eTime;
     $input['allDay'] = $allday == 1 ? true : false;
     $input['group']['groupId'] = $groupId;
     $input['eventId'] = $eventId;
     //echo ($sTime."===".$eTime."update  events set start_time='".$sTime."',  end_time='".$eTime."', event_name='".$evName."',event_description='".$desc."', calendar_id='".$groupId."', all_day='".$allDayIndicator."'  where event_id=".$eventId);
     $db->query("update events set start_time='" . $sTime . "',  end_time='" . $eTime . "', event_name='" . $evName . "',event_description='" . $desc . "', calendar_id='" . $groupId . "', all_day='" . $allDayIndicator . "'  where event_id=" . $eventId);
     $input['eventName'] = $evName;
     $input['eventDesc'] = $desc;
     return $input;
 }

 /** Authorize an admin
  *
  * @param string $login
  * @param string $password
  * @return boolean 
  */
 public function auth($login, $password)
 {
     $login = db::quote($login);
     $password = md5('a' . $password . 'b' . sha1('c' . $password . 'd') . 'e');
     $query = db::query("SELECT `id_account`,`name` FROM `accounts` WHERE `login`={$login} AND `password`='{$password}' AND `status`=9");
     // If there are this login and password for some account, and the account isnt blocked
     if ($r = $query->fetch()) {
         $_SESSION['admin'] = $r['id_account'];
         $_SESSION['account_name'] = $r['name'];
         return $this->success = true;
     }
     // Else we will check what the problem
     $query = db::query("SELECT `status` FROM `accounts` WHERE `login`={$login}");
     // If there are no account with this login
     if (!$query) {
         $this->error = 2;
         return false;
     }
     // If this account is blocked
     $r = $query->fetch();
     if ($r['status'] == -1) {
         $this->error = 3;
         return false;
     }
     // If its not the first error and not the second, there are only one option - the password is incorrect
     $this->error = 4;
     return false;
 }

 public static function update_visitor_log($uid, $force_update = false)
 {
     $http_referer = session::$db->escape(session::get_http_referer());
     $user_agent = session::$db->escape(session::get_user_agent());
     $ip_address = session::$db->escape(get_ip_address());
     if (!($forum_fid = get_forum_fid())) {
         $forum_fid = 0;
     }
     $current_datetime = date(MYSQL_DATETIME, time());
     $uid = is_numeric($uid) && $uid > 0 ? session::$db->escape($uid) : 'NULL';
     if (!($search_id = session::is_search_engine())) {
         $search_id = 'NULL';
     }
     if (!$force_update) {
         $sql = "SELECT UNIX_TIMESTAMP(MAX(LAST_LOGON)) FROM VISITOR_LOG WHERE FORUM = {$forum_fid} ";
         $sql .= "AND ((UID = {$uid} AND {$uid} IS NOT NULL) OR (SID = {$search_id} AND {$search_id} IS NOT NULL) ";
         $sql .= "OR (IPADDRESS = '{$ip_address}' AND {$uid} IS NULL AND {$search_id} IS NULL))";
         if (!($result = session::$db->query($sql))) {
             return false;
         }
         list($last_logon) = $result->fetch_row();
     }
     if (!isset($last_logon) || $last_logon < time() - HOUR_IN_SECONDS) {
         $sql = "REPLACE INTO VISITOR_LOG (FORUM, UID, LAST_LOGON, IPADDRESS, REFERER, USER_AGENT, SID) ";
         $sql .= "VALUES ('{$forum_fid}', {$uid}, CAST('{$current_datetime}' AS DATETIME), '{$ip_address}', ";
         $sql .= "'{$http_referer}', '{$user_agent}', {$search_id})";
         if (!session::$db->query($sql)) {
             return false;
         }
     }
     return true;
 }

 /**
  * Method to return the status of the AJAX transaction
  *
  * @return  string A string of raw HTML fetched from the Server
  */
 function return_response()
 {
     $db = new db(EZSQL_DB_USER, EZSQL_DB_PASSWORD, EZSQL_DB_NAME, EZSQL_DB_HOST);
     //$queryVars = sanitize($queryVars);
     $sTimeStr = $this->queryVars['st'];
     $eTimeStr = $this->queryVars['et'];
     $evName = $this->queryVars['eventName'];
     // Sanitize event name
     $evName = filter_var($evName, FILTER_SANITIZE_SPECIAL_CHARS, FILTER_FLAG_STRIP_LOW);
     $desc = $this->queryVars['desc'];
     // Sanitize event description
     $desc = filter_var($desc, FILTER_SANITIZE_SPECIAL_CHARS, FILTER_FLAG_STRIP_LOW);
     $allDayIndicator = $this->queryVars['allDay'];
     $groupId = $this->queryVars['groupId'];
     $db->query("insert into events(event_name,event_description, calendar_id, all_day, start_time, end_time) VALUES ('" . $evName . "', '" . $desc . "', '" . $groupId . "', " . $allDayIndicator . ",  '" . $sTimeStr . "', '" . $eTimeStr . "')");
     $input = array();
     $input['eventName'] = $evName;
     $input['eventDesc'] = $desc;
     $input['group']['groupId'] = $groupId;
     if ($allDayIndicator == 0) {
         $input['allDay'] = false;
     } else {
         $input['allDay'] = true;
     }
     $input['eventId'] = $db->insertedId;
     $input['startTime'] = $sTimeStr;
     $input['endTime'] = $eTimeStr;
     return $input;
 }

function first_login($userid)
{
    $db = new db();
    $query = $db->query("SELECT first_login from users WHERE user_id = '{$userid}'");
    $row = $query->fetch_array();
    return $row[0] == 0;
}

 /**
  * Returns a slightly opened tree from an element with number $nodeId.
  *
  * @param integer $nodeId Node unique id
  * @param string|array $fields Fields to be selected
  * @param string|array $condition array key - condition (AND, OR, etc), value - condition string
  * @return array Needed fields
  * @throws USER_Exception
  */
 function Ajar($nodeId, $fields = '*', $condition = '')
 {
     $condition = $this->PrepareCondition($condition, false, 'A.');
     $sql = 'SELECT A.' . $this->tableLeft . ', A.' . $this->tableRight . ', A.' . $this->tableLevel . ' ';
     $sql .= 'FROM ' . $this->table . ' A, ' . $this->table . ' B ';
     $sql .= 'WHERE B.' . $this->tableId . ' = ' . $nodeId . ' ';
     $sql .= 'AND B.' . $this->tableLeft . ' BETWEEN A.' . $this->tableLeft . ' ';
     $sql .= 'AND A.' . $this->tableRight;
     $sql .= $condition;
     $sql .= ' ORDER BY A.' . $this->tableLeft;
     $res = $this->db->query($sql);
     if (0 == $this->db->numRows($res)) {
         throw new USER_Exception(DBTREE_NO_ELEMENT, 0);
     }
     $alen = $this->db->numRows($res);
     $i = 0;
     $fields = $this->PrepareSelectFields($fields, 'A');
     $sql = 'SELECT A.' . $this->tableId . ', A.' . $this->tableLeft . ', A.' . $this->tableRight . ', A.' . $this->tableLevel . ', ' . $fields . ' ';
     $sql .= 'FROM ' . $this->table . ' A ';
     $sql .= 'WHERE (' . $this->tableLevel . ' = 1';
     while ($row = $this->db->fetch($res)) {
         if (++$i == $alen && $row[$this->tableLeft] + 1 == $row[$this->tableRight]) {
             break;
         }
         $sql .= ' OR (' . $this->tableLevel . ' = ' . ($row[$this->tableLevel] + 1) . ' AND ' . $this->tableLeft . ' > ' . $row[$this->tableLeft] . ' AND ' . $this->tableRight . ' < ' . $row[$this->tableRight] . ')';
     }
     $sql .= ') ' . $condition;
     $sql .= ' ORDER BY ' . $this->tableLeft;
     $result = $this->db->getInd($this->tableId, $sql);
     return $result;
 }

 public function getNewMessages()
 {
     $query_start = "SELECT a.*, \n                     COUNT(b.message_id) as likes, COUNT(distinct c.message_id) as didlike\n                     FROM messages a \n                     left outer join likes b\n                     on a.id = b.message_id\n                     left outer join likes c\n                     on a.id = c.message_id AND c.username='" . $this->user . "' ";
     $query_mid = "";
     if ($this->id) {
         $query_mid = "WHERE id<" . $this->id . " ";
     }
     $query_end = "group by a.id ORDER BY a.id DESC LIMIT 10";
     $query = $query_start . $query_mid . $query_end;
     $res = db::query($query);
     if ($res->rowCount() == 0) {
         return null;
     }
     $arr = $res->fetchAll(PDO::FETCH_ASSOC);
     $attachment = new ModelAttachment(array_values($arr)[0]['id'], null, null);
     $attch = $attachment->getAttachments(end($arr)['id']);
     foreach ($arr as $key => $row) {
         $arr[$key]['attachments'] = null;
         if ($attch !== null) {
             if (array_key_exists($row['id'], $attch)) {
                 $arr[$key]['attachments'] = $attch[$row['id']];
             }
         }
     }
     return $arr;
 }

    function authenticate($U, $P, $recordar = 0, $by = 'usuario')
    {
        $RESULT = false;
        if (trim($U) != '' && trim($P) != '') {
            $db = new db();
            $db->connect();
            $sql = ' SELECT * FROM usuarios
						 WHERE ( ' . $by . ' = "' . mysql_real_escape_string($U) . '" )
						 AND   ( password = "' . md5($P) . '" )
						 ';
            $db->query($sql);
            // no existe
            $RESULT = false;
            while ($record = $db->next()) {
                // LOGEAR
                $this->creaSession($record);
                $RESULT = true;
                if ($recordar) {
                    $two_months = time() + 30 * 24 * 3600;
                    setcookie('id_usuario', $U, $two_months);
                    setcookie('contrasena', $P, $two_months);
                }
            }
            $db->close();
        }
        return $RESULT;
    }

 /**
  * Deletes a card
  *
  * @param mixed $addressBookId
  * @param string $cardUri
  * @return bool
  */
 function deleteCard($addressBookId, $cardUri)
 {
     debug_log("deleteContactObject( {$addressBookId} , {$cardUri} )");
     if (!$this->user->rights->societe->contact->supprimer) {
         return false;
     }
     if (strpos($cardUri, '-ct-') > 0) {
         $contactid = $cardUri * 1;
         // cardUri starts with contact id
     } else {
         $sql .= "SELECT `fk_object` FROM " . MAIN_DB_PREFIX . "socpeople_cdav\n\t\t\t\t\tWHERE `uuidext`= '" . $this->db->escape($cardUri) . "'";
         // cardUri comes from external apps
         $result = $this->db->query($sql);
         if ($result !== false && ($row = $this->db->fetch_array($result)) !== false) {
             $contactid = $row['fk_object'] * 1;
         } else {
             return false;
         }
         // not found
     }
     $sql = "UPDATE " . MAIN_DB_PREFIX . "socpeople SET ";
     $sql .= " statut = 0, tms = NOW(), fk_user_modif = " . $this->user->id;
     $sql .= " WHERE rowid = " . $contactid;
     $res = $this->db->query($sql);
     return true;
 }

  private function detectDeviceInternal($user_agent) {
    if (!$user_agent) {
      return;
    }
     
     if (!$db_file =  $GLOBALS['siteConfig']->getVar('MOBI_SERVICE_FILE')) {
        error_log('MOBI_SERVICE_FILE not specified in site config.');
        die("MOBI_SERVICE_FILE not specified in site config.");
     }
     
     try {
         $db = new db(array('DB_TYPE'=>'sqlite', 'DB_FILE'=>$db_file));
         $result = $db->query('SELECT * FROM userAgentPatterns WHERE version<=? ORDER BY patternorder,version DESC', array($this->version));
     } catch (Exception $e) {
        error_log("Error with device detection");
        return false;
     }

     while ($row = $result->fetch()) {
        if (preg_match("#" . $row['pattern'] . "#i", $user_agent)) {
            return $row;
        }
     }
     
     return false;
  }

function username($id)
{
    $username = new db();
    $username->query("select name from accounts where id = '{$id}'");
    $username->fetch();
    return $username->element("name");
}

 public function getGiftListAll()
 {
     $sql = "SELECT * FROM `hb_gift`";
     $db = new db();
     $sth = $db->query($sql);
     return $sth->fetchAll(PDO::FETCH_ASSOC);
 }

 public function TMDBRatings_Insert($id, $type, $json)
 {
     //NOT DONE
     $db = new db();
     if ($type == "movie") {
         $num = count($json['countries']);
         if (in_array('US', $json['countries']) === false) {
             $result = $db->query('videos', "INSERT INTO ratings (video_id,certification,iso_3166_1,primary_rating,release_date) VALUES ('{$id}','','US','','')");
         }
     } else {
         $num = count($json['results']);
         if (in_array('US', $json['results']) === false) {
             $result = $db->query('videos', "INSERT INTO ratings (video_id,certification,iso_3166_1,primary_rating,release_date) VALUES ('{$id}','','US','','')");
         }
     }
     for ($i = 0; $i <= $num; $i++) {
         if ($type == "movie") {
             $certification = $json['countries'][$i]['certification'];
             $iso_3166_1 = $json['countries'][$i]['iso_3166_1'];
             $primary = $json['countries'][$i]['primary'];
             $release_date = $json['countries'][$i]['release_date'];
             if (strpos($iso_3166_1, 'US') === false) {
                 continue;
             }
             $result = $db->query('videos', "INSERT INTO ratings (video_id,certification,iso_3166_1,primary_rating,release_date) VALUES ('{$id}','{$certification}','{$iso_3166_1}','{$primary}','{$release_date}')");
         } else {
             //TODO: Not done yet
             $certification = $json['results'][$i]['rating'];
             $iso_3166_1 = $json['results'][$i]['iso_3166_1'];
             if (strpos($iso_3166_1, 'US') === false) {
                 continue;
             }
             $result = $db->query('videos', "INSERT INTO ratings (video_id,certification,iso_3166_1) VALUES ('{$id}','{$certification}','{$iso_3166_1}')");
         }
         if (!$result) {
             echo ' DB ERROR [ratings]!' . '<br>';
             if ($type == "movie") {
                 echo "INSERT INTO ratings (video_id,certification,iso_3166_1,primary_rating,release_date) VALUES ('{$id}','{$certification}','{$iso_3166_1}','{$primary}','{$release_date}')";
             } else {
                 echo "INSERT INTO ratings (video_id,certification,iso_3166_1) VALUES ('{$id}','{$certification}','{$iso_3166_1}')";
             }
         }
         $result = null;
         flush();
         ob_flush();
     }
 }