PHP driver_interface::sql_query_limit方法代码示例

    public function get_bookmarks($ext_mode = '', $forums = array())
    {
        define('POSTS_BOOKMARKS_TABLE', $this->table_prefix . 'posts_bookmarks');
        $start = $this->request->variable('start', 0);
        $sql = 'SELECT COUNT(post_id) as posts_count
			FROM ' . POSTS_BOOKMARKS_TABLE . '
			WHERE user_id = ' . $this->user->data['user_id'];
        $result = $this->db->sql_query($sql);
        $posts_count = (int) $this->db->sql_fetchfield('posts_count');
        $this->db->sql_freeresult($result);
        $sql_where = $sql_fields = '';
        if ($ext_mode != 'find') {
            $sql_where = 'LEFT JOIN ' . USERS_TABLE . ' u ON (p.poster_id = u.user_id)';
            $sql_fields = ', p.post_time, u.user_id, u.username, u.user_colour';
        }
        $pagination_url = append_sid("{$this->phpbb_root_path}postbookmark", "mode=find");
        $this->pagination->generate_template_pagination($pagination_url, 'pagination', 'start', $posts_count, $this->config['topics_per_page'], $start);
        $sql = 'SELECT b.post_id AS b_post_id, b.user_id, b.bookmark_time, b.bookmark_desc, p.post_id, p.forum_id, p.topic_id, p.poster_id, p.post_subject, t.topic_title ' . $sql_fields . '
			FROM ' . POSTS_BOOKMARKS_TABLE . ' b
			LEFT JOIN ' . POSTS_TABLE . ' p ON( b.post_id = p.post_id)
			LEFT JOIN ' . TOPICS_TABLE . ' t ON( t.topic_id = p.topic_id)
			' . $sql_where . '
			WHERE b.user_id = ' . $this->user->data['user_id'] . '
			ORDER BY b.bookmark_time ASC';
        $result = $this->db->sql_query_limit($sql, $this->config['topics_per_page'], $start);
        while ($row = $this->db->sql_fetchrow($result)) {
            $topic_author = $sql_where ? get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']) : '';
            $post_time = $sql_where ? $this->user->format_date($row['post_time']) : '';
            // Send vars to template
            $this->template->assign_block_vars('postrow', array('POST_ID' => $row['b_post_id'], 'POST_TIME' => $post_time, 'BOOKMARK_TIME' => $this->user->format_date($row['bookmark_time']), 'BOOKMARK_DESC' => $row['bookmark_desc'], 'TOPIC_AUTHOR' => $topic_author, 'POST_TITLE' => $row['post_subject'] ? $row['post_subject'] : $row['topic_title'], 'U_VIEW_POST' => append_sid("{$this->phpbb_root_path}viewtopic.{$this->php_ext}", "p=" . $row['post_id'] . "#p" . $row['post_id'] . ""), 'S_DELETED_TOPIC' => !$row['topic_id'] ? true : false, 'S_DELETED_POST' => !$row['post_id'] ? true : false, 'U_POST_BOOKMARK' => '[url=' . generate_board_url() . '/viewtopic.' . $this->php_ext . '?p=' . $row['post_id'] . '#p' . $row['post_id'] . ']' . ($row['post_subject'] ? $row['post_subject'] : $row['topic_title']) . '[/url]'));
        }
        $this->db->sql_freeresult($result);
        $this->template->assign_vars(array('TOTAL_BOOKMARKS' => $this->user->lang('TOTAL_BOOKMARKS', (int) $posts_count), 'PAGE_NUMBER' => $this->pagination->on_page($posts_count, $this->config['topics_per_page'], $start)));
    }

    public function page_header($event)
    {
        if ($this->auth->acl_get('u_did_you_know')) {
            $sql_layer = $this->db->get_sql_layer();
            switch ($sql_layer) {
                case 'postgres':
                    $random = 'RANDOM()';
                    break;
                case 'mssql':
                case 'mssql_odbc':
                    $random = 'NEWID()';
                    break;
                default:
                    $random = 'RAND()';
                    break;
            }
            $sql = 'SELECT word, bbcode_uid, bbcode_bitfield, bbcode_options
				FROM ' . $this->did_you_know . "\n\t\t\t\tWHERE lang_iso = '{$this->user->data['user_lang']}'\n\t\t\t\t\tOR lang_iso = 'default'\n\t\t\t\tORDER BY {$random}";
            $result = $this->db->sql_query_limit($sql, 1);
            $row = $this->db->sql_fetchrow($result);
            $this->db->sql_freeresult($result);
            $word = generate_text_for_display($row['word'], $row['bbcode_uid'], $row['bbcode_bitfield'], $row['bbcode_options']);
            $this->template->assign_vars(array('DID_YOU_KNOW' => str_replace(""", '"', $word), 'S_DIDYOUKNOW' => !empty($this->user->data['user_didyouknow']) ? true : false, 'U_DYK_HIDE' => $this->helper->route('dmzx_didyouknow_controller', array('mode' => 'hide'))));
        }
    }

 /**
  * Likes controller for route /like_post/{like}
  *
  * @param  int   @post_id  The post to be edited.
  */
 public function like_post($post_id)
 {
     // If unknown user or bot, cannot like.
     if ($this->user->data['user_id'] == ANONYMOUS || $this->user->data['is_bot']) {
         return;
     }
     // Add language variables for response.
     $this->user->add_lang_ext('nuleaf/likes', 'likes');
     // Grab forum id for permission.
     $sql = 'SELECT forum_id
 FROM ' . POSTS_TABLE . '
 WHERE post_id = ' . $post_id;
     $result = $this->db->sql_query_limit($sql, 1);
     $forum_id = $this->db->sql_fetchrow($result)['forum_id'];
     $this->db->sql_freeresult($result);
     // Does the user have permission to like posts in this forum?
     if ($this->auth->acl_get('!f_like', $forum_id)) {
         $json_response = new json_response();
         $json_response->send(array('error' => $this->user->lang('LIKE_NOT_AUTHORIZED')));
         return;
     }
     if ($this->request->is_ajax()) {
         $liked = $this->likes_manager->is_liked($post_id);
         if ($liked) {
             // If post is already liked, unlike it.
             $likes_count = $this->likes_manager->unlike($post_id);
         } else {
             // Else like the post.
             $likes_count = $this->likes_manager->like($post_id);
         }
         // Since the post has now been liked/unliked, $liked is reversed.
         $json_response = new json_response();
         $json_response->send(array('likes_count' => $likes_count, 'liked' => !$liked, 'LIKE_POST' => $this->user->lang('LIKE_POST'), 'UNLIKE_POST' => $this->user->lang('UNLIKE_POST'), 'LIKE_BUTTON' => $this->user->lang('LIKE_BUTTON'), 'UNLIKE_BUTTON' => $this->user->lang('UNLIKE_BUTTON')));
     }
 }

    public function handle_downloadlog()
    {
        if (!$this->auth->acl_get('a_')) {
            trigger_error('Access Denied');
        } else {
            $this->user->add_lang_ext('dmzx/downloadlog', 'common');
            $fileid = $this->request->variable('file', 0);
            $start = $this->request->variable('start', 0);
            // Pagination number from ACP
            $dll = $this->config['downloadlog_value'];
            // Generate pagination
            $sql = 'SELECT COUNT(downloadslog_id) AS total_downloadlogs
				FROM ' . $this->userdownloadslog_table . '
				WHERE user_id = user_id
				AND file_id = ' . $fileid;
            $result = $this->db->sql_query($sql);
            $total_downloadlogs = (int) $this->db->sql_fetchfield('total_downloadlogs');
            $sql = 'SELECT d.user_id, d.down_date, u.user_id, u.username, u.user_colour
				FROM ' . $this->userdownloadslog_table . ' d, ' . USERS_TABLE . ' u
				WHERE u.user_id = d.user_id
				AND file_id = ' . $fileid . '
				ORDER BY d.down_date DESC';
            $top_result = $this->db->sql_query_limit($sql, $dll, $start);
            while ($row = $this->db->sql_fetchrow($top_result)) {
                $this->template->assign_block_vars('downloaders', array('D_USERNAME' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']), 'D_TIME' => $this->user->format_date($row['down_date'])));
            }
        }
        $pagination_url = $this->helper->route('dmzx_downloadlog_controller', array('file' => $fileid));
        //Start pagination
        $this->pagination->generate_template_pagination($pagination_url, 'pagination', 'start', $total_downloadlogs, $dll, $start);
        $this->template->assign_vars(array('DOWNLOADERS_USERS' => $total_downloadlogs == 1 ? $this->user->lang['DOWNLOADERS_COUNT'] : sprintf($this->user->lang['DOWNLOADERS_COUNTS'], $total_downloadlogs), 'DOWNLOADERS_VERSION' => $this->config['downloadlog_version']));
        page_header('Downloaders Log', false);
        $this->template->set_filenames(array('body' => 'DownloadLog.html'));
        page_footer();
    }

    /**
     * Changes the regex replacement for second pass
     *
     * @param object $event
     * @return null
     * @access public
     */
    public function modify_replies($event)
    {
        if (!function_exists('get_username_string')) {
            include $this->root_path . 'includes/functions_content.' . $this->php_ext;
        }
        // 1. output each line with user + post-count
        // 2. output in "inline-popup" like in "mark posts read"
        $topic_row = $event['topic_row'];
        $topic_id = $topic_row['TOPIC_ID'];
        $sql = 'SELECT COUNT(p.post_id) AS posts, p.poster_id, u.username, u.user_colour
		FROM phpbb_posts p, phpbb_users u
		WHERE p.topic_id = ' . (int) $topic_id . '
		AND p.poster_id = u.user_id
		GROUP BY p.poster_id
		ORDER BY posts DESC';
        $result = $this->db->sql_query_limit($sql, 5);
        while ($row = $this->db->sql_fetchrow($result)) {
            var_dump($row);
            $post_count = $row['posts'];
            $display_username = get_username_string('full', $row['poster_id'], $row['username'], $row['user_colour']);
            echo $display_username . ' with ' . $post_count . 'posts<br />';
        }
        $this->db->sql_freeresult($result);
        $topic_row['REPLIES'] = '<a href="#t=' . $topic_id . '" class="whoposted">' . $topic_row['REPLIES'] . '</a>';
        $event['topic_row'] = $topic_row;
    }

    /**
     * Delete a version of an article
     *
     * @param	int		$id	Id of the version to delete
     * @return	object
     */
    public function version($id)
    {
        if (!$this->auth->acl_get('m_wiki_delete')) {
            trigger_error('NOT_AUTHORISED');
        }
        $sql = 'SELECT article_approved, article_url
			FROM ' . $this->article_table . '
				WHERE article_id = ' . (int) $id;
        $result = $this->db->sql_query_limit($sql, 1);
        $row = $this->db->sql_fetchrow($result);
        $this->db->sql_freeresult($result);
        if ($row['article_approved'] != 0) {
            trigger_error($this->user->lang['NO_DELETE_ACTIVE_VERSION'] . '<br /><br /><a href="' . $this->helper->route('tas2580_wiki_article', array('article' => $row['article_url'])) . '">' . $this->user->lang['BACK_TO_ARTICLE'] . '</a>');
        }
        if (confirm_box(true)) {
            $sql = 'DELETE FROM ' . $this->article_table . '
				WHERE article_id = ' . (int) $id;
            $this->db->sql_query($sql);
            //return $helper->message('DELETE_VERSION_SUCCESS', array());
            trigger_error($this->user->lang['DELETE_VERSION_SUCCESS'] . '<br /><br /><a href="' . $this->helper->route('tas2580_wiki_article', array('article' => $row['article_url'])) . '">' . $this->user->lang['BACK_TO_ARTICLE'] . '</a>');
        } else {
            $s_hidden_fields = build_hidden_fields(array('id' => $id));
            confirm_box(false, $this->user->lang['CONFIRM_DELETE_VERSION'], $s_hidden_fields);
        }
        redirect($this->helper->route('tas2580_wiki_index', array('id' => $id)));
    }

    /**
     * Get number of groups, displayed on the legend
     *
     * @return	int		value of the last item displayed
     */
    public function get_group_count()
    {
        $sql = 'SELECT group_legend
			FROM ' . GROUPS_TABLE . '
			ORDER BY group_legend DESC';
        $result = $this->db->sql_query_limit($sql, 1);
        $group_count = (int) $this->db->sql_fetchfield('group_legend');
        $this->db->sql_freeresult($result);
        return $group_count;
    }

    /**
     * Get module data from database
     *
     * @param int $module_id Module ID
     * @return array Module data array
     */
    public function get_module_data($module_id)
    {
        $sql = 'SELECT *
			FROM ' . PORTAL_MODULES_TABLE . '
			WHERE module_id = ' . (int) $module_id;
        $result = $this->db->sql_query_limit($sql, 1);
        $module_data = $this->db->sql_fetchrow($result);
        $this->db->sql_freeresult($result);
        return $module_data;
    }

    /**
     * Get batch.
     *
     * @return array
     */
    protected function get_batch()
    {
        $sql = 'SELECT DISTINCT topic_id, post_user_id
			FROM ' . $this->posts_table . '
			WHERE post_approved = 1
				AND post_deleted = 0';
        $result = $this->db->sql_query_limit($sql, $this->limit, $this->start);
        $rows = $this->db->sql_fetchrowset($result);
        $this->db->sql_freeresult($result);
        return $rows;
    }

    /**
     * {@inheritdoc}
     */
    public function get_template_side($module_id)
    {
        $sql = 'SELECT user_id, username, user_posts, user_colour
			FROM ' . USERS_TABLE . '
			WHERE user_type <> ' . USER_IGNORE . "\n\t\t\t\tAND user_posts <> 0\n\t\t\t\tAND username <> ''\n\t\t\tORDER BY user_posts DESC";
        $result = $this->db->sql_query_limit($sql, $this->config['board3_topposters_' . $module_id], 0, 600);
        while ($row = $this->db->sql_fetchrow($result)) {
            $this->template->assign_block_vars('topposters', array('S_SEARCH_ACTION' => append_sid("{$this->phpbb_root_path}search.{$this->php_ext}", 'author_id=' . $row['user_id'] . '&amp;sr=posts'), 'USERNAME_FULL' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']), 'POSTER_POSTS' => $row['user_posts']));
        }
        $this->db->sql_freeresult($result);
        return 'topposters_side.html';
    }

 /**
  * get members
  */
 public function get_list($get = array())
 {
     $this->settings = $get + array('query_type' => 'recent', 'date_range' => '', 'max_members' => 5);
     $sql = $this->get_sql_statement();
     $result = $this->db->sql_query_limit($sql, $this->settings['max_members']);
     $has_results = false;
     while ($row = $this->db->sql_fetchrow($result)) {
         $has_results = true;
         $this->ptemplate->assign_block_vars('member', call_user_func_array(array($this, $this->view_mode), array($row)));
     }
     $this->db->sql_freeresult($result);
     return $this->show_results($has_results);
 }

    /**
     * Return correct object for specified mode
     *
     * @param string	$mode		The feeds mode.
     * @param int	$forum_id	Forum id specified by the script if forum feed provided.
     * @param int	$topic_id	Topic id specified by the script if topic feed provided.
     *
     * @return object	Returns correct feeds object for specified mode.
     */
    function get_feed($mode, $forum_id, $topic_id)
    {
        switch ($mode) {
            case 'forums':
                if (!$this->config['feed_overall_forums']) {
                    return false;
                }
                return $this->container->get('feed.forums');
                break;
            case 'topics':
            case 'topics_new':
                if (!$this->config['feed_topics_new']) {
                    return false;
                }
                return $this->container->get('feed.topics');
                break;
            case 'topics_active':
                if (!$this->config['feed_topics_active']) {
                    return false;
                }
                return $this->container->get('feed.topics_active');
                break;
            case 'news':
                // Get at least one news forum
                $sql = 'SELECT forum_id
					FROM ' . FORUMS_TABLE . '
					WHERE ' . $this->db->sql_bit_and('forum_options', FORUM_OPTION_FEED_NEWS, '<> 0');
                $result = $this->db->sql_query_limit($sql, 1, 0, 600);
                $s_feed_news = (int) $this->db->sql_fetchfield('forum_id');
                $this->db->sql_freeresult($result);
                if (!$s_feed_news) {
                    return false;
                }
                return $this->container->get('feed.news');
                break;
            default:
                if ($topic_id && $this->config['feed_topic']) {
                    return $this->container->get('feed.topic')->set_topic_id($topic_id);
                } else {
                    if ($forum_id && $this->config['feed_forum']) {
                        return $this->container->get('feed.forum')->set_forum_id($forum_id);
                    } else {
                        if ($this->config['feed_overall']) {
                            return $this->container->get('feed.overall');
                        }
                    }
                }
                return false;
                break;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function get_template_side($module_id)
    {
        $sql = 'SELECT user_id, username, user_regdate, user_colour
			FROM ' . USERS_TABLE . '
			WHERE user_type <> ' . USER_IGNORE . '
				AND user_inactive_time = 0
			ORDER BY user_regdate DESC';
        $result = $this->db->sql_query_limit($sql, $this->config['board3_max_last_member_' . $module_id], 0, 600);
        while (($row = $this->db->sql_fetchrow($result)) && $row['username']) {
            $this->template->assign_block_vars('latest_members', array('USERNAME_FULL' => get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']), 'JOINED' => $this->user->format_date($row['user_regdate'], $format = 'd M')));
        }
        $this->db->sql_freeresult($result);
        return 'latest_members_side.html';
    }

    public function index_modify_page_title($event)
    {
        $this->user->add_lang_ext('dmzx/newestmembers', 'common');
        $sql = 'SELECT user_id, username, user_colour
			FROM ' . USERS_TABLE . '
			WHERE user_type IN (' . USER_NORMAL . ', ' . USER_FOUNDER . ')
			ORDER BY user_id DESC';
        $result = $this->db->sql_query_limit($sql, 5);
        if ($result) {
            while ($row = $this->db->sql_fetchrow($result)) {
                $this->template->assign_block_vars('newestmembers', array('NEWESTMEMBERS' => sprintf($this->user->lang['NEWEST_MEMBERS'], get_username_string('full', $row['user_id'], $row['username'], $row['user_colour']))));
            }
        }
        $this->db->sql_freeresult($result);
    }

    public function get_last_post_data($data)
    {
        $forum_id = (int) $data['forum_id'];
        $topic_id = (int) $data['topic_id'];
        $user_id = (int) $this->user->data['user_id'];
        $sql_array = array('SELECT' => 'f.enable_indexing, f.forum_id, p.bbcode_bitfield, p.bbcode_uid, p.post_created,
				p.enable_bbcode,  p.enable_magic_url, p.enable_smilies, p.poster_id, p.post_attachment,
				p.post_edit_locked, p.post_id, p.post_subject, p.post_text, p.post_time, p.post_visibility, t.topic_attachment,
				t.topic_first_post_id, t.topic_id, t.topic_last_post_time', 'FROM' => array(FORUMS_TABLE => 'f', POSTS_TABLE => 'p', TOPICS_TABLE => 't'), 'WHERE' => "p.post_id = t.topic_last_post_id\n\t\t\t\tAND t.topic_posts_unapproved = 0\n\t\t\t\tAND t.topic_id = {$topic_id}\n\t\t\t\tAND (f.forum_id = t.forum_id \n\t\t\t\t\tOR f.forum_id = {$forum_id})");
        $sql = $this->db->sql_build_query('SELECT', $sql_array);
        $result = $this->db->sql_query_limit($sql, 1);
        $last_post_data = $this->db->sql_fetchrow($result);
        $this->db->sql_freeresult($result);
        return $last_post_data;
    }