PHP SQL::query_first方法代码示例

 /**
  * retrieve one record by name
  *
  * You can access $result['value'] and $result['edit_date'] after one single
  * fetch.
  *
  * @param string the id of the value to be retrieved
  * @param string an optional default value
  * @return string cached information, or NULL if the no accurate information is available for this id
  */
 public static function get_record($id, $default_value = NULL)
 {
     global $context;
     // sanity check
     if (!$id) {
         $output = NULL;
         return $output;
     }
     // select among available items -- exact match
     $query = "SELECT * FROM " . SQL::table_name('values') . " WHERE id LIKE '" . SQL::escape($id) . "'";
     // do not report on error
     if (!($item = SQL::query_first($query, TRUE))) {
         return $item;
     }
     // default value
     if (!isset($item['value']) || !$item['value']) {
         $item['value'] = $default_value;
     }
     // we have a valid item
     return $item;
 }

 /**
  * get enrolment record
  *
  * @param string to designate the target anchor
  * @param int target user, or NULL for current surfer
  * @return array enrolment attributes, or NULL
  */
 public static function get_record($reference, $id = NULL)
 {
     global $context;
     // which surfer?
     if (!$id) {
         $id = Surfer::get_id();
     }
     // look for surfer id, if any
     if ($id) {
         $where = "user_id LIKE '" . SQL::escape($id) . "'";
     } elseif (isset($_REQUEST['surfer_address']) && $_REQUEST['surfer_address']) {
         $where = "user_email LIKE '" . SQL::escape($_REQUEST['surfer_address']) . "'";
     } elseif ($email = Surfer::get_email_address()) {
         $where = "user_email LIKE '" . SQL::escape($email) . "'";
     } else {
         return NULL;
     }
     // get at least one record
     $query = "SELECT * FROM " . SQL::table_name('enrolments') . " WHERE (anchor LIKE '" . $reference . "') AND " . $where;
     return SQL::query_first($query);
 }

 /**
  * get some statistics for some sections
  *
  * Only sections matching following criteria are returned:
  * - section is visible (active='Y')
  * - section is restricted (active='R'), but surfer is a logged user
  * - section is hidden (active='N'), but surfer is an associate
  *
  * Non-activated and expired sections are counted as well.
  *
  * @param string the selected anchor (e.g., 'section:12')
  * @return array the resulting ($count, $min_date, $max_date) array
  *
  * @see sections/delete.php
  * @see sections/index.php
  * @see sections/layout_sections.php
  * @see sections/layout_sections_as_yahoo.php
  * @see sections/view.php
  */
 public static function stat_for_anchor($anchor = '')
 {
     global $context;
     // limit the query to one level
     if ($anchor) {
         $where = "(sections.anchor LIKE '" . SQL::escape($anchor) . "')";
     } else {
         $where = "(sections.anchor='' OR sections.anchor is NULL)";
     }
     // show everything if we are about to suppress a section
     if (!preg_match('/delete\\.php/', $context['script_url'])) {
         // display active and restricted items
         $where .= "AND (sections.active='Y'";
         // list restricted sections to authenticated surfers
         if (Surfer::is_logged()) {
             $where .= " OR sections.active='R'";
         }
         // list hidden sections to associates, editors and readers
         if (Surfer::is_empowered('S')) {
             $where .= " OR sections.active='N'";
         }
         $where .= ")";
         // hide sections removed from index maps
         $where .= " AND (sections.index_map = 'Y')";
         // non-associates will have only live sections
         if ($anchor && !Surfer::is_empowered()) {
             $where .= " AND ((sections.activation_date is NULL)" . "\tOR (sections.activation_date <= '" . $context['now'] . "'))" . " AND ((sections.expiry_date is NULL)" . "\tOR (sections.expiry_date <= '" . NULL_DATE . "') OR (sections.expiry_date > '" . $context['now'] . "'))";
         }
     }
     // list sections
     $query = "SELECT COUNT(*) as count, MIN(edit_date) as oldest_date, MAX(edit_date) as newest_date" . " FROM " . SQL::table_name('sections') . " AS sections" . " WHERE " . $where;
     $output = SQL::query_first($query);
     return $output;
 }

 /**
  * pull most recent notification
  *
  * This script will wait for new updates before providing them to caller.
  * Because of potential time-outs, you have to care of retries.
  *
  * @return array attributes of the oldest notification, if any
  *
  * @see users/heartbit.php
  */
 public static function pull()
 {
     global $context;
     // return by reference
     $output = NULL;
     // only authenticated surfers can be notified
     if (!Surfer::get_id()) {
         Safe::header('Status: 401 Unauthorized', TRUE, 401);
         die(i18n::s('You are not allowed to perform this operation.'));
     }
     // only consider recent records -- 180 = 3 minutes * 60 seconds
     $threshold = gmstrftime('%Y-%m-%d %H:%M:%S', time() - 180);
     // the query to get time of last update
     $query = "SELECT * FROM " . SQL::table_name('notifications') . " AS notifications " . " WHERE (notifications.recipient = " . SQL::escape(Surfer::get_id()) . ")" . "\tAND (edit_date >= '" . SQL::escape($threshold) . "')" . " ORDER BY notifications.edit_date" . " LIMIT 1";
     // stop if there is nothing to return
     if (!($record = SQL::query_first($query)) || !isset($record['data'])) {
         return 'NTR';
     }
     // restore the entire record
     $output = Safe::unserialize($record['data']);
     // localize on server-side message displayed by the client software
     $lines = array();
     switch ($output['type']) {
         case 'alert':
             // a new item has been created
             if (strpos($output['action'], ':create')) {
                 $lines[] = sprintf(i18n::s('New page: %s'), $output['title']) . "\n" . sprintf(i18n::s('%s by %s'), ucfirst(Anchors::get_action_label($output['action'])), $output['nick_name']) . "\n";
                 // surfer prompt
                 $lines[] = i18n::s('Would you like to browse the page?');
                 // else consider this as an update
             } else {
                 // provide a localized message
                 $lines[] = sprintf(i18n::s('Updated: %s'), $output['title']) . "\n" . sprintf(i18n::s('%s by %s'), ucfirst(Anchors::get_action_label($output['action'])), $output['nick_name']) . "\n";
                 // surfer prompt
                 $lines[] = i18n::s('Would you like to browse the page?');
             }
             break;
         case 'browse':
             // message is optional
             if (isset($output['message']) && trim($output['message'])) {
                 $lines[] = sprintf(i18n::s('From %s:'), $output['nick_name']) . "\n" . $output['message'] . "\n";
             }
             // address is mandatory
             $lines[] = i18n::s('Would you like to browse the page?');
             break;
         case 'hello':
             // message is optional
             if (isset($output['message']) && trim($output['message'])) {
                 $lines[] = sprintf(i18n::s('From %s:'), $output['nick_name']) . "\n" . $output['message'] . "\n";
             }
             // address is present on new chat
             if (isset($output['address']) && trim($output['address'])) {
                 $lines[] = i18n::s('Would you like to browse the page?');
             }
             break;
     }
     // content of the dialog box that will be displayed to surfer
     if (count($lines)) {
         $output['dialog_text'] = implode("\n", $lines);
     }
     // forget this notification
     $query = "DELETE FROM " . SQL::table_name('notifications') . " WHERE id = " . SQL::escape($record['id']);
     SQL::query($query, TRUE);
     // return the new notification
     return $output;
 }

 /**
  * get some statistics for one anchor
  *
  * @param the selected anchor (e.g., 'section:12')
  * @return the resulting ($count, $min_date, $max_date) array
  */
 public static function stat_for_anchor($anchor)
 {
     global $context;
     // sanity check
     if (!$anchor) {
         return NULL;
     }
     $anchor = SQL::escape($anchor);
     // select among available items
     $query = "SELECT COUNT(*) as count, MIN(edit_date) as oldest_date, MAX(edit_date) as newest_date" . " FROM " . SQL::table_name('versions') . " AS versions" . " WHERE (versions.anchor LIKE '" . SQL::escape($anchor) . "')";
     $output = SQL::query_first($query);
     return $output;
 }

 /**
  * get some statistics for one anchor
  *
  * @param the selected anchor (e.g., 'article:12')
  * @return the resulting ($count, $oldest_date, $newest_date, $total_size) array
  */
 public static function stat_for_anchor($anchor)
 {
     global $context;
     // sanity check
     if (!$anchor) {
         return NULL;
     }
     // limit the scope of the request
     $where = Files::get_sql_where();
     // select among available items
     $query = "SELECT COUNT(*) as count, MIN(edit_date) as oldest_date, MAX(edit_date) as newest_date" . ", SUM(file_size) as total_size" . " FROM " . SQL::table_name('files') . " AS files" . " WHERE files.anchor LIKE '" . SQL::escape($anchor) . "' AND " . $where;
     $output = SQL::query_first($query);
     return $output;
 }

 /**
  * count present users
  *
  * Only users matching following criteria are returned:
  * - user is visible (active='Y')
  * - user is restricted (active='R'), but surfer is a logged member
  * - user is restricted (active='N'), but surfer is an associate
  * - user has clicked during the last 15 minutes
  *
  * @return the resulting ($count, $min_date, $max_date) array
  *
  * @see users/index.php
  */
 public static function stat_present()
 {
     global $context;
     // limit the scope of the request
     $where = "users.active='Y'";
     if (Surfer::is_member()) {
         $where .= " OR users.active='R'";
     }
     if (Surfer::is_associate()) {
         $where .= " OR users.active='N'";
     }
     // present means 'a click not too long in the past'
     $threshold = gmstrftime('%Y-%m-%d %H:%M:%S', time() - 15 * 60);
     $where = "(" . $where . ") AND (click_date > '" . $threshold . "')";
     // select among available items
     $query = "SELECT COUNT(*) as count, MIN(users.edit_date) as oldest_date, MAX(users.edit_date) as newest_date" . " FROM " . SQL::table_name('users') . " AS users" . " WHERE " . $where;
     $output = SQL::query_first($query, FALSE, $context['users_connection']);
     return $output;
 }

$section_id = '';
if (isset($_REQUEST['anchor']) && strpos($_REQUEST['anchor'], 'section:') === 0) {
    $section_id = str_replace('section:', '', $_REQUEST['anchor']);
}
$section_id = strip_tags($section_id);
// offset, to navigate in result set
$offset = 1.0;
if (isset($_REQUEST['offset'])) {
    $offset = (double) $_REQUEST['offset'];
}
if ($offset > 1.0 || $offset < 0.0) {
    $offset = 1.0;
}
// minimum size for any search token - depends of mySQL setup
$query = "SHOW VARIABLES LIKE 'ft_min_word_len'";
if (!defined('MINIMUM_TOKEN_SIZE') && ($row = SQL::query_first($query)) && $row['Value'] > 0) {
    define('MINIMUM_TOKEN_SIZE', $row['Value']);
}
// by default MySQL indexes words with at least four chars
if (!defined('MINIMUM_TOKEN_SIZE')) {
    define('MINIMUM_TOKEN_SIZE', 4);
}
// kill short and redundant tokens; adapt to boolean search
$boolean_search = '';
$tokens = preg_split('/[\\s,]+/', $search);
if (@count($tokens)) {
    foreach ($tokens as $token) {
        // too short
        if (strlen(preg_replace('/&.+?;/', 'x', $token)) < MINIMUM_TOKEN_SIZE) {
            continue;
        }

 /**
  * get some statistics for one anchor
  *
  * @param the selected anchor (e.g., 'article:12')
  * @return the resulting ($count, $min_date, $max_date) array
  */
 public static function stat_past_for_anchor($anchor)
 {
     global $context;
     // restrict the query to addressable content
     $where = Articles::get_sql_where();
     // put only published pages in boxes
     if (isset($variant) && $variant == 'boxes') {
         $where .= " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')";
         // provide published pages to anonymous surfers
     } elseif (!Surfer::is_logged()) {
         $where .= " AND NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')";
         // logged surfers that are non-associates are restricted to their own articles, plus published articles
     } elseif (!Surfer::is_empowered()) {
         $where .= " AND ((articles.create_id=" . Surfer::get_id() . ") OR (NOT ((articles.publish_date is NULL) OR (articles.publish_date <= '0000-00-00'))" . " AND (articles.publish_date < '" . $context['now'] . "')))";
     }
     // now
     $match = gmstrftime('%Y-%m-%d %H:%M:%S');
     // select among available items
     $query = "SELECT COUNT(*) as count, MIN(articles.edit_date) as oldest_date, MAX(articles.edit_date) as newest_date " . " FROM " . SQL::table_name('dates') . " as dates " . ", " . SQL::table_name('articles') . " AS articles" . " WHERE ((dates.anchor_type LIKE 'article') AND (dates.anchor_id = articles.id))" . "\tAND (dates.date_stamp < '" . SQL::escape($match) . "') AND\t(articles.anchor = '" . SQL::escape($anchor) . "') AND " . $where;
     $output = SQL::query_first($query);
     return $output;
 }

 /**
  * get one documentation snippet
  *
  * @param string the name of the snippet to fetch
  * @return the resulting $row array, with at least keys: 'name', 'anchor' and 'content'
  */
 public static function get($name)
 {
     global $context;
     // select among available items
     $query = "SELECT * FROM " . SQL::table_name('phpdoc') . " AS phpdoc " . " WHERE phpdoc.name = '" . SQL::escape($name) . "'";
     $output = SQL::query_first($query);
     return $output;
 }

         $to_avoid[] = str_replace('`', '', SQL::table_name($token));
     }
 }
 //enumerate tables
 $queries = 0;
 $tables = SQL::list_tables($context['database']);
 while ($row = SQL::fetch_row($tables)) {
     // table name
     $table_name = $row[0];
     // skip unmatched prefixes
     if (isset($_REQUEST['backup_prefix']) && !preg_match('/' . preg_quote($_REQUEST['backup_prefix'], '/') . '/i', $table_name)) {
         continue;
     }
     // the string to re-create table structure
     $query = "SHOW CREATE TABLE " . $table_name;
     if (!($result = SQL::query_first($query)) || !isset($result['Create Table'])) {
         continue;
     }
     // strip constraints and keep only engine definition
     $create_query = preg_replace('/(ENGINE=\\w+)\\b.*$/i', '$1', $result['Create Table']);
     // split lines
     $create_query = str_replace('\\n', "\n", $create_query);
     // build the table creation query
     $sql = 'DROP TABLE IF EXISTS `' . $table_name . "`;\n\n" . $create_query . ";\n\n";
     if ($compressed) {
         gzwrite($handle, $sql);
     } else {
         fwrite($handle, $sql);
     }
     // skip content of some tables
     if (in_array($table_name, $to_avoid)) {

 /**
  * retrieve cached information
  *
  * @param string the id of the text to be retrieved
  * @return string cached information, or NULL if the no accurate information is available for this id
  */
 public static function get($id, $f_capa = true, $f_lang = true, $f_gmt_off = true)
 {
     global $context;
     // return by reference
     $output = NULL;
     // recover from previous poisoining, if any
     $context['cache_has_been_poisoned'] = FALSE;
     // always disable cache when server is not switched on
     if (!file_exists($context['path_to_root'] . 'parameters/switch.on')) {
         return $output;
     }
     // the sql back-end may be not available during software updates or on NO_MODEL_PRELOAD
     if (!is_callable(array('SQL', 'query'))) {
         return $output;
     }
     // maybe we don't have to cache
     if (isset($context['without_cache']) && $context['without_cache'] == 'Y') {
         return $output;
     }
     // sanity check
     if (!$id) {
         return $output;
     }
     // cached content depends on surfer capability
     if ($f_capa) {
         $id .= '/' . Surfer::get_capability();
     }
     // cached content depends on selected language
     if ($f_lang) {
         $id .= '/' . $context['language'];
     }
     // cached content depends on time offset
     if ($f_gmt_off) {
         $id .= '/' . Surfer::get_gmt_offset();
     }
     // select among available items -- exact match
     $query = "SELECT * FROM " . SQL::table_name('cache') . " AS cache" . " WHERE (cache.id LIKE '" . SQL::escape($id) . "')";
     // do not report on error
     if (!($item = SQL::query_first($query, TRUE))) {
         return $output;
     }
     // check item validity
     if ($item['expiry_date'] < gmstrftime('%Y-%m-%d %H:%M:%S')) {
         return $output;
     }
     // we have a valid cached item
     $output = $item['text'];
     return $output;
 }

             // send the message
             Mailer::notify(Surfer::from(), $recipient, $subject, $message, $headers, $attachments);
         }
     }
     // drop enrolment record
     $query = "DELETE FROM " . SQL::table_name('enrolments') . " WHERE id = " . SQL::escape($_REQUEST['target']);
     SQL::query($query);
 }
 // validate an application
 if (isset($_REQUEST['action']) && $_REQUEST['action'] == 'validate' && isset($_REQUEST['target']) && $_REQUEST['target']) {
     // update enrolment record
     $query = "UPDATE " . SQL::table_name('enrolments') . " SET approved = 'Y' WHERE id = " . SQL::escape($_REQUEST['target']);
     SQL::query($query);
     // list enrolment for this meeting
     $query = "SELECT * FROM " . SQL::table_name('enrolments') . " WHERE id = " . SQL::escape($_REQUEST['target']);
     if (($result = SQL::query_first($query)) && ($user = Users::get($result['user_id']))) {
         // add the page to the watch list
         Members::assign($anchor->get_reference(), 'user:' . $user['id']);
         // ensure that the enrolled person can access private pages
         if ($anchor->is_hidden()) {
             Members::assign('user:' . $user['id'], $anchor->get_reference());
         }
         // confirm enrolment by e-mail
         if ($user['email'] && preg_match(VALID_RECIPIENT, $user['email'])) {
             // use this email address
             if ($user['full_name']) {
                 $recipient = Mailer::encode_recipient($user['email'], $user['full_name']);
             } else {
                 $recipient = Mailer::encode_recipient($user['email'], $user['nick_name']);
             }
             // mail subject

 /**
  * get some statistics for some categories
  *
  * Only categories matching following criteria are returned:
  * - category is visible (active='Y')
  * - category is restricted (active='R'), but surfer is a logged user
  * - an anchor has been provided and category is hidden (active='N'), but surfer is an associate
  * - an expiry date has not been defined, or is not yet passed
  *
  * @param the selected anchor (e.g., 'category:12')
  * @return the resulting ($count, $min_date, $max_date) array
  */
 public static function stat_for_anchor($anchor)
 {
     global $context;
     // limit the scope of the request
     $where = "categories.active='Y'";
     if (Surfer::is_member()) {
         $where .= " OR categories.active='R'";
     }
     // list hidden categories to associates, but not on the category tree
     // they will be listed through a call to list_inactive_by_title() -- see categories/index.php
     if ($anchor && Surfer::is_associate()) {
         $where .= " OR categories.active='N'";
     }
     // only consider live categories
     $where = "(" . $where . ")" . " AND ((categories.expiry_date is NULL)" . "\tOR (categories.expiry_date <= '" . NULL_DATE . "') OR (categories.expiry_date > '" . $context['now'] . "'))";
     // limit the query to one level
     if ($anchor) {
         $where = "(categories.anchor LIKE '" . SQL::escape($anchor) . "') AND (" . $where . ')';
     } else {
         $where = "(categories.anchor='' OR categories.anchor is NULL) AND (" . $where . ')';
     }
     // select among available items
     $query = "SELECT COUNT(*) as count, MIN(edit_date) as oldest_date, MAX(edit_date) as newest_date" . " FROM " . SQL::table_name('categories') . " AS categories" . " WHERE " . $where;
     $output = SQL::query_first($query);
     return $output;
 }

 /**
  * get some statistics
  *
  * @return the number of rows in table
  *
  * @see control/index.php
  */
 public static function stat()
 {
     global $context;
     // select among available items
     $query = "SELECT COUNT(*) as count FROM " . SQL::table_name('profiles');
     $output = SQL::query_first($query);
     return $output;
 }