PHP SQL::query_scalar方法代码示例

 /**
  * count record for one anchor
  *
  * @param the selected anchor (e.g., 'article:12')
  * @param boolean TRUE if this can be optionnally avoided
  * @return the resulting count, or NULL on error
  */
 public static function count_for_anchor($anchor, $optional = FALSE)
 {
     global $context;
     // sanity check
     if (!$anchor) {
         return NULL;
     }
     // request the database only in hi-fi mode
     if ($optional && $context['skins_with_details'] != 'Y') {
         return NULL;
     }
     // select among available items
     $query = "SELECT COUNT(*) as count" . " FROM " . SQL::table_name('links') . " AS links" . " WHERE links.anchor LIKE '" . SQL::escape($anchor) . "'";
     return SQL::query_scalar($query);
 }

 /**
  * locate some reference
  *
  * @param string the anchor (e.g., 'article:123')
  * @return string longitude and latitude of the anchor, else NULL
  *
  * @see articles/layout_articles_as_contents.php
  * @see articles/layout_articles_as_feed.php
  */
 public static function locate_anchor($anchor)
 {
     global $context;
     // the request
     $query = "SELECT CONCAT(location.latitude, ', ', location.longitude) as geolocation FROM " . SQL::table_name('locations') . " AS location" . " WHERE (location.anchor LIKE '" . SQL::escape($anchor) . "') " . " ORDER BY location.edit_date DESC, location.geo_place_name LIMIT 0, 1";
     // the location, if any
     $output = SQL::query_scalar($query);
     return $output;
 }

 /**
  * get the unique handle associated to a section
  *
  * @param int or string the id or nick name of the section
  * @return the associated handle, or NULL if no record matches the input parameter
  */
 public static function &get_handle($id)
 {
     global $context;
     // sanity check
     if (!$id) {
         $output = NULL;
         return $output;
     }
     // ensure proper unicode encoding
     $id = (string) $id;
     $id = utf8::encode($id);
     // cache previous answers
     static $cache;
     if (!is_array($cache)) {
         $cache = array();
     }
     // cache hit
     if (isset($cache[$id])) {
         return $cache[$id];
     }
     // search by id or nick name
     $query = "SELECT handle FROM " . SQL::table_name('sections') . " AS sections" . " WHERE (sections.id = " . SQL::escape((int) $id) . ") OR (sections.nick_name LIKE '" . SQL::escape($id) . "')" . " ORDER BY edit_date DESC LIMIT 1";
     // do the job
     $output = SQL::query_scalar($query);
     // save in cache
     $cache[$id] = $output;
     // return by reference
     return $output;
 }

 /**
  * count records for one anchor
  *
  * @param string the selected anchor (e.g., 'article:12')
  * @param boolean TRUE if this can be optionnally avoided
  * @param array list of ids to avoid, if any
  * @return int the resulting count, or NULL on error
  */
 public static function count_for_anchor($anchor, $optional = FALSE, $avoid = NULL)
 {
     global $context;
     // sanity check
     if (!$anchor) {
         return NULL;
     }
     // request the database only in hi-fi mode
     if ($optional && $context['skins_with_details'] != 'Y') {
         return NULL;
     }
     // limit the scope of the request
     $where = Files::get_sql_where();
     // ids to avoid
     if ($avoid) {
         $where .= ' AND (files.id NOT IN (' . join(',', $avoid) . '))';
     }
     // select among available items
     $query = "SELECT COUNT(*) as count" . " FROM " . SQL::table_name('files') . " AS files" . " WHERE files.anchor LIKE '" . SQL::escape($anchor) . "' AND " . $where;
     return SQL::query_scalar($query);
 }

 /**
  * count records for one anchor
  *
  * Only articles matching following criteria are returned:
  * - article is visible (active='Y')
  * - article is restricted (active='R'), but the surfer is an authenticated member,
  * or YACS is allowed to show restricted teasers
  * - article is protected (active='N'), but surfer is an associate, and we are not feeding someone
  * - surfer is anonymous or the variant is 'boxes', and article has been officially published
  * - logged surfers are restricted to their own articles, plus published articles
  * - an expiry date has not been defined, or is not yet passed
  *
  * @param string the selected anchor (e.g., 'section:12')
  * @param boolean FALSE to include sticky pages, TRUE otherwise
  * @return int the resulting count, or NULL on error
  */
 public static function count_for_anchor($anchor, $without_sticky = FALSE)
 {
     global $context;
     // sanity check
     if (!$anchor) {
         return NULL;
     }
     // restrict the query to addressable content
     $where = Articles::get_sql_where();
     // avoid sticky articles
     if ($without_sticky) {
         $where .= " AND (articles.rank >= 10000)";
     }
     // anonymous surfers and subscribers will see only published articles
     if (!Surfer::is_member()) {
         $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'] . "')))";
     }
     // only consider live articles
     $where .= " AND ((articles.expiry_date is NULL) " . "OR (articles.expiry_date <= '" . NULL_DATE . "') OR (articles.expiry_date > '" . $context['now'] . "'))";
     // several anchors
     if (is_array($anchor)) {
         $items = array();
         foreach ($anchor as $token) {
             $items[] = "articles.anchor LIKE '" . SQL::escape($token) . "'";
         }
         $where_anchor = join(' OR ', $items);
         // or only one
     } else {
         $where_anchor = "articles.anchor LIKE '" . SQL::escape($anchor) . "'";
     }
     // select among available items
     $query = "SELECT COUNT(*) as count" . " FROM " . SQL::table_name('articles') . " AS articles" . " WHERE (" . $where_anchor . ") AND (" . $where . ")";
     return SQL::query_scalar($query);
 }

 /**
  * count dates attached to some anchor
  *
  * @param string the selected anchor (e.g., 'article:12')
  * @return int the resulting count, or NULL on error
  */
 public static function count_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" . " 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 (articles.anchor = '" . SQL::escape($anchor) . "') AND (" . $where . ")";
     return SQL::query_scalar($query);
 }

 * Only associates can use this script, except if no switch file is present.
 * Invocations are also accepted if the user table is missing or empty.
 *
 * If an associate user profile is created, apply it to the current surfing session.
 *
 * @author Bernard Paques
 * @author GnapZ
 * @tester Christian Loubechine
 * @reference
 * @license http://www.gnu.org/copyleft/lesser.txt GNU Lesser General Public License
 */
// common libraries
include_once '../shared/global.php';
// force the creation of a user profile if the user table does not exists, or is empty
$query = "SELECT count(*) FROM " . SQL::table_name('users');
if (!SQL::query_scalar($query, FALSE, $context['users_connection'])) {
    $permitted = TRUE;
} elseif (!(file_exists('../parameters/switch.on') || file_exists('../parameters/switch.off'))) {
    $permitted = TRUE;
} elseif (Surfer::is_associate()) {
    $permitted = TRUE;
} else {
    $permitted = FALSE;
}
// load localized strings
i18n::bind('control');
// load the skin
load_skin('control');
// the path to this page
$context['path_bar'] = array('control/' => i18n::s('Control Panel'));
// default page title

    // create table with the appropriate structure
    $query = "CREATE TABLE `" . SQL::table_name('top_uploaders') . "` (\n" . "user_id TEXT DEFAULT '',\n" . "user_label TEXT DEFAULT '',\n" . "`" . implode("` TEXT DEFAULT '', `", $all_years) . "` TEXT DEFAULT '',\n" . "total TEXT DEFAULT '')";
    SQL::query($query);
    // subset of targeted users
    $query = "SELECT \n" . "u.id AS user_id, \n" . "CONCAT(u.full_name, ' (', u.nick_name, ')') AS user_label, \n" . "count(a.id) AS total \n" . "FROM " . SQL::table_name('users') . " AS u, " . SQL::table_name('files') . " AS a WHERE (a.edit_id = u.id) \n" . "GROUP BY u.id \n" . "ORDER BY total DESC \n" . "LIMIT 0, 100";
    if ($result = SQL::query($query)) {
        // one record per user
        $records = 0;
        while ($item = SQL::fetch($result)) {
            $fields = array();
            foreach ($item as $name => $value) {
                $fields[] = "`" . $name . "`='" . SQL::escape($value) . "'";
            }
            // breakdown for recent years
            foreach ($all_years as $year) {
                $query = "SELECT count(id) FROM " . SQL::table_name('files') . " WHERE (edit_date LIKE '" . $year . "%') AND (edit_id = " . $item['user_id'] . ")";
                if ($value = SQL::query_scalar($query)) {
                    $fields[] = "`" . $year . "`='" . SQL::escape($value) . "'";
                } else {
                    $fields[] = "`" . $year . "`='0'";
                }
            }
            $query = "INSERT INTO " . SQL::table_name('top_uploaders') . ' SET ' . implode(', ', $fields);
            $records += SQL::query($query);
        }
        echo sprintf('%d records have been processed', $records) . BR;
    }
    // all done
    $time = round(get_micro_time() - $context['start_time'], 2);
    exit(sprintf('Script terminated in %.2f seconds.', $time) . BR);
}