PHP Settings::query方法代碼示例

 /**
  * Get a range of releases. used in admin manage list
  *
  * @param $start
  * @param $num
  *
  * @return array
  */
 public function getFailedRange($start, $num)
 {
     if ($start === false) {
         $limit = '';
     } else {
         $limit = ' LIMIT ' . $start . ',' . $num;
     }
     return $this->pdo->query("\n\t\t\tSELECT r.*, CONCAT(cp.title, ' > ', c.title) AS category_name\n\t\t\tFROM releases r\n\t\t\tRIGHT JOIN dnzb_failures df ON df.release_id = r.id\n\t\t\tLEFT OUTER JOIN category c ON c.id = r.categoryid\n\t\t\tLEFT OUTER JOIN category cp ON cp.id = c.parentid\n\t\t\tORDER BY postdate DESC" . $limit);
 }

 public function getRange($start, $num)
 {
     if ($start === false) {
         $limit = '';
     } else {
         $limit = ' LIMIT ' . $num . ' OFFSET ' . $start;
     }
     return $this->pdo->query('SELECT * FROM bookinfo ORDER BY createddate DESC' . $limit);
 }

 /**
  * @param array $excludedcats
  *
  * @return array
  */
 public function getForMenu($excludedcats = [])
 {
     $ret = [];
     $exccatlist = '';
     if (count($excludedcats) > 0) {
         $exccatlist = ' AND id NOT IN (' . implode(',', $excludedcats) . ')';
     }
     $arr = $this->pdo->query(sprintf('SELECT * FROM category WHERE status = %d %s', Category::STATUS_ACTIVE, $exccatlist), true, nZEDb_CACHE_EXPIRY_LONG);
     foreach ($arr as $a) {
         if ($a['parentid'] == '') {
             $ret[] = $a;
         }
     }
     foreach ($ret as $key => $parent) {
         $subcatlist = [];
         $subcatnames = [];
         foreach ($arr as $a) {
             if ($a['parentid'] == $parent['id']) {
                 $subcatlist[] = $a;
                 $subcatnames[] = $a['title'];
             }
         }
         if (count($subcatlist) > 0) {
             array_multisort($subcatnames, SORT_ASC, $subcatlist);
             $ret[$key]['subcatlist'] = $subcatlist;
         } else {
             unset($ret[$key]);
         }
     }
     return $ret;
 }

    /**
     * Match added comments to releases.
     *
     * @access protected
     */
    protected function matchComments()
    {
        $res = $this->pdo->query('
			SELECT r.id, r.nzb_guid
			FROM releases r
			INNER JOIN release_comments rc ON rc.nzb_guid = r.nzb_guid
			WHERE rc.releaseid = 0');
        $found = count($res);
        if ($found > 0) {
            foreach ($res as $row) {
                $this->pdo->queryExec(sprintf("UPDATE release_comments SET releaseid = %d WHERE nzb_guid = %s", $row['id'], $this->pdo->escapeString($row['nzb_guid'])));
                $this->pdo->queryExec(sprintf('UPDATE releases SET comments = comments + 1 WHERE id = %d', $row['id']));
            }
            if (nZEDb_ECHOCLI) {
                echo '(Sharing) Matched ' . $found . ' comments.' . PHP_EOL;
            }
        }
        // Update first time seen.
        $siteTimes = $this->pdo->queryDirect('SELECT createddate, siteid FROM release_comments WHERE createddate > \'2005-01-01\' GROUP BY siteid ORDER BY createddate ASC');
        if ($siteTimes instanceof \Traversable && $siteTimes->rowCount()) {
            foreach ($siteTimes as $site) {
                $this->pdo->queryExec(sprintf('UPDATE sharing_sites SET first_time = %s WHERE site_guid = %s', $this->pdo->escapeString($site['createddate']), $this->pdo->escapeString($site['siteid'])));
            }
        }
    }

 /**
  * @param       $cat
  * @param       $start
  * @param       $num
  * @param       $orderby
  * @param int   $maxage
  * @param array $excludedcats
  */
 public function getGamesRange($cat, $start, $num, $orderby, $maxage = -1, $excludedcats = [])
 {
     $browseby = $this->getBrowseBy();
     $catsrch = '';
     if (count($cat) > 0 && $cat[0] != -1) {
         $catsrch = (new Category(['Settings' => $this->pdo]))->getCategorySearch($cat);
     }
     if ($maxage > 0) {
         $maxage = sprintf(' AND r.postdate > NOW() - INTERVAL %d DAY ', $maxage);
     } else {
         $maxage = '';
     }
     $exccatlist = "";
     if (count($excludedcats) > 0) {
         $exccatlist = " AND r.categoryid NOT IN (" . implode(",", $excludedcats) . ")";
     }
     $order = $this->getGamesOrder($orderby);
     $games = $this->pdo->queryCalc(sprintf("\n\t\t\t\tSELECT SQL_CALC_FOUND_ROWS con.id,\n\t\t\t\t\tGROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id\n\t\t\t\tFROM gamesinfo con\n\t\t\t\tLEFT JOIN releases r ON con.id = r.gamesinfo_id\n\t\t\t\tWHERE r.nzbstatus = 1\n\t\t\t\tAND con.title != ''\n\t\t\t\tAND con.cover = 1\n\t\t\t\tAND r.passwordstatus %s\n\t\t\t\tAND %s %s %s %s\n\t\t\t\tGROUP BY con.id\n\t\t\t\tORDER BY %s %s %s", Releases::showPasswords($this->pdo), $browseby, $catsrch, $maxage, $exccatlist, $order[0], $order[1], $start === false ? '' : ' LIMIT ' . $num . ' OFFSET ' . $start), true, nZEDb_CACHE_EXPIRY_MEDIUM);
     $gameIDs = $releaseIDs = false;
     if (is_array($games['result'])) {
         foreach ($games['result'] as $game => $id) {
             $gameIDs[] = $id['id'];
             $releaseIDs[] = $id['grp_release_id'];
         }
     }
     $return = $this->pdo->query(sprintf("\n\t\t\t\tSELECT\n\t\t\t\t\tGROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id,\n\t\t\t\t\tGROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') as grp_rarinnerfilecount,\n\t\t\t\t\tGROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview,\n\t\t\t\t\tGROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password,\n\t\t\t\t\tGROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid,\n\t\t\t\t\tGROUP_CONCAT(rn.releaseid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid,\n\t\t\t\t\tGROUP_CONCAT(g.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname,\n\t\t\t\t\tGROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name,\n\t\t\t\t\tGROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate,\n\t\t\t\t\tGROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size,\n\t\t\t\t\tGROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts,\n\t\t\t\t\tGROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments,\n\t\t\t\t\tGROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs,\n\t\t\t\t\tGROUP_CONCAT(df.failed ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_failed,\n\t\t\t\tcon.*, YEAR (con.releasedate) as year, r.gamesinfo_id, g.name AS group_name,\n\t\t\t\trn.releaseid AS nfoid\n\t\t\t\tFROM releases r\n\t\t\t\tLEFT OUTER JOIN groups g ON g.id = r.group_id\n\t\t\t\tLEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id\n\t\t\t\tLEFT OUTER JOIN dnzb_failures df ON df.release_id = r.id\n\t\t\t\tINNER JOIN gamesinfo con ON con.id = r.gamesinfo_id\n\t\t\t\tWHERE con.id IN (%s)\n\t\t\t\tAND r.id IN (%s)\n\t\t\t\tAND %s\n\t\t\t\tGROUP BY con.id\n\t\t\t\tORDER BY %s %s", is_array($gameIDs) ? implode(',', $gameIDs) : -1, is_array($releaseIDs) ? implode(',', $releaseIDs) : -1, $catsrch, $order[0], $order[1]), true, nZEDb_CACHE_EXPIRY_MEDIUM);
     if (!empty($return)) {
         $return[0]['_totalcount'] = isset($games['total']) ? $games['total'] : 0;
     }
     return $return;
 }

 /**
  * @param bool $activeOnly
  *
  * @return array
  */
 public function getGenres($activeOnly = false)
 {
     if ($activeOnly) {
         return $this->pdo->query("SELECT musicgenre.* FROM musicgenre INNER JOIN (SELECT DISTINCT musicgenreid FROM musicinfo) x ON x.musicgenreid = musicgenre.id ORDER BY title");
     } else {
         return $this->pdo->query("SELECT * FROM musicgenre ORDER BY title");
     }
 }

 public function getCommentsForUserRange($uid, $start, $num)
 {
     if ($start === false) {
         $limit = '';
     } else {
         $limit = " LIMIT {$num} OFFSET {$start}";
     }
     return $this->pdo->query(sprintf("\n\t\t\t\tSELECT release_comments.*\n\t\t\t\tFROM release_comments\n\t\t\t\tWHERE user_id = %d\n\t\t\t\tORDER BY release_comments.createddate DESC %s", $uid, $limit));
 }

    /**
     * Retrieves all info for a specific AniDB ID
     *
     * @param int $anidbID
     * @return array|boolean
     */
    public function getAnimeInfo($anidbID)
    {
        $animeInfo = $this->pdo->query(sprintf('SELECT at.anidbid, at.lang, at.title,
				ai.startdate, ai.enddate, ai.updated, ai.related, ai.creators, ai.description,
				ai.rating, ai.picture, ai.categories, ai.characters, ai.type, ai.similar
				FROM anidb_titles AS at LEFT JOIN anidb_info ai USING (anidbid)
				WHERE at.anidbid = %d', $anidbID));
        return isset($animeInfo[0]) ? $animeInfo[0] : false;
    }

 public function data_getForMenuByTypeAndRole($id, $role)
 {
     if ($role == Users::ROLE_ADMIN) {
         $role = "";
     } else {
         $role = sprintf("AND (role = %d OR role = 0)", $role);
     }
     return $this->pdo->query(sprintf("SELECT * FROM content WHERE showinmenu = 1 AND status = 1 AND contenttype = %d %s ", $id, $role));
 }

 /**
  * Get the regex from the DB, cache them locally for 15 mins.
  * Cache them also in the cache server, as this script might be terminated.
  *
  * @param string $groupName
  */
 protected function _fetchRegex($groupName)
 {
     // Check if we need to do an initial cache or refresh our cache.
     if (isset($this->_regexCache[$groupName]['ttl']) && time() - $this->_regexCache[$groupName]['ttl'] < 900) {
         return;
     }
     // Get all regex from DB which match the current group name. Cache them for 15 minutes. #CACHEDQUERY#
     $this->_regexCache[$groupName]['regex'] = $this->pdo->query(sprintf('SELECT r.regex%s FROM %s r WHERE %s REGEXP r.group_regex AND r.status = 1 ORDER BY r.ordinal ASC, r.group_regex ASC', $this->tableName === 'category_regexes' ? ', r.category_id' : '', $this->tableName, $this->pdo->escapeString($groupName)), true, 900);
     // Set the TTL.
     $this->_regexCache[$groupName]['ttl'] = time();
 }

 /**
  * Delete a role by ID.
  *
  * @param int $id ID of the role.
  *
  * @return bool|\PDOStatement
  */
 public function deleteRole($id)
 {
     $res = $this->pdo->query(sprintf("SELECT id FROM users WHERE role = %d", $id));
     if (sizeof($res) > 0) {
         $userids = [];
         foreach ($res as $user) {
             $userids[] = $user['id'];
         }
         $defaultrole = $this->getDefaultRole();
         $this->pdo->queryExec(sprintf("UPDATE users SET role = %d WHERE id IN (%s)", $defaultrole['id'], implode(',', $userids)));
     }
     return $this->pdo->queryExec(sprintf("DELETE FROM user_roles WHERE id = %d", $id));
 }

 /**
  * Get all genres for search-filter.tpl
  *
  * @param bool $activeOnly
  *
  * @return array|null
  */
 public function getAllGenres($activeOnly = false)
 {
     $ret = null;
     if ($activeOnly) {
         $res = $this->pdo->query("SELECT title FROM genres WHERE disabled = 0 AND type = 6000 ORDER BY title");
     } else {
         $res = $this->pdo->query("SELECT title FROM genres WHERE disabled = 1 AND type = 6000 ORDER BY title");
     }
     foreach ($res as $arr => $value) {
         $ret[] = $value['title'];
     }
     return $ret;
 }

 /**
  * Get all groups in the DB.
  *
  * @return bool
  * @access protected
  */
 protected function getAllGroups()
 {
     $this->allGroups = [];
     $groups = $this->pdo->query("SELECT id, name FROM groups");
     foreach ($groups as $group) {
         $this->allGroups[$group["name"]] = $group["id"];
     }
     if (count($this->allGroups) === 0) {
         $this->echoOut('You have no groups in your database!');
         return false;
     }
     return true;
 }

 /**
  * Reset all groups.
  *
  * @return bool
  */
 public function resetall()
 {
     $this->pdo->queryExec("TRUNCATE TABLE collections");
     $this->pdo->queryExec("TRUNCATE TABLE binaries");
     $this->pdo->queryExec("TRUNCATE TABLE parts");
     $this->pdo->queryExec("TRUNCATE TABLE missed_parts");
     $groups = $this->pdo->query("SELECT id FROM groups");
     foreach ($groups as $group) {
         $this->pdo->queryExec('DROP TABLE IF EXISTS collections_' . $group['id']);
         $this->pdo->queryExec('DROP TABLE IF EXISTS binaries_' . $group['id']);
         $this->pdo->queryExec('DROP TABLE IF EXISTS parts_' . $group['id']);
         $this->pdo->queryExec('DROP TABLE IF EXISTS missed_parts_' . $group['id']);
     }
     // Reset the group stats.
     return $this->pdo->queryExec("\n\t\t\tUPDATE groups\n\t\t\tSET backfill_target = 0, first_record = 0, first_record_postdate = NULL, last_record = 0,\n\t\t\t\tlast_record_postdate = NULL, last_updated = NULL, active = 0");
 }

 public function getConsoleRange($cat, $start, $num, $orderby, $excludedcats = [])
 {
     $browseby = $this->getBrowseBy();
     if ($start === false) {
         $limit = "";
     } else {
         $limit = " LIMIT " . $num . " OFFSET " . $start;
     }
     $catsrch = '';
     if (count($cat) > 0 && $cat[0] != -1) {
         $catsrch = (new Category(['Settings' => $this->pdo]))->getCategorySearch($cat);
     }
     $exccatlist = "";
     if (count($excludedcats) > 0) {
         $exccatlist = " AND r.categoryid NOT IN (" . implode(",", $excludedcats) . ")";
     }
     $order = $this->getConsoleOrder($orderby);
     return $this->pdo->query(sprintf("SELECT GROUP_CONCAT(r.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_id, " . "GROUP_CONCAT(r.rarinnerfilecount ORDER BY r.postdate DESC SEPARATOR ',') as grp_rarinnerfilecount, " . "GROUP_CONCAT(r.haspreview ORDER BY r.postdate DESC SEPARATOR ',') AS grp_haspreview, " . "GROUP_CONCAT(r.passwordstatus ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_password, " . "GROUP_CONCAT(r.guid ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_guid, " . "GROUP_CONCAT(rn.id ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_nfoid, " . "GROUP_CONCAT(groups.name ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grpname, " . "GROUP_CONCAT(r.searchname ORDER BY r.postdate DESC SEPARATOR '#') AS grp_release_name, " . "GROUP_CONCAT(r.postdate ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_postdate, " . "GROUP_CONCAT(r.size ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_size, " . "GROUP_CONCAT(r.totalpart ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_totalparts, " . "GROUP_CONCAT(r.comments ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_comments, " . "GROUP_CONCAT(r.grabs ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_grabs, " . "con.*, r.consoleinfoid, groups.name AS group_name, genres.title as genre, rn.id as nfoid FROM releases r " . "LEFT OUTER JOIN groups ON groups.id = r.group_id " . "LEFT OUTER JOIN release_nfos rn ON rn.releaseid = r.id " . "INNER JOIN consoleinfo con ON con.id = r.consoleinfoid " . "INNER JOIN genres ON con.genre_id = genres.id " . "WHERE r.nzbstatus = 1 AND con.title != '' AND " . "r.passwordstatus %s AND %s %s %s " . "GROUP BY con.id ORDER BY %s %s" . $limit, Releases::showPasswords($this->pdo), $browseby, $catsrch, $exccatlist, $order[0], $order[1]), true, nZEDb_CACHE_EXPIRY_MEDIUM);
 }