PHP db_query_fetch_all函数代码示例

function dynamic_menu_content()
{
    $cache_name = user_is_logged_in() ? 'logged_in_' . $_SESSION['class'] : 'guest';
    if (cache_start($cache_name, CONFIG_CACHE_TIME_DYNAMIC, CONST_CACHE_DYNAMIC_MENU_GROUP)) {
        $entries = db_query_fetch_all('SELECT
                title,
                internal_page,
                permalink,
                url,
                visibility
            FROM
                dynamic_menu
            WHERE
                ' . (user_is_logged_in() ? 'min_user_class <= ' . $_SESSION['class'] . ' AND (visibility = "private" OR visibility = "both")' : 'visibility = "public" OR visibility = "both"') . '
            ORDER BY priority DESC');
        foreach ($entries as $entry) {
            echo '
            <li>
                <a href="', $entry['internal_page'] ? CONFIG_SITE_URL . 'content?show=' . $entry['permalink'] : htmlspecialchars($entry['url']), '">', htmlspecialchars($entry['title']), '</a>
            </li>
            ';
        }
        cache_end($cache_name, CONST_CACHE_DYNAMIC_MENU_GROUP);
    }
}

function json_scoreboard()
{
    // generate a json scoreboard
    // this function is so hacky..
    // could probably do with a rewrite
    $user_types = db_select_all('user_types', array('id', 'title AS category'));
    if (empty($user_types)) {
        $user_types = array(array('id' => 0, 'category' => 'all'));
    }
    for ($i = 0; $i < count($user_types); $i++) {
        $scores = db_query_fetch_all('
            SELECT
               u.id AS user_id,
               u.team_name,
               u.competing,
               co.country_code,
               SUM(c.points) AS score,
               MAX(s.added) AS tiebreaker
            FROM users AS u
            LEFT JOIN countries AS co ON co.id = u.country_id
            LEFT JOIN submissions AS s ON u.id = s.user_id AND s.correct = 1
            LEFT JOIN challenges AS c ON c.id = s.challenge
            WHERE u.competing = 1 AND u.user_type = :user_type
            GROUP BY u.id
            ORDER BY score DESC, tiebreaker ASC', array('user_type' => $user_types[$i]['id']));
        unset($user_types[$i]['id']);
        for ($j = 0; $j < count($scores); $j++) {
            $user_types[$i]['teams'][htmlspecialchars($scores[$j]['team_name'])] = array('position' => $j + 1, 'score' => isset($scores[$j]['score']) ? $scores[$j]['score'] : 0, 'country' => $scores[$j]['country_code']);
        }
    }
    echo json_encode($user_types);
}

function json_scoreboard($user_type = null)
{
    $values = array();
    if (is_valid_id($user_type)) {
        $values['user_type'] = $user_type;
    }
    $scores = db_query_fetch_all('
        SELECT
           u.id AS user_id,
           u.team_name,
           co.country_code,
           SUM(c.points) AS score,
           MAX(s.added) AS tiebreaker
        FROM users AS u
        LEFT JOIN countries AS co ON co.id = u.country_id
        LEFT JOIN submissions AS s ON u.id = s.user_id AND s.correct = 1
        LEFT JOIN challenges AS c ON c.id = s.challenge
        WHERE
          u.competing = 1
          ' . (is_valid_id($user_type) ? 'AND u.user_type = :user_type' : '') . '
        GROUP BY u.id
        ORDER BY score DESC, tiebreaker ASC', $values);
    $scoreboard = array();
    for ($i = 0; $i < count($scores); $i++) {
        $scoreboard['standings'][$i] = array('pos' => $i + 1, 'team' => $scores[$i]['team_name'], 'score' => array_get($scores[$i], 'score', 0), 'country' => $scores[$i]['country_code']);
    }
    echo json_encode($scoreboard);
}

      <tbody>
    ';
$query = 'SELECT
           INET_NTOA(ipl.ip) AS ip,
           ipl.added,
           ipl.last_used,
           ipl.times_used,
           u.team_name,
           u.id AS user_id
        FROM ip_log AS ipl
        LEFT JOIN users AS u ON ipl.user_id = u.id
        ';
if (!empty($where)) {
    $query .= 'WHERE ' . implode('=? AND ', array_keys($where)) . '=? ';
}
$entries = db_query_fetch_all($query, array_values($where));
foreach ($entries as $entry) {
    echo '
    <tr>
        <td>
            <a href="', CONFIG_SITE_ADMIN_URL, 'list_ip_log?user_id=', htmlspecialchars($entry['user_id']), '">
                ', htmlspecialchars($entry['team_name']), '
            </a>
        </td>
        <td><a href="', CONFIG_SITE_ADMIN_URL, 'list_ip_log?ip=', htmlspecialchars($entry['ip']), '">', htmlspecialchars(CONFIG_GET_IP_HOST_BY_ADDRESS ? gethostbyaddr($entry['ip']) : '<i>Lookup disabled in config</i>'), '</a></td>
        <td>', date_time($entry['added']), '</td>
        <td>', date_time($entry['last_used']), '</td>
        <td>', number_format($entry['times_used']), '</td>
    </tr>
    ';
}

          <th>Team name</th>
          <th>Added</th>
          <th>Flag</th>
          <th>Correct</th>
          <th>Manage</th>
        </tr>
      </thead>
      <tbody>
    ';
$submissions = db_query_fetch_all('
    SELECT
       s.id,
       u.id AS user_id,
       u.team_name,
       s.added,
       s.correct,
       s.flag,
       c.id AS challenge_id,
       c.title AS challenge_title
    ' . $query . '
    ORDER BY s.added DESC
    LIMIT ' . $from . ', ' . $results_per_page, array_values($where));
foreach ($submissions as $submission) {
    echo '
    <tr>
        <td><a href="', CONFIG_SITE_URL, 'challenge.php?id=', htmlspecialchars($submission['challenge_id']), '">', htmlspecialchars($submission['challenge_title']), '</a></td>
        <td><a href="', CONFIG_SITE_ADMIN_URL, 'user.php?id=', htmlspecialchars($submission['user_id']), '">', htmlspecialchars($submission['team_name']), '</a></td>
        <td>', time_elapsed($submission['added']), ' ago</td>
        <td>', htmlspecialchars($submission['flag']), '</td>
        <td>
            ', $submission['correct'] ? '<img src="' . CONFIG_SITE_URL_STATIC_RESOURCES . 'img/accept.png" alt="Correct!" title="Correct!" />' : '<img src="' . CONFIG_SITE_URL_STATIC_RESOURCES . 'img/stop.png" alt="Wrong!" title="Wrong!" />', '

            GROUP BY u.id
            ORDER BY score DESC, tiebreaker ASC', array('user_type' => $user_type['id']));
            scoreboard($scores);
        }
    }
    echo '
        </div>  <!-- / span6 -->

        <div class="col-lg-6">
        ';
    section_head(lang_get('challenges'));
    $categories = db_query_fetch_all('
        SELECT
           id,
           title,
           available_from,
           available_until
        FROM
           categories
        WHERE
           available_from < ' . $now . ' AND
           exposed = 1
        ORDER BY title');
    challenges($categories);
    echo '
        </div> <!-- / span6 -->
    </div> <!-- / row -->
    ';
    cache_end(CONST_CACHE_NAME_SCORES);
}
foot();

<?php

require '../../include/mellivora.inc.php';
enforce_authentication(CONST_USER_CLASS_MODERATOR);
head('Site management');
menu_management();
check_server_configuration();
$categories = db_query_fetch_all('SELECT * FROM categories ORDER BY title');
if (empty($categories)) {
    message_generic('Welcome', 'Your CTF is looking a bit empty! Start by adding a category using the menu above.');
}
section_subhead('CTF Overview', '<a href="' . CONFIG_SITE_ADMIN_URL . 'visualise">Visualise challenge availability</a>', false);
foreach ($categories as $category) {
    echo '
    <h4>
    ', htmlspecialchars($category['title']), '
    <a href="edit_category.php?id=', htmlspecialchars($category['id']), '" class="btn btn-xs btn-primary">Edit category</a>
    <a href="new_challenge.php?category=', htmlspecialchars($category['id']), '" class="btn btn-xs btn-primary">Add challenge</a>
    </h4>
    ';
    $challenges = db_select_all('challenges', array('id', 'title', 'description', 'exposed', 'available_from', 'available_until', 'points'), array('category' => $category['id']), 'points ASC');
    if (empty($challenges)) {
        message_inline_blue('This category is empty! Use the link above to add a challenge.');
    } else {
        echo '
    <table class="table table-striped table-hover">
      <thead>
        <tr>
          <th>Title</th>
          <th>Description</th>
          <th class="center">Points</th>

if ($search_for) {
    $values['search_for_team_name'] = '%' . $search_for . '%';
    $values['search_for_email'] = '%' . $search_for . '%';
}
$from = get_pager_from($_GET);
$num_users = db_count_num('users');
$results_per_page = 100;
$users = db_query_fetch_all('
    SELECT
       u.id,
       u.email,
       u.team_name,
       u.added,
       u.class,
       u.enabled,
       co.country_name,
       co.country_code,
       COUNT(ipl.id) AS num_ips
    FROM users AS u
    LEFT JOIN ip_log AS ipl ON ipl.user_id = u.id
    LEFT JOIN countries AS co ON co.id = u.country_id
    ' . ($search_for ? 'WHERE u.team_name LIKE :search_for_team_name OR u.email LIKE :search_for_email' : '') . (verifySA() ? '' : 'WHERE u.instanceID =' . $_SESSION["IID"]) . '
    GROUP BY u.id
    ORDER BY u.team_name ASC
    LIMIT ' . $from . ', ' . $results_per_page, $values);
pager(CONFIG_SITE_ADMIN_URL . 'list_users/', count($users), $results_per_page, $from);
foreach ($users as $user) {
    echo '
    <tr>
        <td>
            <a href="', CONFIG_SITE_URL, 'user?id=', htmlspecialchars($user['id']), '">', htmlspecialchars($user['team_name']), '</a>
        </td>

if (strlen(array_get($_GET, 'code')) != 2) {
    message_error(lang_get('please_supply_country_code'));
}
$country = db_select_one('countries', array('id', 'country_name', 'country_code'), array('country_code' => $_GET['code']));
if (!$country) {
    message_error(lang_get('please_supply_country_code'));
}
head($country['country_name']);
if (cache_start(CONST_CACHE_NAME_COUNTRY . $_GET['code'], CONFIG_CACHE_TIME_COUNTRIES)) {
    section_head(htmlspecialchars($country['country_name']) . country_flag_link($country['country_name'], $country['country_code'], true), '', false);
    $scores = db_query_fetch_all('
            SELECT
               u.id AS user_id,
               u.team_name,
               u.competing,
               co.id AS country_id,
               co.country_name,
               co.country_code,
               SUM(c.points) AS score,
               MAX(s.added) AS tiebreaker
            FROM users AS u
            LEFT JOIN countries AS co ON co.id = u.country_id
            LEFT JOIN submissions AS s ON u.id = s.user_id AND s.correct = 1
            LEFT JOIN challenges AS c ON c.id = s.challenge
            WHERE u.competing = 1 AND co.id = :country_id
            GROUP BY u.id
            ORDER BY score DESC, tiebreaker ASC', array('country_id' => $country['id']));
    scoreboard($scores);
    cache_end(CONST_CACHE_NAME_COUNTRY . $_GET['code']);
}
foot();

      </thead>
      <tbody>
    ';
$from = get_pager_from($_GET);
$num_users = db_count_num('users');
$results_per_page = 100;
pager(CONFIG_SITE_ADMIN_URL . 'list_users/', $num_users, $results_per_page, $from);
$users = db_query_fetch_all('
    SELECT
       u.id,
       u.email,
       u.team_name,
       u.added,
       u.class,
       u.enabled,
       co.country_name,
       co.country_code,
       COUNT(ipl.id) AS num_ips
    FROM users AS u
    LEFT JOIN ip_log AS ipl ON ipl.user_id = u.id
    LEFT JOIN countries AS co ON co.id = u.country_id
    GROUP BY u.id
    ORDER BY u.team_name ASC
    LIMIT ' . $from . ', ' . $results_per_page);
foreach ($users as $user) {
    echo '
    <tr>
        <td>
            ', country_flag_link($user['country_name'], $user['country_code']), '
            <a href="', CONFIG_SITE_URL, 'user?id=', htmlspecialchars($user['id']), '">', htmlspecialchars($user['team_name']), '</a>
        </td>

function challenges($categories)
{
    $now = time();
    $num_participating_users = get_num_participating_users();
    foreach ($categories as $category) {
        echo '
        <table class="team-table table table-striped table-hover">
          <thead>
            <tr>
              <th>', htmlspecialchars($category['title']), '</th>
              <th class="center">', lang_get('points'), '</th>
              <th class="center"><span class="has-tooltip" data-toggle="tooltip" data-placement="top" title="% of actively participating users">', lang_get('percentage_solvers'), '</span></th>
              <th>', lang_get('first_solvers'), '</th>
            </tr>
          </thead>
          <tbody>
         ';
        $challenges = db_query_fetch_all('
            SELECT
               id,
               title,
               points,
               available_from
            FROM challenges
            WHERE
              available_from < ' . $now . ' AND
              category = :category AND
              exposed = 1
            ORDER BY points ASC', array('category' => $category['id']));
        foreach ($challenges as $challenge) {
            $num_solvers = db_count_num('submissions', array('correct' => 1, 'challenge' => $challenge['id']));
            echo '
            <tr>
                <td>
                    <a href="challenge?id=', htmlspecialchars($challenge['id']), '">', htmlspecialchars($challenge['title']), '</a>
                </td>

                <td class="center">
                    ', number_format($challenge['points']), '
                </td>

                <td class="center">
                    ', number_format($num_solvers / $num_participating_users * 100), '%
                </td>

                <td class="team-name">';
            $users = db_query_fetch_all('
                SELECT
                   u.id,
                   u.team_name
                FROM users AS u
                JOIN submissions AS s ON s.user_id = u.id
                WHERE
                   u.competing = 1 AND
                   s.correct = 1 AND
                   s.challenge = :challenge
                ORDER BY s.added ASC
                LIMIT 3', array('challenge' => $challenge['id']));
            if (count($users)) {
                $pos = 1;
                foreach ($users as $user) {
                    echo get_position_medal($pos++), '<a href="user?id=', htmlspecialchars($user['id']), '">', htmlspecialchars($user['team_name']), '</a><br />';
                }
            } else {
                echo '<i>', lang_get('unsolved'), '</i>';
            }
            echo '
                </td>
            </tr>';
        }
        echo '
        </tbody>
        </table>';
    }
}

       e.id,
       e.message,
       e.added,
       e.added_by,
       e.trace,
       INET_NTOA(e.user_ip) AS user_ip,
       u.team_name
    FROM exceptions AS e
    LEFT JOIN users AS u ON u.id = e.added_by
    ';
if (!empty($where)) {
    $query .= 'WHERE ' . implode('=? AND ', array_keys($where)) . '=? ';
}
$query .= 'ORDER BY e.id DESC
           LIMIT ' . $from . ', ' . CONST_NUM_EXCEPTIONS_PER_PAGE;
$exceptions = db_query_fetch_all($query, array_values($where));
foreach ($exceptions as $exception) {
    echo '
    <tr>
        <td>', htmlspecialchars($exception['message']), '</td>
        <td>', date_time($exception['added']), '</td>
        <td>', $exception['added_by'] ? '<a href="' . CONFIG_SITE_ADMIN_URL . 'user.php?id=' . htmlspecialchars($exception['added_by']) . '">' . htmlspecialchars($exception['team_name']) . '</a>' : '<i>N/A</i>', '
        </td>
        <td><a href="', CONFIG_SITE_ADMIN_URL, 'list_ip_log.php?ip=', htmlspecialchars($exception['user_ip']), '">', htmlspecialchars($exception['user_ip']), '</a></td>
    </tr>
    <tr>
        <td colspan="4">
            <pre>', nl2br(htmlspecialchars($exception['trace'])), ' </pre>
        </td>
    </tr>
    ';

          <th>Added by</th>
          <th>Type</th>
          <th>Priority</th>
          <th>Enabled</th>
          <th>Manage</th>
        </tr>
      </thead>
      <tbody>
    ';
$rules = db_query_fetch_all('
    SELECT
       re.id,
       re.added,
       re.added_by,
       re.rule,
       re.enabled,
       re.white,
       re.priority,
       u.team_name
    FROM restrict_email AS re
    LEFT JOIN users AS u ON re.added_by = u.id
    ORDER BY re.priority ASC');
foreach ($rules as $rule) {
    echo '
    <tr>
        <td>', htmlspecialchars($rule['rule']), '</td>
        <td>', date_time($rule['added']), '</td>
        <td>', htmlspecialchars($rule['team_name']), '</td>
        <td>
            ', $rule['white'] ? '<img src="' . CONFIG_SITE_URL_STATIC_RESOURCES . 'img/accept.png" alt="Whitelisted" title="Whitelisted" />' : '<img src="' . CONFIG_SITE_URL_STATIC_RESOURCES . 'img/stop.png" alt="Blacklisted" title="Blacklisted" />', '
        </td>

            <tr>
                <td>
                    <a href="challenge?id=', htmlspecialchars($challenge['id']), '">', htmlspecialchars($challenge['title']), '</a>
                </td>

                <td>
                    ', number_format($challenge['points']), '
                </td>

                <td>';
            $users = db_query_fetch_all('
                SELECT
                   u.id,
                   u.team_name
                FROM users AS u
                JOIN submissions AS s ON s.user_id = u.id
                WHERE
                   u.competing = 1 AND
                   s.correct = 1 AND
                   s.challenge=:challenge
                ORDER BY s.added ASC
                LIMIT 3', array('challenge' => $challenge['id']));
            if (count($users)) {
                $pos = 1;
                foreach ($users as $user) {
                    echo get_position_medal($pos++), '<a href="user?id=', htmlspecialchars($user['id']), '">', htmlspecialchars($user['team_name']), '</a><br />';
                }
            } else {
                echo '<i>Unsolved</i>';
            }
            echo '
                </td>

function print_user_ip_log($user_id, $limit = 0)
{
    validate_id($user_id);
    section_subhead('IP address usage', ($limit ? 'Limited to ' . $limit . ' results ' : '') . button_link('Show all for user', 'list_ip_log?user_id=' . htmlspecialchars($user_id)), false);
    echo '
        <table id="files" class="table table-striped table-hover">
          <thead>
            <tr>
              <th>IP</th>
              <th>Hostname</th>
              <th>First used</th>
              <th>Last used</th>
              <th>Times used</th>
            </tr>
          </thead>
          <tbody>
        ';
    $entries = db_query_fetch_all('
        SELECT
            INET_NTOA(ip) AS ip,
            added,
            last_used,
            times_used
        FROM ip_log
        WHERE user_id = :user_id
        ORDER BY last_used DESC
        ' . ($limit ? 'LIMIT ' . $limit : ''), array('user_id' => $user_id));
    foreach ($entries as $entry) {
        echo '
        <tr>
            <td><a href="', CONFIG_SITE_ADMIN_URL, 'list_ip_log.php?ip=', htmlspecialchars($entry['ip']), '">', htmlspecialchars($entry['ip']), '</a></td>
            <td>', CONFIG_GET_IP_HOST_BY_ADDRESS ? gethostbyaddr($entry['ip']) : '<i>Lookup disabled in config</i>', '</td>
            <td>', date_time($entry['added']), '</td>
            <td>', date_time($entry['last_used']), '</td>
            <td>', number_format($entry['times_used']), '</td>
        </tr>
        ';
    }
    echo '
          </tbody>
        </table>
         ';
}