PHP driver_interface::sql_build_query方法代碼示例

 /**
  * Get Tree Query
  *
  * @param	integer	$start			Starting level
  * @param	integer $level			Max depth
  * @param	array	$sql_array		Array of elements to merge into query
  * 										array(
  * 											'SELECT'	=> array('p.*'),
  * 											'WHERE'		=> array('p.post_id = 2'),
  * 										)
  * @return	string		The sql query
  */
 public function qet_tree_sql($start = 0, $level = 0, $sql_array = array())
 {
     $sql_array = array_merge_recursive(array('SELECT' => array('t.*'), 'FROM' => array($this->items_table => ' t'), 'WHERE' => array('t.depth ' . ($level ? " BETWEEN {$start} AND " . ($start + $level) : ' >= ' . $start), $this->sql_where ? 't.' . $this->sql_where : ''), 'ORDER_BY' => 't.left_id ASC'), $sql_array);
     $sql_array['SELECT'] = join(', ', array_filter($sql_array['SELECT']));
     $sql_array['WHERE'] = join(' AND ', array_filter($sql_array['WHERE']));
     return $this->db->sql_build_query('SELECT', $sql_array);
 }

 /**
  * {@inheritdoc}
  */
 public function get_group_rules($type = '')
 {
     $sql_array = array('SELECT' => 'agr.*, agt.autogroups_type_name', 'FROM' => array($this->autogroups_rules_table => 'agr', $this->autogroups_types_table => 'agt'), 'WHERE' => 'agr.autogroups_type_id = agt.autogroups_type_id' . ($type ? " AND agt.autogroups_type_name = '" . $this->db->sql_escape($type) . "'" : ''));
     $sql = $this->db->sql_build_query('SELECT', $sql_array);
     $result = $this->db->sql_query($sql, 7200);
     $rows = $this->db->sql_fetchrowset($result);
     $this->db->sql_freeresult($result);
     return $rows;
 }

 /**
  * Get users that should not have their default status changed
  *
  * @return array An array of user ids
  * @access public
  */
 public function get_default_exempt_users()
 {
     $user_id_ary = array();
     // Get users whose default group is autogroup_default_exempt
     $sql_array = array('SELECT' => 'u.user_id', 'FROM' => array(USERS_TABLE => 'u'), 'LEFT_JOIN' => array(array('FROM' => array(GROUPS_TABLE => 'g'), 'ON' => 'g.group_id = u.group_id')), 'WHERE' => 'g.autogroup_default_exempt = 1');
     $sql = $this->db->sql_build_query('SELECT', $sql_array);
     $result = $this->db->sql_query($sql);
     while ($row = $this->db->sql_fetchrow($result)) {
         $user_id_ary[] = $row['user_id'];
     }
     $this->db->sql_freeresult($result);
     return $user_id_ary;
 }

 /**
  * Build a cache of group names
  *
  * @param object $event The event object
  * @return null
  * @access public
  */
 public function build_group_name_cache($event)
 {
     if ($this->cache->get('_user_groups') === false) {
         $sql_ary = array('SELECT' => 'ug.user_id, g.group_name, g.group_colour, g.group_type, g.group_id', 'FROM' => array(USERS_TABLE => 'u'), 'LEFT_JOIN' => array(array('FROM' => array(USER_GROUP_TABLE => 'ug'), 'ON' => 'ug.user_id = u.user_id'), array('FROM' => array(GROUPS_TABLE => 'g'), 'ON' => 'ug.group_id = g.group_id')), 'WHERE' => $this->db->sql_in_set('u.user_type', array(USER_FOUNDER, USER_NORMAL)) . ' AND ug.user_pending = 0', 'ORDER_BY' => 'u.user_id ASC, g.group_name');
         $result = $this->db->sql_query($this->db->sql_build_query('SELECT', $sql_ary));
         $user_groups = array();
         while ($row = $this->db->sql_fetchrow($result)) {
             $user_groups[$row['user_id']][] = array('group_name' => (string) $row['group_name'], 'group_colour' => $row['group_colour'], 'group_id' => $row['group_id'], 'group_type' => $row['group_type']);
         }
         $this->db->sql_freeresult($result);
         // cache this data for 5 minutes
         $this->cache->put('_user_groups', $user_groups, 300);
     }
 }

    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;
    }

    /**
     * Get contrib count for a category.
     *
     * @param $category_id
     * @return int
     */
    public function _get_contrib_count($category_id)
    {
        // Bundle up the children in a nice array
        $child_list = array($category_id);
        $sql = 'SELECT left_id, right_id
			FROM ' . $this->categories_table . '
			WHERE category_id = ' . (int) $category_id . '
			ORDER BY left_id ASC';
        $result = $this->db->sql_query($sql);
        $cat_row = $this->db->sql_fetchrow($result);
        $this->db->sql_freeresult($result);
        if (!$cat_row) {
            return 0;
        }
        $sql = 'SELECT category_id
			FROM ' . $this->categories_table . '
			WHERE left_id > ' . $cat_row['left_id'] . '
				AND right_id < ' . $cat_row['right_id'];
        $result = $this->db->sql_query($sql);
        while ($row = $this->db->sql_fetchrow($result)) {
            $child_list[] = $row['category_id'];
        }
        $this->db->sql_freeresult($result);
        $sql_ary = array('SELECT' => 'COUNT(DISTINCT c.contrib_id) AS cnt', 'FROM' => array($this->contrib_in_categories_table => 'cic', $this->contribs_table => 'c'), 'WHERE' => 'cic.contrib_id = c.contrib_id
				AND ' . $this->db->sql_in_set('cic.category_id', array_map('intval', $child_list)) . '
				AND c.contrib_visible = 1
				AND ' . $this->db->sql_in_set('c.contrib_status', array(TITANIA_CONTRIB_APPROVED, TITANIA_CONTRIB_DOWNLOAD_DISABLED)));
        $sql = $this->db->sql_build_query('SELECT', $sql_ary);
        $this->db->sql_query($sql);
        $cnt = (int) $this->db->sql_fetchfield('cnt');
        $this->db->sql_freeresult();
        return $cnt;
    }

    /**
     * Display popup comment
     *
     * @param	int		$link_id		The category ID
     * @param	int		$page			Page number taken from the URL
     * @param	string	$mode			add|edit
     * @return	\Symfony\Component\HttpFoundation\Response	A Symfony Response object
     * @throws	\phpbb\exception\http_exception
     */
    public function view($link_id, $page, $mode = 'new')
    {
        $this->_check_comments_enable($link_id);
        $comment_id = $this->request->variable('c', 0);
        $view = $this->request->variable('view', '');
        $start = ($page - 1) * $this->config['dir_comments_per_page'];
        $this->s_hidden_fields = array_merge($this->s_hidden_fields, array('page' => $page));
        $this->_populate_form($link_id, $mode);
        $sql = 'SELECT COUNT(comment_id) AS nb_comments
			FROM ' . DIR_COMMENT_TABLE . '
			WHERE comment_link_id = ' . (int) $link_id;
        $result = $this->db->sql_query($sql);
        $nb_comments = (int) $this->db->sql_fetchfield('nb_comments');
        $this->db->sql_freeresult($result);
        // Make sure $start is set to the last page if it exceeds the amount
        $start = $this->pagination->validate_start($start, $this->config['dir_comments_per_page'], $nb_comments);
        $sql_array = array('SELECT' => 'a.comment_id, a.comment_user_id, a. comment_user_ip, a.comment_date, a.comment_text, a.comment_uid, a.comment_bitfield, a.comment_flags, u.username, u.user_id, u.user_colour, z.foe', 'FROM' => array(DIR_COMMENT_TABLE => 'a'), 'LEFT_JOIN' => array(array('FROM' => array(USERS_TABLE => 'u'), 'ON' => 'a.comment_user_id = u.user_id'), array('FROM' => array(ZEBRA_TABLE => 'z'), 'ON' => 'z.user_id = ' . $this->user->data['user_id'] . ' AND z.zebra_id = a.comment_user_id')), 'WHERE' => 'a.comment_link_id = ' . (int) $link_id, 'ORDER_BY' => 'a.comment_date DESC');
        $sql = $this->db->sql_build_query('SELECT', $sql_array);
        $result = $this->db->sql_query_limit($sql, $this->config['dir_comments_per_page'], $start);
        $have_result = false;
        while ($comments = $this->db->sql_fetchrow($result)) {
            $have_result = true;
            $edit_allowed = $this->user->data['is_registered'] && ($this->auth->acl_get('m_edit_comment_dir') || $this->user->data['user_id'] == $comments['comment_user_id'] && $this->auth->acl_get('u_edit_comment_dir'));
            $delete_allowed = $this->user->data['is_registered'] && ($this->auth->acl_get('m_delete_comment_dir') || $this->user->data['user_id'] == $comments['comment_user_id'] && $this->auth->acl_get('u_delete_comment_dir'));
            $this->template->assign_block_vars('comment', array('MINI_POST_IMG' => $this->user->img('icon_post_target', 'POST'), 'S_USER' => get_username_string('full', $comments['comment_user_id'], $comments['username'], $comments['user_colour']), 'S_USER_IP' => $comments['comment_user_ip'], 'S_DATE' => $this->user->format_date($comments['comment_date']), 'S_COMMENT' => generate_text_for_display($comments['comment_text'], $comments['comment_uid'], $comments['comment_bitfield'], $comments['comment_flags']), 'S_ID' => $comments['comment_id'], 'U_EDIT' => $edit_allowed ? $this->helper->route('ernadoo_phpbbdirectory_comment_edit_controller', array('link_id' => (int) $link_id, 'comment_id' => (int) $comments['comment_id'])) : '', 'U_DELETE' => $delete_allowed ? $this->helper->route('ernadoo_phpbbdirectory_comment_delete_controller', array('link_id' => (int) $link_id, 'comment_id' => (int) $comments['comment_id'], '_referer' => $this->helper->get_current_url())) : '', 'S_IGNORE_POST' => $comments['foe'] && ($view != 'show' || $comment_id != $comments['comment_id']) ? true : false, 'L_IGNORE_POST' => $comments['foe'] ? $this->user->lang('POST_BY_FOE', get_username_string('full', $comments['comment_user_id'], $comments['username'], $comments['user_colour']), '<a href="' . $this->helper->url('directory/link/' . $link_id . '/comment' . ($page > 1 ? '/' . $page : '') . '?view=show#c' . (int) $comments['comment_id']) . '">', '</a>') : '', 'L_POST_DISPLAY' => $comments['foe'] ? $this->user->lang('POST_DISPLAY', '<a class="display_post" data-post-id="' . $comments['comment_id'] . '" href="' . $this->helper->url('directory/link/' . $link_id . '/comment' . ($page > 1 ? '/' . $page : '') . '?c=' . (int) $comments['comment_id'] . '&view=show#c' . (int) $comments['comment_id']) . '">', '</a>') : '', 'S_INFO' => $this->auth->acl_get('m_info')));
        }
        $base_url = array('routes' => 'ernadoo_phpbbdirectory_comment_view_controller', 'params' => array('link_id' => (int) $link_id));
        $this->pagination->generate_template_pagination($base_url, 'pagination', 'page', $nb_comments, $this->config['dir_comments_per_page'], $start);
        $this->template->assign_vars(array('TOTAL_COMMENTS' => $this->user->lang('DIR_NB_COMMS', (int) $nb_comments), 'S_HAVE_RESULT' => $have_result ? true : false));
        return $this->helper->render('comments.html', $this->user->lang['DIR_COMMENT_TITLE']);
    }

 /**
  * {@inheritdoc}
  */
 public function get_item()
 {
     if (!isset($this->result)) {
         if (!$this->get_sql()) {
             return false;
         }
         $sql_ary = $this->sql;
         /**
          * Event to modify the feed item sql
          *
          * @event core.feed_base_modify_item_sql
          * @var	array	sql_ary		The SQL array to get the feed item data
          *
          * @since 3.1.10-RC1
          */
         $vars = array('sql_ary');
         extract($this->phpbb_dispatcher->trigger_event('core.feed_base_modify_item_sql', compact($vars)));
         $this->sql = $sql_ary;
         unset($sql_ary);
         // Query database
         $sql = $this->db->sql_build_query('SELECT', $this->sql);
         $this->result = $this->db->sql_query_limit($sql, $this->num_items);
     }
     return $this->db->sql_fetchrow($this->result);
 }

    /**
     * Performs a search on keywords depending on display specific params. You have to run split_keywords() first
     *
     * @param	array		$keywords_ary		contains each words to search
     * @param	string		$fields				contains either titleonly (link titles should be searched), desconly (only description bodies should be searched)
     * @param	string		$terms				is either 'all' (use query as entered, words without prefix should default to "have to be in field") or 'any' (ignore search query parts and just return all posts that contain any of the specified words)
     * @param	array		$sort_by_sql		contains SQL code for the ORDER BY part of a query
     * @param	string		$sort_key			is the key of $sort_by_sql for the selected sorting
     * @param	string		$sort_dir			is either a or d representing ASC and DESC
     * @param	string		$sort_days			specifies the maximum amount of days a post may be old
     * @param	array		$ex_cid_ary			specifies an array of category ids which should not be searched
     * @param	int			$cat_id				is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched
     * @param	array		&$id_ary			passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
     * @param	int			$start				indicates the first index of the page
     * @param	int			$per_page			number of ids each page is supposed to contain
     * @return	int								total number of results
     */
    public function keyword_search($keywords_ary, $fields, $terms, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_cid_ary, $cat_id, &$id_ary, $start, $per_page)
    {
        $matches = array();
        switch ($fields) {
            case 'titleonly':
                $matches[] = 'l.link_name';
                break;
            case 'desconly':
                $matches[] = 'l.link_description';
                break;
            default:
                $matches = array('l.link_name', 'l.link_description');
        }
        $search_query = '';
        foreach ($keywords_ary as $word) {
            $match_search_query = '';
            foreach ($matches as $match) {
                $match_search_query .= ($match_search_query ? ' OR ' : '') . 'LOWER(' . $match . ') ';
                $match_search_query .= $this->db->sql_like_expression(str_replace('*', $this->db->get_any_char(), $this->db->get_any_char() . strtolower($word) . $this->db->get_any_char()));
            }
            $search_query .= (!$search_query ? '' : ($terms == 'all' ? ' AND ' : ' OR ')) . '(' . $match_search_query . ')';
        }
        $direction = $sort_dir == 'd' ? 'DESC' : 'ASC';
        if (is_array($sort_by_sql[$sort_key])) {
            $sql_sort_order = implode(' ' . $direction . ', ', $sort_by_sql[$sort_key]) . ' ' . $direction;
        } else {
            $sql_sort_order = $sort_by_sql[$sort_key] . ' ' . $direction;
        }
        $sql_array = array('SELECT' => 'l.link_id', 'FROM' => array(DIR_LINK_TABLE => 'l'), 'WHERE' => 'l.link_active = 1
				' . ($search_query ? 'AND (' . $search_query . ')' : '') . '
				' . (sizeof($ex_cid_ary) ? ' AND ' . $this->db->sql_in_set('l.link_cat', $ex_cid_ary, true) : '') . '
				' . ($cat_id ? ' AND ' . $this->db->sql_in_set('l.link_cat', $cat_id) : '') . '
				' . ($sort_days ? ' AND l.link_time >= ' . (time() - $sort_days * 86400) : ''), 'ORDER_BY' => $sql_sort_order);
        if ($sql_sort_order[0] == 'u') {
            $sql_array['LEFT_JOIN'][] = array('FROM' => array(USERS_TABLE => 'u'), 'ON' => 'u.user_id = l.link_user_id');
        }
        $sql = $this->db->sql_build_query('SELECT', $sql_array);
        $result = $this->db->sql_query($sql);
        while ($row = $this->db->sql_fetchrow($result)) {
            $id_ary[] = $row['link_id'];
        }
        $this->db->sql_freeresult($result);
        $total_match_count = sizeof($id_ary);
        $id_ary = array_slice($id_ary, $start, (int) $per_page);
        return $total_match_count;
    }

 /**
  * Get post data
  *
  * @param mixed|false $topic_first_or_last_post (first|last)
  * @param array $post_ids
  * @param bool|false $limit
  * @param int $start
  * @param array $sql_array
  * @return array
  */
 public function get_post_data($topic_first_or_last_post = false, $post_ids = array(), $limit = false, $start = 0, $sql_array = array())
 {
     $sql = $this->db->sql_build_query('SELECT_DISTINCT', $this->_get_posts_sql_array($topic_first_or_last_post, $post_ids, $sql_array));
     $result = $this->db->sql_query_limit($sql, $limit, $start, $this->cache_time);
     $post_data = array();
     while ($row = $this->db->sql_fetchrow($result)) {
         $parse_flags = ($row['bbcode_bitfield'] ? OPTION_FLAG_BBCODE : 0) | OPTION_FLAG_SMILIES;
         $row['post_text'] = generate_text_for_display($row['post_text'], $row['bbcode_uid'], $row['bbcode_bitfield'], $parse_flags, true);
         $post_data[$row['topic_id']][$row['post_id']] = $row;
         $this->store['poster_ids'][] = $row['poster_id'];
         $this->store['poster_ids'][] = $row['post_edit_user'];
         $this->store['poster_ids'][] = $row['post_delete_user'];
         $this->store['attachments'][] = $row['post_id'];
     }
     $this->db->sql_freeresult($result);
     return $post_data;
 }

 protected function get_sql_statement()
 {
     $sql_ary = array('SELECT' => 'u.user_id, u.username, u.user_colour, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height', 'FROM' => array(USERS_TABLE => 'u'), 'WHERE' => $this->db->sql_in_set('u.user_type', array(USER_NORMAL, USER_FOUNDER)));
     $sql_method = '_set_' . $this->settings['query_type'] . '_sql';
     call_user_func_array(array($this, $sql_method), array(&$sql_ary));
     $this->_set_range_sql($sql_ary);
     return $this->db->sql_build_query('SELECT', $sql_ary);
 }

 /**
  * Fetch messages from the database
  */
 public function mchat_get_messages($sql_where, $total = 0, $offset = 0)
 {
     $sql_array = array('SELECT' => 'm.*, u.username, u.user_colour, u.user_avatar, u.user_avatar_type, u.user_avatar_width, u.user_avatar_height, u.user_allow_pm', 'FROM' => array($this->mchat_table => 'm'), 'LEFT_JOIN' => array(array('FROM' => array(USERS_TABLE => 'u'), 'ON' => 'm.user_id = u.user_id')), 'WHERE' => $sql_where, 'ORDER_BY' => 'm.message_id DESC');
     $sql = $this->db->sql_build_query('SELECT', $sql_array);
     $result = $this->db->sql_query_limit($sql, $total, $offset);
     $rows = $this->db->sql_fetchrowset($result);
     $this->db->sql_freeresult($result);
     return $rows;
 }

 /**
  * Fetches all topic solved data related to the given post.
  *
  * @param int $post_id ID of post to fetch topic/forum data for.
  *
  * @return mixed topic data, or false if not found
  */
 public function get_topic_data($post_id)
 {
     $select_sql_array = array('SELECT' => 't.topic_id, t.topic_poster, t.topic_status, t.topic_type, t.topic_solved, ' . 'f.forum_id, f.forum_allow_solve, f.forum_allow_unsolve, f.forum_lock_solved, ' . 'p.post_id', 'FROM' => array(FORUMS_TABLE => 'f', POSTS_TABLE => 'p', TOPICS_TABLE => 't'), 'WHERE' => 'p.post_id = ' . (int) $post_id . ' AND t.topic_id = p.topic_id AND f.forum_id = t.forum_id');
     $select_sql = $this->db->sql_build_query('SELECT', $select_sql_array);
     $result = $this->db->sql_query($select_sql);
     $topic_data = $this->db->sql_fetchrow($result);
     $this->db->sql_freeresult($result);
     return $topic_data;
 }

 /**
  * Получение id пользователя по email
  * @param string $email
  * @return int|bool
  */
 public function getUserIdByEmail($email = '')
 {
     $sql_array = array('SELECT' => 'user_id', 'FROM' => array(USERS_TABLE => 'u'), 'WHERE' => "user_email = '{$this->db->sql_escape($email)}'");
     $sql = $this->db->sql_build_query('SELECT', $sql_array);
     $result = $this->db->sql_query_limit($sql, 1);
     $result = $this->db->sql_fetchrow($result);
     $this->db->sql_freeresult();
     return isset($result['user_id']) ? $result['user_id'] : false;
 }

 /**
  * Get the usage count of the tag that is used the most
  *
  * @return int maximum
  */
 private function get_maximum_tag_usage_count()
 {
     $sql_array = array('SELECT' => 't.count', 'FROM' => array($this->table_prefix . tables::TAGS => 't'), 'WHERE' => 't.count > 0', 'ORDER_BY' => 't.count DESC');
     $sql = $this->db->sql_build_query('SELECT', $sql_array);
     $result = $this->db->sql_query_limit($sql, 1);
     $re = (int) $this->db->sql_fetchfield('count');
     $this->db->sql_freeresult($result);
     return $re;
 }