PHP Settings::query方法代码示例

 /**
  * Get all groups used by releaseregexes for use in dropdownlist.
  */
 public function getGroupsForSelect()
 {
     $categories = $this->pdo->query("SELECT distinct coalesce(groupname,'all') as groupname from releaseregex order by groupname ");
     $temp_array = [];
     $temp_array[-1] = "--Please Select--";
     foreach ($categories as $category) {
         $temp_array[$category["groupname"]] = $category["groupname"];
     }
     return $temp_array;
 }

 /**
  * @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");
     }
 }

 /**
  * @param        $start
  * @param        $num
  * @param string $seriesname
  *
  * @return array
  */
 public function getSeriesRange($start, $num, $seriesname = '')
 {
     $limit = $start === false ? '' : " LIMIT " . $start . "," . $num;
     $rsql = '';
     if ($seriesname != '') {
         $rsql .= sprintf("AND thetvdb.seriesname LIKE %s ", $this->pdo->escapeString("%" . $seriesname . "%"));
     }
     return $this->pdo->query(sprintf(" SELECT id, tvdbid, seriesname, overview FROM thetvdb WHERE 1=1 %s AND tvdbid > %d ORDER BY tvdbid ASC" . $limit, $rsql, 0));
 }

    /**
     * Retrieves all info for a specific AniDB id
     *
     * @param int $anidbID
     * @return
     */
    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
				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 comments for a user by limit.
  */
 public function getCommentsForUserRange($uid, $start, $num)
 {
     if ($start === false) {
         $limit = "";
     } else {
         $limit = " LIMIT " . $start . "," . $num;
     }
     return $this->pdo->query(sprintf("SELECT release_comments.*, r.guid, r.searchname, users.username FROM release_comments INNER JOIN releases r ON r.id = release_comments.releaseid LEFT OUTER JOIN users ON users.id = release_comments.userid WHERE userid = %d ORDER BY release_comments.createddate DESC " . $limit, $uid));
 }

 /**
  * Get forum posts for a user for paged list in profile.
  */
 public function getForUserRange($uid, $start, $num)
 {
     if ($start === false) {
         $limit = "";
     } else {
         $limit = " LIMIT " . $start . "," . $num;
     }
     return $this->pdo->query(sprintf(" SELECT forumpost.*, users.username FROM forumpost LEFT OUTER JOIN users ON users.id = forumpost.userid where userid = %d order by forumpost.createddate desc " . $limit, $uid));
 }

 /**
  * Get predb rows by limit and filter.
  */
 public function getPreRange($start = 0, $num, $dirname = '', $category = '')
 {
     // Only use Sphinx if this is a search request
     if ($dirname) {
         if ($this->pdo->getSetting('sphinxenabled') && $this->pdo->getSetting('sphinxindexpredb')) {
             // Search using Sphinx
             $sphinx = new Sphinx();
             $results = $sphinx->getPreRange($start, $num, $dirname, $category);
             if (is_array($results)) {
                 return $results;
             }
         }
     }
     $dirname = str_replace(' ', '%', $dirname);
     $dirname = empty($dirname) ? '' : sprintf('WHERE dirname LIKE %s', $this->pdo->escapeString('%' . $dirname . '%'));
     $category = empty($category) ? '' : sprintf((empty($dirname) ? 'WHERE' : ' AND') . " category = %s", $this->pdo->escapeString($category));
     $sql = sprintf('SELECT p.*, r.guid FROM predb p left outer join releases r on p.id = r.preid %s %s ORDER BY ctime DESC LIMIT %d,%d', $dirname, $category, $start, $num);
     return $this->pdo->query($sql, true);
 }

 /**
  * Get all genres for search-filter.tpl
  *
  * @param bool $activeOnly
  *
  * @return array|null
  */
 public function getAllGenres($activeOnly = false)
 {
     $res = $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;
 }

    /**
     * Match added comments to releases.
     */
    protected function matchComments()
    {
        $res = $this->pdo->query('
			SELECT r.id, r.nzb_guid
			FROM releases r
			INNER JOIN releasecomment 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 releasecomment 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']));
            }
            echo '(Sharing) Matched ' . $found . ' comments.' . PHP_EOL;
        }
    }

 /**
  * Process all releases tagged as musicinfoid -2 to attempt to retrieve properties from mediainfo xml.
  */
 public function processMusicReleaseFromMediaInfo()
 {
     $res = $this->pdo->query("SELECT r.searchname, ref.releaseid, ref.mediainfo FROM releaseextrafull ref INNER JOIN releases r ON r.id = ref.releaseid WHERE r.musicinfoid = -2");
     $rescount = sizeof($res);
     if ($rescount > 0) {
         if ($this->echooutput) {
             echo "MusicPr : Processing " . $rescount . " audio releases via mediainfo\n";
         }
         //load genres
         $gen = new Genres();
         $defaultGenres = $gen->getGenres(Genres::MUSIC_TYPE);
         $genreassoc = array();
         foreach ($defaultGenres as $dg) {
             $genreassoc[$dg['id']] = strtolower($dg['title']);
         }
         foreach ($res as $rel) {
             $albumId = -3;
             $mi = null;
             $mi = @simplexml_load_string($rel["mediainfo"]);
             if ($mi != null) {
                 $artist = (string) $mi->File->track[0]->Performer;
                 $album = (string) $mi->File->track[0]->Album;
                 $year = (string) $mi->File->track[0]->Recorded_date;
                 $genre = (string) $mi->File->track[0]->Genre;
                 $publisher = (string) $mi->File->track[0]->Publisher;
                 $albumCheck = $this->getMusicInfoByName($artist, $album);
                 if ($albumCheck === false) {
                     //
                     // insert new musicinfo
                     //
                     $genreKey = -1;
                     if ($genre != "") {
                         $albumId = $this->addUpdateMusicInfo($album, "", "", "null", $artist, $publisher, "null", "", $year, $genreKey, "", 0);
                     }
                 } else {
                     $albumId = $albumCheck["id"];
                 }
             }
             $sql = sprintf("update releases set musicinfoid = %d where id = %d", $albumId, $rel["releaseid"]);
             $this->pdo->queryExec($sql);
         }
     }
     return true;
 }

 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, " . "GROUP_CONCAT(r.failed ORDER BY r.postdate DESC SEPARATOR ',') AS grp_release_failed," . "con.*, r.consoleinfoid, groups.name AS group_name, rn.id as nfoid FROM releases r " . "LEFT OUTER JOIN groups ON groups.id = r.groupid " . "LEFT OUTER JOIN releasenfo rn ON rn.releaseid = r.id " . "INNER JOIN consoleinfo con ON con.id = r.consoleinfoid " . "INNER JOIN genres ON con.genreid = 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, NN_CACHE_EXPIRY_MEDIUM);
 }

    /**
     * Match added comments to releases.
     *
     * @access protected
     */
    protected function matchComments()
    {
        $res = $this->pdo->query('
			SELECT r.id
			FROM release_comments rc
			INNER JOIN releases r USING (nzb_guid)
			WHERE rc.releaseid = 0');
        $found = count($res);
        if ($found > 0) {
            foreach ($res as $row) {
                $this->pdo->queryExec(sprintf("\n\t\t\t\t\t\tUPDATE release_comments rc\n\t\t\t\t\t\tINNER JOIN releases r USING (nzb_guid)\n\t\t\t\t\t\tSET rc.releaseid = %d, r.comments = r.comments + 1\n\t\t\t\t\t\tWHERE r.id = %d\n\t\t\t\t\t\tAND rc.releaseid = 0", $row['id'], $row['id']));
            }
            if (NN_ECHOCLI) {
                echo "(Sharing) Matched {$found}  comments." . PHP_EOL;
            }
        }
        // Update first time seen.
        $this->pdo->queryExec(sprintf("\n\t\t\t\t\tUPDATE sharing_sites ss\n\t\t\t\t\tINNER JOIN\n\t\t\t\t\t\t(SELECT siteid, createddate\n\t\t\t\t\t\tFROM release_comments\n\t\t\t\t\t\tWHERE createddate > '2005-01-01'\n\t\t\t\t\t\tGROUP BY siteid\n\t\t\t\t\t\tORDER BY createddate ASC) rc\n\t\t\t\t\tON ss.site_guid = rc.siteid\n\t\t\t\t\tSET ss.first_time = rc.createddate\n\t\t\t\t\tWHERE ss.first_time IS NULL OR ss.first_time > rc.createddate"));
    }

 /**
  * Get range of consoleinfo rows for browse list.
  */
 public function getConsoleRange($cat, $start, $num, $orderby, $maxage = -1, $excludedcats = [])
 {
     $browseby = $this->getBrowseBy();
     if ($start === false) {
         $limit = "";
     } else {
         $limit = " LIMIT " . $start . "," . $num;
     }
     $catsrch = "";
     if (count($cat) > 0 && $cat[0] != -1) {
         $catsrch = " (";
         foreach ($cat as $category) {
             if ($category != -1) {
                 $categ = new Category();
                 if ($categ->isParent($category)) {
                     $children = $categ->getChildren($category);
                     $chlist = "-99";
                     foreach ($children as $child) {
                         $chlist .= ", " . $child["id"];
                     }
                     if ($chlist != "-99") {
                         $catsrch .= " r.categoryid in (" . $chlist . ") or ";
                     }
                 } else {
                     $catsrch .= sprintf(" r.categoryid = %d or ", $category);
                 }
             }
         }
         $catsrch .= "1=2 )";
     }
     $maxagesql = "";
     if ($maxage > 0) {
         $maxagesql = sprintf(" and r.postdate > now() - interval %d day ", $maxage);
     }
     $exccatlist = "";
     if (count($excludedcats) > 0) {
         $exccatlist = " and r.categoryid not in (" . implode(",", $excludedcats) . ")";
     }
     $order = $this->getConsoleOrder($orderby);
     $sql = sprintf(" SELECT r.*, r.id as releaseid, con.*, g.title as genre, groups.name as group_name, concat(cp.title, ' > ', c.title) as category_name, concat(cp.id, ',', c.id) as category_ids, rn.id as nfoid from releases r left outer join groups on groups.id = r.groupid inner join consoleinfo con on con.id = r.consoleinfoid left outer join releasenfo rn on rn.releaseid = r.id and rn.nfo is not null left outer join category c on c.id = r.categoryid left outer join category cp on cp.id = c.parentid left outer join genres g on g.id = con.genreid where r.passwordstatus <= (select value from settings where setting='showpasswordedrelease') and %s %s %s %s order by %s %s" . $limit, $browseby, $catsrch, $maxagesql, $exccatlist, $order[0], $order[1]);
     return $this->pdo->query($sql, true);
 }