PHP driver_interface::sql_in_set方法代码示例

 public function modify_posting($event)
 {
     if ($event['mode'] == 'post' && !$event['forum_id']) {
         $forum_ary = array();
         $forum_read_ary = $this->auth->acl_getf('f_read');
         foreach ($forum_read_ary as $forum_id => $allowed) {
             if ($allowed['f_read'] && $this->auth->acl_get('f_post', $forum_id)) {
                 if (!$this->exclude_forum($forum_id, $this->config['newtopic_forum'])) {
                     continue;
                 }
                 $forum_ary[] = (int) $forum_id;
             }
         }
         if (sizeof($forum_ary)) {
             // Fetching topics of public forums
             $sql = 'SELECT forum_id, forum_name, forum_type FROM ' . FORUMS_TABLE . "\n\t\t\t\t\tWHERE " . $this->db->sql_in_set('forum_id', $forum_ary) . "\n\t\t\t\t\t\tAND forum_type != " . FORUM_LINK;
             $result = $this->db->sql_query($sql);
             $forumrow = $this->db->sql_fetchrowset($result);
             $this->db->sql_freeresult($result);
             $s_forum_options = '<select id="f" name="f" onchange="this.form.submit();">';
             foreach ($forumrow as $row) {
                 $s_forum_options .= '<option value="' . $row['forum_id'] . '"' . ($row['forum_id'] == $forum_id ? ' selected="selected"' : '') . '' . ($row['forum_type'] == FORUM_CAT ? ' disabled="disabled" class="disabled-option"' : '') . '>' . ($row['forum_type'] != FORUM_CAT ? '&nbsp;&nbsp;' : '') . $row['forum_name'] . '</option>';
                 $forum_id = $row['forum_type'] == FORUM_POST ? $row['forum_id'] : '';
             }
             $s_forum_options .= '</select>';
             $this->template->assign_vars(array('S_FORUM_OPTIONS' => $s_forum_options, 'S_FORUM_OPT_TRUE' => $forum_id ? true : false));
             $event['forum_id'] = $forum_id;
         }
     }
 }

    /**
     * Update BBCode order fields in the db on move up/down
     *
     * @param string $action The action move_up|move_down
     * @return null
     * @access public
     */
    public function move($action)
    {
        $bbcode_id = $this->request->variable('id', 0);
        if (!check_link_hash($this->request->variable('hash', ''), $action . $bbcode_id)) {
            trigger_error($this->user->lang('FORM_INVALID'), E_USER_WARNING);
        }
        // Get current order
        $sql = 'SELECT bbcode_order
			FROM ' . BBCODES_TABLE . "\n\t\t\tWHERE bbcode_id = {$bbcode_id}";
        $result = $this->db->sql_query($sql);
        $current_order = (int) $this->db->sql_fetchfield('bbcode_order');
        $this->db->sql_freeresult($result);
        // First one can't be moved up
        if ($current_order <= 1 && $action == 'move_up') {
            return;
        }
        $order_total = $current_order * 2 + $this->increment($action);
        // Update the db
        $sql = 'UPDATE ' . BBCODES_TABLE . '
			SET bbcode_order = ' . $order_total . ' - bbcode_order
			WHERE ' . $this->db->sql_in_set('bbcode_order', array($current_order, $current_order + $this->increment($action)));
        $this->db->sql_query($sql);
        // Resync bbcode_order
        $this->resynchronize_bbcode_order();
        // return a JSON response if this was an AJAX request
        if ($this->request->is_ajax()) {
            $json_response = new \phpbb\json_response();
            $json_response->send(array('success' => (bool) $this->db->sql_affectedrows()));
        }
    }

    public function add_page_header_links($event)
    {
        if (!empty($this->config['allow_visits_counter'])) {
            $this->language->add_lang('common', 'dmzx/counter');
            $sql = 'SELECT COUNT(*) AS visits_counter
				FROM ' . $this->visits_counter_table . '
				WHERE ' . $this->db->sql_in_set('uvc_ip', $this->user->ip);
            $result = $this->db->sql_query($sql);
            $visits_counter = (int) $this->db->sql_fetchfield('visits_counter');
            $this->db->sql_freeresult($result);
            $visits = $this->config['visits_counter'];
            if ($visits_counter == 0) {
                $sql_ary = array('uvc_ip' => $this->user->ip, 'uvc_timestamp' => time());
                $sql = 'INSERT INTO ' . $this->visits_counter_table . ' ' . $this->db->sql_build_array('INSERT', $sql_ary);
                $this->db->sql_query($sql);
                $this->config->increment('visits_counter', 1, true);
            } else {
                $sql_ary = array('uvc_timestamp' => time());
                $sql = 'UPDATE ' . $this->visits_counter_table . ' SET ' . $this->db->sql_build_array('UPDATE', $sql_ary) . '
					WHERE ' . $this->db->sql_in_set('uvc_ip', $this->user->ip);
                $this->db->sql_query($sql);
            }
            $timestamp = time() - 3600 * 24;
            $sql_ary = array($timestamp);
            $sql = 'DELETE FROM ' . $this->visits_counter_table . '
				WHERE uvc_timestamp < ' . $timestamp;
            $this->db->sql_query($sql);
            $sql = 'SELECT COUNT(*) AS num_del
				FROM ' . $this->visits_counter_table . ' ';
            $result = $this->db->sql_query($sql);
            $visitsok = (int) $this->db->sql_fetchfield('num_del');
            $this->template->assign_vars(array('UNIQUE_VISITS_COUNTER' => $this->language->lang('UNIQUE_VISITS_COUNTER', $visitsok)));
        }
    }

 /**
  * Run the cronjob.
  */
 public function run()
 {
     $time = strtotime('- ' . $this->config['ajaxshoutbox_prune_days'] . ' days');
     $sql = 'SELECT * FROM ' . $this->table . ' WHERE post_time <= ' . $time;
     $result = $this->db->sql_query($sql);
     $canpush = $this->push->canPush();
     $delete = array();
     while ($row = $this->db->sql_fetchrow($result)) {
         if ($canpush) {
             if ($this->push->delete($row['shout_id']) !== false) {
                 $delete[] = $row['shout_id'];
             }
         } else {
             $delete[] = $row['shout_id'];
         }
     }
     $this->db->sql_freeresult();
     if (sizeof($delete)) {
         $sql = 'DELETE FROM ' . $this->table . ' WHERE ' . $this->db->sql_in_set('shout_id', $delete);
         $this->db->sql_query($sql);
         $uuid = $this->user->data['user_id'];
         if (!$uuid) {
             $uuid = ANONYMOUS;
         }
         $this->log->add('admin', $uuid, $this->user->ip, 'LOG_AJAX_SHOUTBOX_PRUNED', time(), array(sizeof($delete)));
     }
     $this->config->set('shoutbox_prune_gc', time(), false);
 }

 /**
  * @param array $sql_array
  */
 private function _limit_by_group(array &$sql_array)
 {
     if (!empty($this->settings['group_ids'])) {
         $sql_array['FROM'][USER_GROUP_TABLE] = 'ug';
         $sql_array['WHERE'][] = 't.topic_poster = ug.user_id';
         $sql_array['WHERE'][] = $this->db->sql_in_set('ug.group_id', $this->settings['group_ids']);
     }
 }

    public function submit_attachments($data)
    {
        if (empty($data['attachment_data'])) {
            return;
        }
        $space_taken = $files_added = 0;
        $orphan_rows = array();
        foreach ($data['attachment_data'] as $pos => $attach_row) {
            $orphan_rows[(int) $attach_row['attach_id']] = array();
        }
        if (sizeof($orphan_rows)) {
            $sql = 'SELECT attach_id, filesize, physical_filename
				FROM ' . ATTACHMENTS_TABLE . '
				WHERE ' . $this->db->sql_in_set('attach_id', array_keys($orphan_rows)) . '
					AND is_orphan = 1
					AND poster_id = ' . (int) $this->user->data['user_id'];
            $result = $this->db->sql_query($sql);
            $orphan_rows = array();
            while ($row = $this->db->sql_fetchrow($result)) {
                $orphan_rows[$row['attach_id']] = $row;
            }
            $this->db->sql_freeresult($result);
        }
        foreach ($data['attachment_data'] as $pos => $attach_row) {
            if ($attach_row['is_orphan'] && !in_array($attach_row['attach_id'], array_keys($orphan_rows))) {
                continue;
            }
            if (!$attach_row['is_orphan']) {
                // update entry in db if attachment already stored in db and filespace
                $sql = 'UPDATE ' . ATTACHMENTS_TABLE . "\n\t\t\t\t\tSET attach_comment = '" . $this->db->sql_escape($attach_row['attach_comment']) . "'\n\t\t\t\t\tWHERE attach_id = " . (int) $attach_row['attach_id'] . '
						AND is_orphan = 0';
                $this->db->sql_query($sql);
            } else {
                // insert attachment into db
                if (!@file_exists($this->phpbb_root_path . $this->config['upload_path'] . '/' . basename($orphan_rows[$attach_row['attach_id']]['physical_filename']))) {
                    continue;
                }
                $space_taken += $orphan_rows[$attach_row['attach_id']]['filesize'];
                $files_added++;
                $attach_sql = array('post_msg_id' => $data['post_id'], 'topic_id' => $data['topic_id'], 'is_orphan' => 0, 'poster_id' => (int) $this->user->data['user_id'], 'attach_comment' => $attach_row['attach_comment']);
                $sql = 'UPDATE ' . ATTACHMENTS_TABLE . ' SET ' . $this->db->sql_build_array('UPDATE', $attach_sql) . '
					WHERE attach_id = ' . $attach_row['attach_id'] . '
						AND is_orphan = 1
						AND poster_id = ' . (int) $this->user->data['user_id'];
                $this->db->sql_query($sql);
            }
        }
        if ($space_taken && $files_added) {
            $this->config->set('upload_dir_size', $this->config['upload_dir_size'] + $space_taken, true);
            $this->config->set('num_files', $this->config['num_files'] + $files_added, true);
        }
    }

    public function users_online_string_sql($event)
    {
        $string_sql = $event['sql'];
        $online_users = $event['online_users']['online_users'];
        $sql = 'SELECT u.username, u.username_clean, u.user_id, u.user_type, u.user_allow_viewonline, u.user_colour, s.session_browser
			FROM ' . USERS_TABLE . ' u JOIN ' . SESSIONS_TABLE . ' s
			ON u.user_id = s.session_user_id
			WHERE ' . $this->db->sql_in_set('u.user_id', $event['online_users']['online_users']) . '
				AND s.session_time >= ' . (time() - $this->config['load_online_time'] * 60) . '
			GROUP BY u.user_id
			ORDER BY u.username_clean ASC';
        $event['sql'] = $sql;
    }

    /**
     * Get user's group ids
     *
     * @param array $user_id_ary An array of user ids to check
     * @return array An array of usergroup ids each user belongs to
     * @access public
     */
    public function get_users_groups($user_id_ary)
    {
        $group_id_ary = array();
        $sql = 'SELECT user_id, group_id
			FROM ' . USER_GROUP_TABLE . '
			WHERE ' . $this->db->sql_in_set('user_id', $user_id_ary, false, true);
        $result = $this->db->sql_query($sql);
        while ($row = $this->db->sql_fetchrow($result)) {
            $group_id_ary[$row['user_id']][] = $row['group_id'];
        }
        $this->db->sql_freeresult($result);
        return $group_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);
     }
 }

    /**
     * Processes the users's profile-field data as soon as it is grabbed from the DB.
     * It will use the profile-field data to try to grab info from the Battle.net API.
     *
     * @var    int|array $user_ids   Single user id or an array of ids
     * @var    array     $field_data Array with profile fields data
     *
     * @return array     $field_data Array with modified profile fields data
     */
    public function process_pf_grab($user_ids, $field_data)
    {
        $pbwow_config = $this->pbwow_config;
        if (isset($pbwow_config['bnetchars_enable']) && $pbwow_config['bnetchars_enable'] && $this->avatars_enabled_full) {
            $cachelife = isset($pbwow_config['bnetchars_cachetime']) ? intval($pbwow_config['bnetchars_cachetime']) : 86400;
            $apitimeout = isset($pbwow_config['bnetchars_timeout']) ? intval($pbwow_config['bnetchars_timeout']) : 1;
            $apikey = isset($pbwow_config['bnet_apikey']) ? $pbwow_config['bnet_apikey'] : false;
            // No API key? Cancel everything
            if (!$apikey) {
                return $field_data;
            }
            // Get all the characters of the requested users
            $sql = 'SELECT *
				FROM ' . $this->pbwow_chars_table . '
				WHERE ' . $this->db->sql_in_set('user_id', $user_ids);
            $result = $this->db->sql_query($sql);
            $char_data = $no_call_list = array();
            while ($row = $this->db->sql_fetchrow($result)) {
                $char_data[$row['user_id']] = $row;
            }
            $this->db->sql_freeresult($result);
            // Get a user list with all the API calls to be made
            $call_list = $this->generate_api_call_list($user_ids, $field_data, $char_data, $cachelife, $apikey);
            // Extract the users that have valid CPF input values, but don't need an API call
            if (isset($call_list['no_call'])) {
                $no_call_list = $call_list['no_call'];
                unset($call_list['no_call']);
            }
            // Get the character data from the Battle.net API
            $api_data = $this->call_bnet_api($call_list, $apitimeout);
            // Use the data from the API to save and merge with CPF data
            $field_data = $this->process_api_data($api_data, $no_call_list, $char_data, $field_data);
        }
        return $field_data;
    }

    /**
     *  The actual validation
     */
    public function check_answer()
    {
        // Well how did the user sorted it
        $options_left = $this->request->variable('sortables_options_left', array(0));
        $options_right = $this->request->variable('sortables_options_right', array(0));
        // Make sure the didn't submitted more options then it should (like trying everything... left/right: options ^ 2 )
        if ($this->total_options === sizeof($options_left) + sizeof($options_right)) {
            // Let's count how many options the user sorted correctly
            $sql = 'SELECT COUNT(*) AS total
							FROM ' . $this->table_sortables_answers . '
							WHERE question_id = ' . (int) $this->question . '
									AND ((answer_sort = 0 AND ' . $this->db->sql_in_set('answer_id', $options_left, false, true) . ')
									OR (answer_sort = 1 AND ' . $this->db->sql_in_set('answer_id', $options_right, false, true) . '))';
            $result = $this->db->sql_query($sql);
            $total_options_good = (int) $this->db->sql_fetchfield('total');
            // Now compare that amount with the total amount of options for this question
            if ($this->total_options === $total_options_good) {
                $this->solved = $this::SOLVED;
                // Remember this for the hidden fields
                $this->options_left = $options_left;
                $this->options_right = $options_right;
            }
            $this->db->sql_freeresult($result);
        }
        return $this->solved === $this::SOLVED;
    }

    /**
     * Synchronize queue topic url values.
     *
     * @return null
     */
    protected function sync_contrib_topics($start)
    {
        $i = 0;
        $limit = 250;
        $topic_type_where = $this->db->sql_in_set('topic_type', array(TITANIA_SUPPORT, TITANIA_QUEUE_DISCUSSION));
        $sql = 'SELECT contrib_id, contrib_type, contrib_name_clean
			FROM ' . $this->contribs_table;
        $result = $this->db->sql_query_limit($sql, $limit, $start);
        while ($row = $this->db->sql_fetchrow($result)) {
            $url = serialize(array('contrib_type' => $this->contrib_types->get($row['contrib_type'])->url, 'contrib' => $row['contrib_name_clean']));
            $where = 'parent_id = ' . (int) $row['contrib_id'] . '
				AND ' . $topic_type_where;
            $this->update_field($this->topics_table, 'topic', $url, $where);
            $i++;
        }
        $this->db->sql_freeresult();
        $sql = "SELECT topic_id, topic_url\n\t\t\tFROM {$this->topics_table}\n\t\t\tWHERE {$topic_type_where}";
        $result = $this->db->sql_query($sql);
        while ($row = $this->db->sql_fetchrow($result)) {
            $where = 'topic_id = ' . (int) $row['topic_id'];
            $this->update_field($this->posts_table, 'post', $row['topic_url'], $where);
        }
        $this->db->sql_freeresult($result);
        if ($i === $limit) {
            return $start + $limit;
        }
    }

    /**
     * {@inheritdoc}
     */
    public function delete_notifications($notification_type_id, $item_id, $parent_id = false, $user_id = false)
    {
        $sql = 'DELETE FROM ' . $this->notifications_table . '
			WHERE notification_type_id = ' . (int) $notification_type_id . '
				AND ' . (is_array($item_id) ? $this->db->sql_in_set('item_id', $item_id) : 'item_id = ' . (int) $item_id) . ($parent_id !== false ? ' AND ' . (is_array($parent_id) ? $this->db->sql_in_set('item_parent_id', $parent_id) : 'item_parent_id = ' . (int) $parent_id) : '') . ($user_id !== false ? ' AND ' . (is_array($user_id) ? $this->db->sql_in_set('user_id', $user_id) : 'user_id = ' . (int) $user_id) : '');
        $this->db->sql_query($sql);
    }

    /**
     * Delete attachments from database table
     */
    protected function delete_attachments_from_db()
    {
        /**
         * Perform additional actions before attachment(s) deletion
         *
         * @event core.delete_attachments_before
         * @var	string	mode			Variable containing attachments deletion mode, can be: post|message|topic|attach|user
         * @var	mixed	ids				Array or comma separated list of ids corresponding to the mode
         * @var	bool	resync			Flag indicating if posts/messages/topics should be synchronized
         * @var	string	sql_id			The field name to collect/delete data for depending on the mode
         * @var	array	post_ids		Array with post ids for deleted attachment(s)
         * @var	array	topic_ids		Array with topic ids for deleted attachment(s)
         * @var	array	message_ids		Array with private message ids for deleted attachment(s)
         * @var	array	physical		Array with deleted attachment(s) physical file(s) data
         * @since 3.1.7-RC1
         */
        $vars = array('mode', 'ids', 'resync', 'sql_id', 'post_ids', 'topic_ids', 'message_ids', 'physical');
        extract($this->dispatcher->trigger_event('core.delete_attachments_before', compact($vars)));
        // Delete attachments
        $sql = 'DELETE FROM ' . ATTACHMENTS_TABLE . '
			WHERE ' . $this->db->sql_in_set($this->sql_id, $this->ids);
        $sql .= $this->sql_where;
        $this->db->sql_query($sql);
        $this->num_deleted = $this->db->sql_affectedrows();
    }

    /**
     * @param array $allowed_extensions
     * @param bool $exclude_in_message
     * @param string $order_by
     * @return string
     */
    private function _get_attachment_sql($allowed_extensions, $exclude_in_message, $order_by)
    {
        return 'SELECT *
			FROM ' . ATTACHMENTS_TABLE . '
			WHERE ' . $this->db->sql_in_set('post_msg_id', $this->store['attachments']) . ($exclude_in_message ? ' AND in_message = 0' : '') . (sizeof($allowed_extensions) ? ' AND ' . $this->db->sql_in_set('extension', $allowed_extensions) : '') . '
			ORDER BY ' . $order_by;
    }