PHP SQL::get方法代码示例

 /**
  * 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 number of users for newsletter from UserList filterset
 *
 * @return array 
 * 		'all' - Number of accounts in filterset
 * 		'active' - Number of active accounts in filterset
 * 		'newsletter' - Number of active accounts which accept newsletter email
 */
function get_newsletter_users_numbers()
{
    $numbers = array('all' => 0, 'active' => 0, 'newsletter' => 0);
    load_class('users/model/_userlist.class.php', 'UserList');
    // Initialize users list from session cache in order to know number of users
    $UserList = new UserList('admin');
    $UserList->memorize = false;
    $UserList->load_from_Request();
    $users_IDs = $UserList->filters['users'];
    if (count($users_IDs)) {
        // Found users in the filterset
        global $DB;
        $numbers['all'] = count($users_IDs);
        // Get number of all active users
        $SQL = new SQL();
        $SQL->SELECT('COUNT( * )');
        $SQL->FROM('T_users');
        $SQL->WHERE('user_ID IN ( ' . implode(', ', $users_IDs) . ' )');
        $SQL->WHERE_and('user_status IN ( \'activated\', \'autoactivated\' )');
        $numbers['active'] = $DB->get_var($SQL->get());
        // Get number of all active users which accept newsletter email
        $SQL = get_newsletter_users_sql($users_IDs);
        $SQL->SELECT('COUNT( * )');
        $numbers['newsletter'] = $DB->get_var($SQL->get());
    }
    return $numbers;
}

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

/**
 * Uninstall b2evolution: Delete DB & Cache files
 */
function uninstall_b2evolution()
{
    global $DB;
    /* REMOVE PAGE CACHE */
    load_class('_core/model/_pagecache.class.php', 'PageCache');
    // Remove general page cache
    $PageCache = new PageCache(NULL);
    $PageCache->cache_delete();
    // Skip if T_blogs table is already deleted. Note that db_delete() will not throw any errors on missing tables.
    if ($DB->query('SHOW TABLES LIKE "T_blogs"')) {
        // Get all blogs
        $blogs_SQL = new SQL();
        $blogs_SQL->SELECT('blog_ID');
        $blogs_SQL->FROM('T_blogs');
        $blogs = $DB->get_col($blogs_SQL->get());
        $BlogCache =& get_BlogCache('blog_ID');
        foreach ($blogs as $blog_ID) {
            $Blog = $BlogCache->get_by_ID($blog_ID);
            // Remove page cache of current blog
            $PageCache = new PageCache($Blog);
            $PageCache->cache_delete();
        }
    }
    /* REMOVE DATABASE */
    db_delete();
    echo '<p>' . T_('Reset done!') . '</p>';
}

 /**
  * {@inheritdoc}
  */
 public function get($key, &$token = null)
 {
     $return = parent::get($key, $token);
     if ($token !== null) {
         // BYTEA data return streams - we actually need the data in
         // serialized format, not some silly stream
         $token = $this->serialize($return);
     }
     return $return;
 }

 /**
  * Return Data-Model for the View
  */
 private function getModel()
 {
     require_once 'classes/SQL.php';
     $model = new stdClass();
     $model->template = 'admin/HighscoreTable';
     $model->isAdmin = person::getPermissionName() == 'admin' ? 'true' : 'false';
     $model->gameID = $this->getGameId();
     $sql = new SQL();
     $sql->connect();
     $model->data = $sql->get("SELECT * FROM ViewHighscore ORDER BY GamerMainScore DESC");
     return $model;
 }

 /**
  * Add new or edit Categories in DB
  * paramters = POST: Categorie ID and Categorie-Properties
  */
 public function saveKategorien()
 {
     require 'classes/SQL.php';
     $sql = new SQL();
     $sql->connect();
     $id = mysql_real_escape_string(htmlentities($_POST['katID']));
     $bez = mysql_real_escape_string(htmlentities($_POST['bezTxt']));
     $desc = mysql_real_escape_string(htmlentities($_POST['descTxt']));
     $returnData;
     $success = true;
     if ($id == 0 && $bez != "") {
         $sql->doThat('INSERT INTO quizCategories(bezeichnung, beschreibung) VALUES ("' . $bez . '","' . $desc . '")');
         $returnData = $sql->get('SELECT * FROM quizCategories WHERE bezeichnung="' . $bez . '" AND beschreibung="' . $desc . '" LIMIT 1');
     } elseif ($bez != "") {
         $sql->doThat('UPDATE quizCategories SET bezeichnung="' . $bez . '", beschreibung="' . $desc . '" WHERE id=' . $id);
         $returnData = $sql->get('SELECT * FROM quizCategories WHERE id=' . $id);
     } else {
         $success = false;
     }
     $arr = array('success' => $success, 'row' => array('id' => $returnData[0]->id, 'bez' => $returnData[0]->bezeichnung, 'desc' => $returnData[0]->beschreibung));
     return json_encode($arr);
 }

 /**
  * Return QuestionTable-Page as HTML-View
  * parameters = POST: CategorieID
  */
 public function getQuestionTable()
 {
     require 'classes/SQL.php';
     require 'classes/view.php';
     $categorie = htmlentities($_POST['katID']);
     $model = new stdClass();
     $model->template = 'admin/QuestionTable';
     $sql = new SQL();
     $sql->connect();
     $model->data = $sql->get("SELECT * FROM ViewQuestionQuote WHERE categorie='" . $categorie . "' ORDER BY question");
     $view = new view();
     return $view->loadTemplate($model);
 }

/**
 * Get number of users for newsletter from UserList filterset
 *
 * @return array Numbers of users:
 *     'all' - Currently selected recipients (Accounts which accept newsletter emails)
 *     'active' - Already received (Accounts which have already been sent this newsletter)
 *     'newsletter' - Ready to send (Accounts which have not been sent this newsletter yet)
 */
function get_newsletter_users_numbers()
{
    $numbers = array('all' => 0, 'active' => 0, 'newsletter' => 0);
    $users_IDs = get_filterset_user_IDs();
    if (count($users_IDs)) {
        // Found users in the filterset
        global $DB;
        $numbers['all'] = count($users_IDs);
        // Get number of all active users
        $SQL = new SQL();
        $SQL->SELECT('COUNT( * )');
        $SQL->FROM('T_users');
        $SQL->WHERE('user_ID IN ( ' . implode(', ', $users_IDs) . ' )');
        $SQL->WHERE_and('user_status IN ( \'activated\', \'autoactivated\' )');
        $numbers['active'] = $DB->get_var($SQL->get());
        // Get number of all active users which accept newsletter email
        $SQL = get_newsletter_users_sql($users_IDs);
        $SQL->SELECT('COUNT( * )');
        $numbers['newsletter'] = $DB->get_var($SQL->get());
    }
    return $numbers;
}

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

 * @copyright (c)2009-2015 by Francois Planque - {@link http://fplanque.com/}
 * Parts of this file are copyright (c)2009 by The Evo Factory - {@link http://www.evofactory.com/}.
 *
 * @package evocore
 */
if (!defined('EVO_MAIN_INIT')) {
    die('Please, do not access this page directly.');
}
global $Blog;
// Create query
$SQL = new SQL();
$SQL->SELECT('t.*, IF( tb.itc_ityp_ID > 0, 1, 0 ) AS type_enabled');
$SQL->FROM('T_items__type AS t');
$SQL->FROM_add('LEFT JOIN T_items__type_coll AS tb ON itc_ityp_ID = ityp_ID AND itc_coll_ID = ' . $Blog->ID);
// Create result set:
$Results = new Results($SQL->get(), 'ityp_');
$Results->title = T_('Item/Post/Page types') . get_manual_link('managing-item-types');
// get reserved and default ids
global $default_ids;
$default_ids = ItemType::get_default_ids();
/**
 * Callback to build possible actions depending on post type id
 *
 */
function get_actions_for_itemtype($id)
{
    global $default_ids;
    $action = action_icon(T_('Duplicate this Post Type...'), 'copy', regenerate_url('action', 'ityp_ID=' . $id . '&action=new'));
    if (!ItemType::is_reserved($id)) {
        // Edit all post types except of not reserved post type
        $action = action_icon(T_('Edit this Post Type...'), 'edit', regenerate_url('action', 'ityp_ID=' . $id . '&action=edit')) . $action;

$SQL->FROM_add('LEFT JOIN T_track__goalcat ON gcat_ID = goal_gcat_ID');
if (!empty($final)) {
    // We want to filter on final goals only:
    $SQL->WHERE_and('goal_redir_url IS NULL');
}
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_and('CONCAT_WS( " ", goal_name, goal_key, goal_redir_url ) LIKE "%' . $DB->escape($s) . '%"');
}
if (!empty($cat)) {
    // We want to filter on category:
    $SQL->WHERE_and('goal_gcat_ID = ' . $DB->quote($cat));
}
// Create result set:
$Results = new Results($SQL->get(), 'goals_', '-A');
$Results->Cache =& get_GoalCache();
$Results->title = T_('Goals') . get_manual_link('goal-settings');
/**
 * Callback to add filters on top of the result set
 *
 * @param Form
 */
function filter_goals(&$Form)
{
    $Form->checkbox_basic_input('final', get_param('final'), T_('Final only') . ' •');
    $Form->text('s', get_param('s'), 30, T_('Search'), '', 255);
    $GoalCategoryCache =& get_GoalCategoryCache(NT_('All'));
    $GoalCategoryCache->load_all();
    $Form->select_input_object('cat', get_param('cat'), $GoalCategoryCache, T_('Category'), array('allow_none' => true));
}

/**
 * Create sample messages and display a process of creating
 *
 * @param integer Number of loops
 * @param integer Number of messages in each conversation
 * @param integer Number of words in each message
 * @param integer Max # of participants in a conversation
 */
function tool_create_sample_messages($num_loops, $num_messages, $num_words, $max_users)
{
    global $Messages, $DB;
    echo T_('Creating of the sample messages...');
    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;
    // Get all users
    $SQL = new SQL();
    $SQL->SELECT('user_ID');
    $SQL->FROM('T_users');
    $users = $DB->get_col($SQL->get());
    if (count($users) < 2) {
        // No users
        $Messages->add(T_('At least two users must exist in DB to create the messages'), 'error');
        $action = 'show_create_messages';
        break;
    }
    $count_threads = 0;
    $count_messages = 0;
    for ($l = 0; $l < $num_loops; $l++) {
        $user_links = array();
        foreach ($users as $from_user_ID) {
            foreach ($users as $to_user_ID) {
                if ($from_user_ID == $to_user_ID || isset($user_links[(string) $from_user_ID . '-' . $to_user_ID])) {
                    continue;
                }
                $user_links[$from_user_ID . '-' . $to_user_ID] = 1;
                // Insert thread
                $DB->query('INSERT INTO T_messaging__thread ( thrd_title, thrd_datemodified )
					VALUES ( ' . $DB->quote(generate_random_key(16)) . ', ' . $DB->quote(date('Y-m-d H:i:s')) . ' )');
                $thread_ID = $DB->insert_id;
                $count_threads++;
                for ($m = 0; $m < $num_messages; $m++) {
                    $msg_text = '';
                    for ($w = 0; $w < $num_words; $w++) {
                        $msg_text .= generate_random_key(8) . ' ';
                    }
                    $message_user_ID = $m % 2 == 0 ? $from_user_ID : $to_user_ID;
                    // Insert message
                    $DB->query('INSERT INTO T_messaging__message ( msg_author_user_ID , msg_datetime, msg_thread_ID, msg_text )
						VALUES ( ' . $DB->quote($message_user_ID) . ', ' . $DB->quote(date('Y-m-d H:i:s')) . ', ' . $DB->quote($thread_ID) . ', ' . $DB->quote($msg_text) . ' )');
                    $count_messages++;
                    if ($count_messages % 100 == 0) {
                        // Display a process of creating by one dot for 100 users
                        echo ' .';
                        evo_flush();
                    }
                }
                // Insert link for thread & user
                $DB->query('INSERT INTO T_messaging__threadstatus ( tsta_thread_ID , tsta_user_ID, tsta_first_unread_msg_ID )
					VALUES ( ' . $DB->quote($thread_ID) . ', ' . $DB->quote($from_user_ID) . ', NULL ),
								 ( ' . $DB->quote($thread_ID) . ', ' . $DB->quote($to_user_ID) . ', NULL )');
            }
        }
        /** Create one conversation between all users ( limit by $max_users ) **/
        // Insert thread
        $DB->query('INSERT INTO T_messaging__thread ( thrd_title, thrd_datemodified )
			VALUES ( ' . $DB->quote(generate_random_key(16)) . ', ' . $DB->quote(date('Y-m-d H:i:s')) . ' )');
        $thread_ID = $DB->insert_id;
        $count_threads++;
        $user_number = 0;
        for ($m = 0; $m < $num_messages; $m++) {
            $msg_text = '';
            for ($w = 0; $w < $num_words; $w++) {
                $msg_text .= generate_random_key(8) . ' ';
            }
            // Insert message
            $DB->query('INSERT INTO T_messaging__message ( msg_author_user_ID , msg_datetime, msg_thread_ID, msg_text )
				VALUES ( ' . $DB->quote($users[$user_number]) . ', ' . $DB->quote(date('Y-m-d H:i:s')) . ', ' . $DB->quote($thread_ID) . ', ' . $DB->quote($msg_text) . ' )');
            $count_messages++;
            $user_number++;
            if ($user_number == count($users) || $user_number == $max_users - 1) {
                // Reset user number to start of the list
                $user_number = 0;
            }
        }
        // Insert the links between thread & users
        $threadstatuses = array();
        foreach ($users as $u => $user_ID) {
            $threadstatuses[] = '( ' . $DB->quote($thread_ID) . ', ' . $DB->quote($user_ID) . ', NULL )';
            if ($u == $max_users - 1) {
                // limit by max users in one thread
                break;
            }
        }
        $DB->query('INSERT INTO T_messaging__threadstatus ( tsta_thread_ID , tsta_user_ID, tsta_first_unread_msg_ID )
			VALUES ' . implode(', ', $threadstatuses));
    }
    echo ' OK.';
//.........这里部分代码省略.........

    // Filter by start date
    $SQL->WHERE_and('emlog_timestamp >= ' . $DB->quote($datestart . ' 00:00:00'));
    $count_SQL->WHERE_and('emlog_timestamp >= ' . $DB->quote($datestart . ' 00:00:00'));
}
if (!empty($datestop)) {
    // Filter by end date
    $SQL->WHERE_and('emlog_timestamp <= ' . $DB->quote($datestop . ' 23:59:59'));
    $count_SQL->WHERE_and('emlog_timestamp <= ' . $DB->quote($datestop . ' 23:59:59'));
}
if (!empty($email)) {
    // Filter by email
    $email = utf8_strtolower($email);
    $SQL->WHERE_and('emlog_to LIKE ' . $DB->quote($email));
    $count_SQL->WHERE_and('emlog_to LIKE ' . $DB->quote($email));
}
$Results = new Results($SQL->get(), 'emlog_', 'D', $UserSettings->get('results_per_page'), $count_SQL->get());
$Results->title = T_('Sent emails') . get_manual_link('sent-emails');
/**
 * Callback to add filters on top of the result set
 *
 * @param Form
 */
function filter_email_sent(&$Form)
{
    global $datestart, $datestop, $email;
    $Form->date_input('datestartinput', $datestart, T_('From date'));
    $Form->date_input('datestopinput', $datestop, T_('To date'));
    $Form->text_input('email', $email, 40, T_('Email'));
}
$Results->filter_area = array('callback' => 'filter_email_sent', 'presets' => array('all' => array(T_('All'), $admin_url . '?ctrl=email&amp;tab=sent')));
$Results->cols[] = array('th' => T_('ID'), 'order' => 'emlog_ID', 'th_class' => 'shrinkwrap', 'td_class' => 'right', 'td' => '$emlog_ID$');