PHP SQL::WHERE方法代码示例

/**
 * Get the categories list
 *
 * @param integer Parent category ID
 * @param integer Level
 * @return array Categories
 */
function fcpf_categories_select($parent_category_ID = -1, $level = 0)
{
    global $blog, $DB;
    $result_Array = array();
    $SQL = new SQL();
    $SQL->SELECT('cat_ID, cat_name');
    $SQL->FROM('T_categories');
    $SQL->WHERE('cat_blog_ID = ' . $DB->quote($blog));
    if ($parent_category_ID == -1) {
        $SQL->WHERE_and('cat_parent_ID IS NULL');
    } else {
        $SQL->WHERE('cat_parent_ID = ' . $DB->quote($parent_category_ID));
    }
    $SQL->ORDER_BY('cat_name');
    $categories = $DB->get_results($SQL->get());
    if (!empty($categories)) {
        foreach ($categories as $category) {
            $result_Array[] = array('value' => $category->cat_ID, 'label' => str_repeat('   ', $level) . $category->cat_name);
            $child_Categories_opts = fcpf_categories_select($category->cat_ID, $level + 1);
            if ($child_Categories_opts != '') {
                foreach ($child_Categories_opts as $cat) {
                    $result_Array[] = $cat;
                }
            }
        }
    }
    return $result_Array;
}

 /**
  * Get an object from cache by IP address
  *
  * Load into cache if necessary
  *
  * @param string IP address
  * @param boolean false if you want to return false on error
  * @param boolean true if function should die on empty/null
  */
 function &get_by_ip($req_ip, $halt_on_error = false, $halt_on_empty = false)
 {
     global $DB, $Debuglog;
     if (!isset($this->ip_index[$req_ip])) {
         // not yet in cache:
         $IP = ip2int($req_ip);
         $SQL = new SQL('Get ID of IP range by IP address');
         $SQL->SELECT('aipr_ID');
         $SQL->FROM('T_antispam__iprange');
         $SQL->WHERE('aipr_IPv4start <= ' . $DB->quote($IP));
         $SQL->WHERE_and('aipr_IPv4end >= ' . $DB->quote($IP));
         $IPRange_ID = $DB->get_var($SQL->get());
         // Get object from IPRangeCache bi ID
         $IPRange = $this->get_by_ID($IPRange_ID, $halt_on_error, $halt_on_empty);
         if ($IPRange) {
             // It is in IPRangeCache
             $this->ip_index[$req_ip] = $IPRange;
         } else {
             // not in the IPRangeCache
             if ($halt_on_error) {
                 debug_die("Requested {$this->objtype} does not exist!");
             }
             $this->ip_index[$req_ip] = false;
         }
     } else {
         $Debuglog->add("Retrieving <strong>{$this->objtype}({$req_ip})</strong> from cache");
     }
     return $this->ip_index[$req_ip];
 }

/**
 * Get SQL for active users which accept newsletter email
 *
 * @param array users IDs
 * @return object SQL
 */
function get_newsletter_users_sql($users_IDs)
{
    global $Settings;
    $SQL = new SQL();
    $SQL->SELECT('u.user_ID');
    $SQL->FROM('T_users u');
    $SQL->FROM_add('LEFT OUTER JOIN T_users__usersettings us ON u.user_ID = us.uset_user_ID');
    $SQL->FROM_add('AND us.uset_name = \'newsletter_news\'');
    $SQL->WHERE('u.user_ID IN ( ' . implode(', ', $users_IDs) . ' )');
    $SQL->WHERE_and('u.user_status IN ( \'activated\', \'autoactivated\' )');
    if ($Settings->get('def_newsletter_news')) {
        // If General setting "newsletter_news" = 1 we also should include all users without defined user's setting "newsletter_news"
        $SQL->WHERE_and('( us.uset_value = 1 OR us.uset_value IS NULL )');
    } else {
        // If General setting "newsletter_news" = 0 we take only users which accept newsletter email
        $SQL->WHERE_and('us.uset_value = 1');
    }
    return $SQL;
}

 /**
  * Load permissions
  *
  * @param integer Group ID
  */
 function load($grp_ID)
 {
     global $DB, $modules;
     // Get default group permission from each module
     foreach ($modules as $module) {
         $Module =& $GLOBALS[$module . '_Module'];
         if (method_exists($Module, 'get_default_group_permissions')) {
             // Module has pluggable permissions and we can add them to the current setting
             $this->add($module, $Module->get_default_group_permissions($grp_ID), $grp_ID);
         }
     }
     if ($grp_ID != 0) {
         // Select current group permission from database
         $SQL = new SQL();
         $SQL->SELECT('*');
         $SQL->FROM('T_groups__groupsettings');
         $SQL->WHERE('gset_grp_ID = ' . $grp_ID);
         $DB->begin();
         // Set current group permissions
         $existing_perm = array();
         foreach ($DB->get_results($SQL->get()) as $row) {
             $existing_perm[] = $row->gset_name;
             $this->permission_values[$row->gset_name] = $row->gset_value;
         }
         // Set default group permission if these permissions don't exist
         $update_permissions = false;
         foreach ($this->permission_values as $name => $value) {
             if (!in_array($name, $existing_perm)) {
                 $this->set($name, $value, $grp_ID);
                 $update_permissions = true;
             }
         }
         if ($update_permissions) {
             // We can update permission as there are some new permnissions
             $this->dbupdate($grp_ID);
         }
         $DB->commit();
     }
 }

 /**
  * Load data from Request form fields.
  *
  * @return boolean true if loaded data seems valid.
  */
 function load_from_Request()
 {
     param_string_not_empty('dom_name', T_('Please enter domain name.'));
     $dom_name = get_param('dom_name');
     $this->set('name', $dom_name);
     $dom_status = param('dom_status', 'string', true);
     $this->set('status', $dom_status, true);
     $dom_type = param('dom_type', 'string', true);
     $this->set('type', $dom_type, true);
     if (!param_errors_detected()) {
         // Check domains with the same name and type
         global $Messages, $DB;
         $SQL = new SQL();
         $SQL->SELECT('dom_ID');
         $SQL->FROM('T_basedomains');
         $SQL->WHERE('dom_ID != ' . $this->ID);
         $SQL->WHERE_and('dom_name = ' . $DB->quote($dom_name));
         $SQL->WHERE_and('dom_type = ' . $DB->quote($dom_type));
         if ($DB->get_var($SQL->get())) {
             $Messages->add(T_('Domain already exists with the same name and type.'));
         }
     }
     return !param_errors_detected();
 }

function contacts_groups($user_ID)
{
    global $current_User, $DB, $cache_user_contacts_groups;
    if (!is_array($cache_user_contacts_groups)) {
        // Execute only first time to init cache
        $cache_user_contacts_groups = array();
        // Get contacts of current user
        $groups_SQL = new SQL();
        $groups_SQL->SELECT('cgr_ID AS ID, cgu_user_ID AS user_ID, cgr_name AS name');
        $groups_SQL->FROM('T_messaging__contact_groupusers');
        $groups_SQL->FROM_add('LEFT JOIN T_messaging__contact_groups ON cgu_cgr_ID = cgr_ID');
        $groups_SQL->WHERE('cgr_user_ID = ' . $current_User->ID);
        $groups_SQL->ORDER_BY('cgr_name');
        $groups = $DB->get_results($groups_SQL->get());
        $remove_link = url_add_param(get_dispctrl_url('contacts'), 'action=remove_user&view=contacts&' . url_crumb('messaging_contacts'));
        foreach ($groups as $group) {
            // Init cache for groups for each contact of current user
            $group_name = $group->name . action_icon(T_('Remove user from this group'), 'remove', url_add_param($remove_link, 'user_ID=' . $group->user_ID . '&group_ID=' . $group->ID));
            if (isset($cache_user_contacts_groups[$group->user_ID])) {
                // nth group of this user
                $cache_user_contacts_groups[$group->user_ID] .= '<br />' . $group_name;
            } else {
                // first group of this user
                $cache_user_contacts_groups[$group->user_ID] = $group_name;
            }
        }
    }
    if (isset($cache_user_contacts_groups[$user_ID])) {
        // user has groups
        echo $cache_user_contacts_groups[$user_ID];
    }
}

/**
 * Create sample comments and display a process of creating
 *
 * @param integer Blog ID
 * @param integer Number of comments
 * @param integer Number of posts
 */
function tool_create_sample_comments($blog_ID, $num_comments, $num_posts)
{
    global $DB, $posttypes_specialtypes, $localtimenow, $Hit, $Messages, $Debuglog;
    $BlogCache =& get_BlogCache();
    $selected_Blog = $BlogCache->get_by_ID($blog_ID, false, false);
    if ($selected_Blog == NULL) {
        // Incorrect blog ID, Exit here
        return;
    }
    echo T_('Creating of the sample comments...');
    evo_flush();
    /**
     * Disable log queries because it increases the memory and stops the process with error "Allowed memory size of X bytes exhausted..."
     */
    $DB->log_queries = false;
    $curr_orderby = $selected_Blog->get_setting('orderby');
    if ($curr_orderby == 'RAND') {
        $curr_orderby .= '()';
    } else {
        $curr_orderby = 'post_' . $curr_orderby;
    }
    $curr_orderdir = $selected_Blog->get_setting('orderdir');
    // find the $num_posts latest posts in blog
    $SQL = new SQL();
    $SQL->SELECT('post_ID');
    $SQL->FROM('T_items__item');
    $SQL->FROM_add('INNER JOIN T_categories ON post_main_cat_ID = cat_ID');
    $SQL->WHERE('cat_blog_ID = ' . $DB->quote($blog_ID));
    $SQL->WHERE_and('post_status = ' . $DB->quote('published'));
    // Set condition to not create sample comments for special posts
    $SQL->WHERE_and('post_ptyp_ID NOT IN ( ' . $DB->quote($posttypes_specialtypes) . ' )');
    $SQL->ORDER_BY($curr_orderby . ' ' . $curr_orderdir . ', post_ID ' . $curr_orderdir);
    $SQL->LIMIT($num_posts);
    $items_result = $DB->get_results($SQL->get(), ARRAY_A, 'Find the x latest posts in blog');
    $count = 1;
    $fix_content = 'This is an auto generated comment for testing the moderation features.
					http://www.test.com/test_comment_';
    // go through on selected items
    foreach ($items_result as $row) {
        $item_ID = $row['post_ID'];
        $ItemCache =& get_ItemCache();
        $commented_Item =& $ItemCache->get_by_ID($item_ID);
        // create $num_comments comments for each item
        for ($i = 0; $i < $num_comments; $i++) {
            $author = 'Test ' . $count;
            $email = 'test_' . $count . '@test.com';
            $url = 'http://www.test-' . rand(1, 3) . '.com/test_comment_' . $count;
            $content = $fix_content . $count;
            for ($j = 0; $j < 50; $j++) {
                // create 50 random word
                $length = rand(1, 15);
                $word = generate_random_key($length, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ');
                $content = $content . ' ' . $word;
            }
            // create and save a new comment
            $Comment = new Comment();
            $Comment->set_Item($commented_Item);
            $Comment->set('status', 'draft');
            $Comment->set('author', $author);
            $Comment->set('author_email', $email);
            $Comment->set('author_url', $url);
            $Comment->set('content', $content);
            $Comment->set('date', date('Y-m-d H:i:s', $localtimenow));
            $Comment->set('author_IP', $Hit->IP);
            $Comment->dbsave();
            $count++;
            if ($count % 100 == 0) {
                // Display a process of creating by one dot for 100 comments
                echo ' .';
                evo_flush();
            }
            // Clear all debug messages, To avoid an error about full memory
            $Debuglog->clear('all');
        }
    }
    echo ' OK.';
    $Messages->add(sprintf(T_('Created %d comments.'), $count - 1), 'success');
}

    die('Please, do not access this page directly.');
}
load_class('regional/model/_currency.class.php', 'Currency');
load_funcs('regional/model/_regional.funcs.php');
// Get params from request
$s = param('s', 'string', '', true);
// Create query
$SQL = new SQL();
$SQL->SELECT('ctry_ID, ctry_code, ctry_name, curr_shortcut, curr_code, ctry_enabled, ctry_preferred, ctry_status, ctry_block_count');
$SQL->FROM('T_regional__country');
$SQL->FROM_add('LEFT JOIN T_regional__currency ON ctry_curr_ID=curr_ID');
$SQL->ORDER_BY('*, ctry_code ASC');
if (!empty($s)) {
    // We want to filter on search keyword:
    // Note: we use CONCAT_WS (Concat With Separator) because CONCAT returns NULL if any arg is NULL
    $SQL->WHERE('CONCAT_WS( " ", ctry_code, ctry_name, curr_code ) LIKE "%' . $DB->escape($s) . '%"');
}
// Create result set:
$Results = new Results($SQL->get(), 'ctry_', '-D');
$Results->title = T_('Countries') . get_manual_link('regional-countries-tab');
/*
 * STATUS TD:
 */
function ctry_td_enabled($ctry_enabled, $ctry_ID)
{
    $r = '';
    $redirect_ctrl = param('ctrl', 'string', 'countries');
    if ($ctry_enabled == true) {
        $r .= action_icon(T_('Disable the country!'), 'bullet_full', regenerate_url('ctrl,action', 'ctrl=countries&action=disable_country&ctry_ID=' . $ctry_ID . '&redirect_ctrl=' . $redirect_ctrl . '&' . url_crumb('country')));
    } else {
        $r .= action_icon(T_('Enable the country!'), 'bullet_empty', regenerate_url('ctrl,action', 'ctrl=countries&action=enable_country&ctry_ID=' . $ctry_ID . '&redirect_ctrl=' . $redirect_ctrl . '&' . url_crumb('country')));

/**
 * Detect timed out cron jobs and Send notifications
 *
 * @param array Task with error
 *             'name'
 *             'message'
 */
function detect_timeout_cron_jobs($error_task = NULL)
{
    global $DB, $time_difference, $cron_timeout_delay, $admin_url;
    $SQL = new SQL('Find cron timeouts');
    $SQL->SELECT('ctsk_ID, ctsk_name, ctsk_key');
    $SQL->FROM('T_cron__log');
    $SQL->FROM_add('INNER JOIN T_cron__task ON ctsk_ID = clog_ctsk_ID');
    $SQL->WHERE('clog_status = "started"');
    $SQL->WHERE_and('clog_realstart_datetime < ' . $DB->quote(date2mysql(time() + $time_difference - $cron_timeout_delay)));
    $SQL->GROUP_BY('ctsk_ID');
    $timeout_tasks = $DB->get_results($SQL->get(), OBJECT, $SQL->title);
    $tasks = array();
    if (count($timeout_tasks) > 0) {
        $cron_jobs_names = get_cron_jobs_config('name');
        foreach ($timeout_tasks as $timeout_task) {
            if (!empty($timeout_task->ctsk_name)) {
                // Task name is defined in DB
                $task_name = $timeout_task->ctsk_name;
            } else {
                // Try to get default task name by key:
                $task_name = isset($cron_jobs_names[$timeout_task->ctsk_key]) ? $cron_jobs_names[$timeout_task->ctsk_key] : $timeout_task->ctsk_key;
            }
            $tasks[$timeout_task->ctsk_ID] = array('name' => $task_name, 'message' => NT_('Cron job has timed out.'));
        }
        // Update timed out cron jobs:
        $DB->query('UPDATE T_cron__log
			  SET clog_status = "timeout"
			WHERE clog_ctsk_ID IN ( ' . $DB->quote(array_keys($tasks)) . ' )', 'Mark timeouts in cron jobs.');
    }
    if (!is_null($error_task)) {
        // Send notification with error task
        $tasks[$error_task['ID']] = $error_task;
    }
    if (count($tasks) > 0) {
        // Send notification email about timed out and error cron jobs to users with edit options permission
        $email_template_params = array('tasks' => $tasks);
        send_admin_notification(NT_('Scheduled task error'), 'scheduled_task_error_report', $email_template_params);
    }
}

/**
 * Check if the login is valid (user exists)
 *
 * @param string login
 * @return boolean true if OK
 */
function user_exists($login)
{
    global $DB;
    $SQL = new SQL();
    $SQL->SELECT('COUNT(*)');
    $SQL->FROM('T_users');
    $SQL->WHERE('user_login = "' . $DB->escape($login) . '"');
    $var = $DB->get_var($SQL->get());
    return $var > 0 ? true : false;
    // PHP4 compatibility
}

     // Get usernames by first chars for autocomplete jQuery plugin & TinyMCE autocomplete plugin
     $q = param('q', 'string', '');
     if (!is_valid_login($q) || evo_strlen($q) < 4) {
         // Restrict a wrong request
         debug_die('Wrong request');
     }
     // Add backslash for special char of sql operator LIKE
     $q = str_replace('_', '\\_', $q);
     if (utf8_strlen($q) == 0) {
         // Don't search logins with empty request
         $usernames = array();
     } else {
         $SQL = new SQL();
         $SQL->SELECT('user_login');
         $SQL->FROM('T_users');
         $SQL->WHERE('user_login LIKE ' . $DB->quote($q . '%'));
         $SQL->WHERE_and('user_status = "activated" OR user_status = "autoactivated"');
         $SQL->ORDER_BY('user_login');
         $usernames = $DB->get_col($SQL->get());
     }
     echo evo_json_encode($usernames);
     exit(0);
     // Exit here in order to don't display the AJAX debug info after JSON formatted data
     break;
 case 'get_user_salt':
     // Get the salt of the user from the given login info
     // Note: If there are more users with the received login then give at most 3 salt values for the 3 most recently active users
     // It always returns at least one salt value to show no difference between the existing and not existing user names
     $get_widget_login_hidden_fields = param('get_widget_login_hidden_fields', 'boolean', false);
     // Check that this action request is not a CSRF hacked request:
     if (!$get_widget_login_hidden_fields) {

 $new_User->set('firstname', $firstname);
 $new_User->set('lastname', $lastname);
 $new_User->set('gender', $gender);
 $new_User->set('source', $source);
 $new_User->set_email($email);
 $new_User->set_datecreated($localtimenow);
 if ($registration_ask_locale) {
     // set locale if it was prompted, otherwise let default
     $new_User->set('locale', $locale);
 }
 if (!empty($invitation)) {
     // Invitation code was entered on the form
     $SQL = new SQL();
     $SQL->SELECT('ivc_source, ivc_grp_ID');
     $SQL->FROM('T_users__invitation_code');
     $SQL->WHERE('ivc_code = ' . $DB->quote($invitation));
     $SQL->WHERE_and('ivc_expire_ts > ' . $DB->quote(date('Y-m-d H:i:s', $localtimenow)));
     if ($invitation_code = $DB->get_row($SQL->get())) {
         // Set source and group from invitation code
         $new_User->set('source', $invitation_code->ivc_source);
         $GroupCache =& get_GroupCache();
         if ($new_user_Group =& $GroupCache->get_by_ID($invitation_code->ivc_grp_ID, false, false)) {
             $new_User->set_Group($new_user_Group);
         }
     }
 }
 if ($new_User->dbinsert()) {
     // Insert system log about user's registration
     syslog_insert('User registration', 'info', 'user', $new_User->ID);
 }
 $new_user_ID = $new_User->ID;

 /**
  * Load a list of blogs owner by specific ID into the cache
  *
  * @param integer
  * @param string
  * @return array of IDs
  */
 function load_owner_blogs($owner_ID, $order_by = '', $order_dir = '')
 {
     global $DB, $Settings, $Debuglog;
     $Debuglog->add("Loading <strong>{$this->objtype}(owner={$owner_ID})</strong> into cache", 'dataobjects');
     if ($order_by == '') {
         // Use default value from settings
         $order_by = $Settings->get('blogs_order_by');
     }
     if ($order_dir == '') {
         // Use default value from settings
         $order_dir = $Settings->get('blogs_order_dir');
     }
     $SQL = new SQL();
     $SQL->SELECT('*');
     $SQL->FROM($this->dbtablename);
     $SQL->WHERE('blog_owner_user_ID = ' . $DB->quote($owner_ID));
     $SQL->ORDER_BY(gen_order_clause($order_by, $order_dir, 'blog_', 'blog_ID'));
     foreach ($DB->get_results($SQL->get(), OBJECT, 'Load owner blog list') as $row) {
         // Instantiate a custom object
         $this->instantiate($row);
     }
     return $DB->get_col(NULL, 0);
 }

 /**
  * Get all child comment IDs
  *
  * @param integer Parent comment ID
  * @return array Comment IDs
  */
 function get_child_comment_IDs($parent_comment_ID = NULL)
 {
     global $DB;
     if ($parent_comment_ID === NULL) {
         // Use current comment ID as main parent ID
         $parent_comment_ID = $this->ID;
     }
     // Get child comment of level 1
     $comments_SQL = new SQL();
     $comments_SQL->SELECT('comment_ID');
     $comments_SQL->FROM('T_comments');
     $comments_SQL->WHERE('comment_in_reply_to_cmt_ID = ' . $parent_comment_ID);
     $parent_comment_IDs = $DB->get_col($comments_SQL->get());
     $comment_IDs = array();
     foreach ($parent_comment_IDs as $comment_ID) {
         // Get all children recursively
         $comment_IDs[] = $comment_ID;
         $child_comment_IDs = $this->get_child_comment_IDs($comment_ID);
         foreach ($child_comment_IDs as $child_comment_ID) {
             $comment_IDs[] = $child_comment_ID;
         }
     }
     return $comment_IDs;
 }

/**
 * Get the unique url name
 *
 * @param string Source text
 * @param string Table name
 * @param string Field name
 * @return string category's url name
 */
function wp_unique_urlname($source, $table, $field)
{
    global $DB;
    // Replace special chars/umlauts, if we can convert charsets:
    load_funcs('locales/_charset.funcs.php');
    $url_name = strtolower(replace_special_chars($source));
    $url_number = 1;
    $url_name_correct = $url_name;
    do {
        // Check for unique url name in DB
        $SQL = new SQL();
        $SQL->SELECT($field);
        $SQL->FROM($table);
        $SQL->WHERE($field . ' = ' . $DB->quote($url_name_correct));
        $category = $DB->get_var($SQL->get());
        if ($category) {
            // Category already exists with such url name; Change it
            $url_name_correct = $url_name . '-' . $url_number;
            $url_number++;
        }
    } while (!empty($category));
    return $url_name_correct;
}