PHP get_records_sql函数代码示例

 function doListAnnotations($url, $username, $block)
 {
     $query = new AnnotationSummaryQuery($url, $username, null, null);
     if ($query->error) {
         $this->httpError(400, 'Bad Request', 'Bad URL');
         return null;
     } elseif (isguest() && ANNOTATION_REQUIRE_USER) {
         $this->httpError(403, 'Forbidden', 'Anonymous listing not allowed');
         return null;
     } else {
         $querySql = $query->sql('section_type, section_name, quote_title');
         $annotationSet = get_records_sql($querySql);
         $annotations = array();
         if ($annotationSet) {
             $i = 0;
             foreach ($annotationSet as $r) {
                 $annotations[$i++] = AnnotationGlobals::recordToAnnotation($r);
             }
         }
         $format = $this->getQueryParam('format', 'atom');
         $logUrl = 'annotate.php?format=' . $format . ($username ? '&user=' . $username : '') . '&url=' . $url;
         add_to_log($query->handler->courseId, 'annotation', 'list', $logUrl);
         return $annotations;
     }
 }

 /**
  * gets all workshops for the config tree
  */
 function get_all_gradable_items()
 {
     global $CFG;
     $sql = "SELECT w.id, w.course, w.name, w.description as summary, c.id as cmid\n                  FROM {$CFG->prefix}workshop w\n            INNER JOIN {$CFG->prefix}course_modules c\n                    ON w.id = c.instance\n                 WHERE c.module = {$this->mainobject->modulesettings['workshop']->id}\n                   AND c.visible = 1\n                   AND w.course IN ({$this->mainobject->course_ids})\n              ORDER BY w.id";
     $workshops = get_records_sql($sql);
     $this->assessments = $workshops;
 }

function map_get_cells($brainstormid, $userid = null, $groupid = 0, $configdata = null)
{
    global $CFG;
    $accessClause = brainstorm_get_accessclauses($userid, $groupid);
    $sql = "\r\n        SELECT\r\n            id,\r\n            itemsource,\r\n            itemdest,\r\n            intvalue,\r\n            floatvalue,\r\n            blobvalue\r\n         FROM\r\n            {$CFG->prefix}brainstorm_operatordata AS od\r\n         WHERE\r\n            brainstormid = {$brainstormid} AND\r\n            operatorid = 'map'\r\n            {$accessClause}\r\n    ";
    $map = array();
    if ($maprecords = get_records_sql($sql)) {
        foreach ($maprecords as $record) {
            if (!$configdata || !@$configdata->quantified) {
                $map[$record->itemsource][$record->itemdest] = 1;
            } else {
                switch ($configdata->quantifiertype) {
                    case 'integer':
                        $map[$record->itemsource][$record->itemdest] = $record->intvalue;
                        break;
                    case 'float':
                        $map[$record->itemsource][$record->itemdest] = $record->floatvalue;
                        break;
                    case 'multiple':
                        $map[$record->itemsource][$record->itemdest] = unserialize($record->blobvalue);
                        break;
                    default:
                }
            }
        }
    }
    return $map;
}

function ma_delete_execute($modids)
{
    global $CFG;
    $in_list = "(" . join(", ", $modids) . ")";
    $sql = "SELECT cm.id as coursemodule, cm.section, cm.course, mo.name as modulename, instance\n        FROM {$CFG->prefix}course_modules cm\n        LEFT JOIN {$CFG->prefix}modules mo on mo.id=cm.module\n        WHERE cm.id IN {$in_list}";
    $records = get_records_sql($sql);
    $courses = array();
    $message = '';
    if ($records) {
        foreach ($records as $id => $record) {
            $modlib = "{$CFG->dirroot}/mod/{$record->modulename}/lib.php";
            $courses[] = $record->course;
            if (file_exists($modlib)) {
                include_once $modlib;
            } else {
                $message .= "This module is missing important code! ({$modlib})  ";
                continue;
            }
            $deleteinstancefunction = $record->modulename . "_delete_instance";
            if (!$deleteinstancefunction($record->instance)) {
                $message .= "Could not delete the {$record->modulename} (instance)  ";
                continue;
            }
            if (!delete_course_module($record->coursemodule)) {
                $message .= "Could not delete the {$record->modulename} (coursemodule)  ";
                continue;
            }
            if (!delete_mod_from_section($record->coursemodule, "{$record->section}")) {
                $message .= "Could not delete the {$record->modulename} from that section  ";
                continue;
            }
        }
    }
    return $message;
}

function resource_get_links(&$course, &$mod, $exclude_sitefile = true)
{
    global $CFG;
    $sql = "SELECT r.id, r.reference, r.name, r.summary, r.alltext, cm.id AS cmid\r\n             FROM {$CFG->prefix}resource r,\r\n                  {$CFG->prefix}course_modules cm,\r\n                  {$CFG->prefix}modules m\r\n             WHERE r.course    = '{$course->id}'\r\n               AND m.name      = 'resource'\r\n               AND cm.module   = m.id\r\n               AND cm.instance = r.id\r\n               AND cm.id = {$mod->id}";
    if ($resources = get_records_sql($sql)) {
        // 各テキストの取得
        $links = array();
        foreach ($resources as $resource) {
            preg_match_all('|/file.php/([^\\"]+)|', $resource->alltext, $matches);
            $links = array_merge($links, $matches[1]);
            preg_match_all('|/file.php/([^\\"]+)|', $resource->summary, $matches);
            $links = array_merge($links, $matches[1]);
            if (trim($resource->reference) && stristr($resource->reference, 'http://') === false || stristr($resources->reference, 'file.php/')) {
                $links[] = $course->id . '/' . $resource->reference;
            }
        }
        // 2008-10-29 暫定処置
        if ($exclude_sitefile) {
            $links = array_filter($links, 'is_not_sitefile');
        }
        return $links;
    } else {
        return null;
    }
}

 function get_content()
 {
     global $THEME, $CFG, $USER;
     // only for logged in users!
     if (!isloggedin() || isguest()) {
         return false;
     }
     // check for outgoing roaming permission first
     if (!has_capability('moodle/site:mnetlogintoremote', get_context_instance(CONTEXT_SYSTEM), NULL, false)) {
         return '';
     }
     if ($this->content !== NULL) {
         return $this->content;
     }
     // TODO: Test this query - it's appropriate? It works?
     // get the hosts and whether we are doing SSO with them
     $sql = "\n             SELECT DISTINCT \n                 h.id, \n                 h.name,\n                 h.wwwroot,\n                 a.name as application,\n                 a.display_name\n             FROM \n                 {$CFG->prefix}mnet_host h,\n                 {$CFG->prefix}mnet_application a,\n                 {$CFG->prefix}mnet_host2service h2s_IDP,\n                 {$CFG->prefix}mnet_service s_IDP,\n                 {$CFG->prefix}mnet_host2service h2s_SP,\n                 {$CFG->prefix}mnet_service s_SP\n             WHERE\n                 h.id != '{$CFG->mnet_localhost_id}' AND\n                 h.id = h2s_IDP.hostid AND\n                 h.applicationid = a.id AND\n                 h2s_IDP.serviceid = s_IDP.id AND\n                 s_IDP.name = 'sso_idp' AND\n                 h2s_IDP.publish = '1' AND\n                 h.id = h2s_SP.hostid AND\n                 h2s_SP.serviceid = s_SP.id AND\n                 s_SP.name = 'sso_idp' AND\n                 h2s_SP.publish = '1'\n             ORDER BY\n                 a.display_name,\n                 h.name";
     $hosts = get_records_sql($sql);
     $this->content = new stdClass();
     $this->content->items = array();
     $this->content->icons = array();
     $this->content->footer = '';
     if ($hosts) {
         foreach ($hosts as $host) {
             $icon = '<img src="' . $CFG->pixpath . '/i/' . $host->application . '_host.gif"' . ' class="icon" alt="' . get_string('server', 'block_mnet_hosts') . '" />';
             $this->content->icons[] = $icon;
             if ($host->id == $USER->mnethostid) {
                 $this->content->items[] = "<a title=\"" . s($host->name) . "\" href=\"{$host->wwwroot}\">" . s($host->name) . "</a>";
             } else {
                 $this->content->items[] = "<a title=\"" . s($host->name) . "\" href=\"{$CFG->wwwroot}/auth/mnet/jump.php?hostid={$host->id}\">" . s($host->name) . "</a>";
             }
         }
     }
     return $this->content;
 }

 function display_search_field($content = '')
 {
     global $CFG;
     $varcharcontent = sql_compare_text('content', 255);
     $sql = "SELECT DISTINCT {$varcharcontent} AS content\n                  FROM {$CFG->prefix}data_content\n                 WHERE fieldid={$this->field->id} AND content IS NOT NULL";
     $usedoptions = array();
     if ($used = get_records_sql($sql)) {
         foreach ($used as $data) {
             $value = $data->content;
             if ($value === '') {
                 continue;
             }
             $usedoptions[$value] = $value;
         }
     }
     $options = array();
     foreach (explode("\n", $this->field->param1) as $option) {
         $option = trim($option);
         if (!isset($usedoptions[$option])) {
             continue;
         }
         $options[$option] = $option;
     }
     if (!$options) {
         // oh, nothing to search for
         return '';
     }
     return choose_from_menu($options, 'f_' . $this->field->id, stripslashes($content), 'choose', '', 0, true);
 }

 /**
  * Add question-type specific form fields.
  *
  * @param MoodleQuickForm $mform the form being built.
  */
 function question_dataset_dependent_items_form($submiturl, $question, $regenerate)
 {
     global $QTYPES, $SESSION, $CFG;
     $this->regenerate = $regenerate;
     $this->question = $question;
     $this->qtypeobj =& $QTYPES[$this->question->qtype];
     //get the dataset defintions for this question
     if (empty($question->id)) {
         $this->datasetdefs = $this->qtypeobj->get_dataset_definitions($question->id, $SESSION->datasetdependent->definitionform->dataset);
     } else {
         if (empty($question->options)) {
             $this->get_question_options($question);
         }
         $this->datasetdefs = $this->qtypeobj->get_dataset_definitions($question->id, array());
     }
     foreach ($this->datasetdefs as $datasetdef) {
         // Get maxnumber
         if ($this->maxnumber == -1 || $datasetdef->itemcount < $this->maxnumber) {
             $this->maxnumber = $datasetdef->itemcount;
         }
     }
     foreach ($this->datasetdefs as $defid => $datasetdef) {
         if (isset($datasetdef->id)) {
             $this->datasetdefs[$defid]->items = get_records_sql(" SELECT itemnumber, definition, id, value\n                          FROM {$CFG->prefix}question_dataset_items\n                          WHERE definition = {$datasetdef->id} ");
         }
     }
     parent::moodleform($submiturl);
 }

 /**
  * Add question-type specific form fields.
  *
  * @param MoodleQuickForm $mform the form being built.
  */
 function question_dataset_dependent_items_form($submiturl, $question, $regenerate)
 {
     global $QTYPES, $SESSION, $CFG;
     $this->regenerate = $regenerate;
     $this->question = $question;
     $this->qtypeobj =& $QTYPES[$this->question->qtype];
     // Validate the question category.
     if (!($category = get_record('question_categories', 'id', $question->category))) {
         print_error('categorydoesnotexist', 'question', $returnurl);
     }
     $this->category = $category;
     $this->categorycontext = get_context_instance_by_id($category->contextid);
     //get the dataset defintions for this question
     if (empty($question->id)) {
         $this->datasetdefs = $this->qtypeobj->get_dataset_definitions($question->id, $SESSION->datasetdependent->definitionform->dataset);
     } else {
         if (empty($question->options)) {
             $this->get_question_options($question);
         }
         $this->datasetdefs = $this->qtypeobj->get_dataset_definitions($question->id, array());
     }
     foreach ($this->datasetdefs as $datasetdef) {
         // Get maxnumber
         if ($this->maxnumber == -1 || $datasetdef->itemcount < $this->maxnumber) {
             $this->maxnumber = $datasetdef->itemcount;
         }
     }
     foreach ($this->datasetdefs as $defid => $datasetdef) {
         if (isset($datasetdef->id)) {
             $this->datasetdefs[$defid]->items = get_records_sql(" SELECT itemnumber, definition, id, value\n                          FROM {$CFG->prefix}question_dataset_items\n                          WHERE definition = {$datasetdef->id} ");
         }
     }
     parent::moodleform($submiturl);
 }

function data_filter($courseid, $text)
{
    global $CFG;
    static $nothingtodo;
    static $contentlist;
    if (!empty($nothingtodo)) {
        // We've been here in this page already
        return $text;
    }
    // if we don't have a courseid, we can't run the query, so
    if (empty($courseid)) {
        return $text;
    }
    // Create a list of all the resources to search for. It may be cached already.
    if (empty($contentlist)) {
        // We look for text field contents only, and only if the field has
        // autolink enabled (param1).
        $sql = 'SELECT dc.id AS contentid, ' . 'dr.id AS recordid, ' . 'dc.content AS content, ' . 'd.id AS dataid ' . 'FROM ' . $CFG->prefix . 'data d, ' . $CFG->prefix . 'data_fields df, ' . $CFG->prefix . 'data_records dr, ' . $CFG->prefix . 'data_content dc ' . "WHERE (d.course = '{$courseid}' or d.course = '" . SITEID . "')" . 'AND d.id = df.dataid ' . 'AND df.id = dc.fieldid ' . 'AND d.id = dr.dataid ' . 'AND dr.id = dc.recordid ' . "AND df.type = 'text' " . "AND " . sql_compare_text('df.param1', 1) . " = '1'";
        if (!($datacontents = get_records_sql($sql))) {
            return $text;
        }
        $contentlist = array();
        foreach ($datacontents as $datacontent) {
            $currentcontent = trim($datacontent->content);
            $strippedcontent = strip_tags($currentcontent);
            if (!empty($strippedcontent)) {
                $contentlist[] = new filterobject($currentcontent, '<a class="data autolink" title="' . $strippedcontent . '" href="' . $CFG->wwwroot . '/mod/data/view.php?d=' . $datacontent->dataid . '&amp;rid=' . $datacontent->recordid . '" ' . $CFG->frametarget . '>', '</a>', false, true);
            }
        }
        // End foreach
    }
    return filter_phrases($text, $contentlist);
    // Look for all these links in the text
}

/**
 * Load the question data necessary in the reports.
 * - Remove description questions.
 * - Order questions in order that they are in the quiz
 * - Add question numbers.
 * - Add grade from quiz_questions_instance
 */
function quiz_report_load_questions($quiz)
{
    global $CFG;
    $questionlist = quiz_questions_in_quiz($quiz->questions);
    //In fact in most cases the id IN $questionlist below is redundant
    //since we are also doing a JOIN on the qqi table. But will leave it in
    //since this double check will probably do no harm.
    if (!($questions = get_records_sql("SELECT q.*, qqi.grade " . "FROM {$CFG->prefix}question q, " . "{$CFG->prefix}quiz_question_instances qqi " . "WHERE q.id IN ({$questionlist}) AND " . "qqi.question = q.id AND " . "qqi.quiz =" . $quiz->id))) {
        print_error('No questions found');
    }
    //Now we have an array of questions from a quiz we work out there question nos and remove
    //questions with zero length ie. description questions etc.
    //also put questions in order.
    $number = 1;
    $realquestions = array();
    $questionids = explode(',', $questionlist);
    foreach ($questionids as $id) {
        if ($questions[$id]->length) {
            // Ignore questions of zero length
            $realquestions[$id] = $questions[$id];
            $realquestions[$id]->number = $number;
            $number += $questions[$id]->length;
        }
    }
    return $realquestions;
}

 function get_question_options(&$question)
 {
     // Get the question answers and their respective tolerances
     // Note: question_numerical is an extension of the answer table rather than
     //       the question table as is usually the case for qtype
     //       specific tables.
     global $CFG;
     if (!($question->options->answers = get_records_sql("SELECT a.*, n.tolerance " . "FROM {$CFG->prefix}question_answers a, " . "     {$CFG->prefix}question_numerical n " . "WHERE a.question = {$question->id} " . "    AND   a.id = n.answer " . "ORDER BY a.id ASC"))) {
         notify('Error: Missing question answer!');
         return false;
     }
     $this->get_numerical_units($question);
     // If units are defined we strip off the default unit from the answer, if
     // it is present. (Required for compatibility with the old code and DB).
     if ($defaultunit = $this->get_default_numerical_unit($question)) {
         foreach ($question->options->answers as $key => $val) {
             $answer = trim($val->answer);
             $length = strlen($defaultunit->unit);
             if ($length && substr($answer, -$length) == $defaultunit->unit) {
                 $question->options->answers[$key]->answer = substr($answer, 0, strlen($answer) - $length);
             }
         }
     }
     return true;
 }

 function get_content()
 {
     global $USER, $CFG;
     if ($this->content !== NULL) {
         return $this->content;
     }
     $this->content = new stdClass();
     $this->content->text = '';
     $this->content->footer = '';
     if (empty($this->instance) or empty($USER->id) or isguest() or empty($CFG->messaging)) {
         return $this->content;
     }
     $this->content->footer = '<a href="' . $CFG->wwwroot . '/message/index.php" onclick="this.target=\'message\'; return openpopup(\'/message/index.php\', \'message\', \'menubar=0,location=0,scrollbars,status,resizable,width=400,height=500\', 0);">' . get_string('messages', 'message') . '</a>...';
     $users = get_records_sql("SELECT m.useridfrom as id, COUNT(m.useridfrom) as count,\n                                         u.firstname, u.lastname, u.picture, u.lastaccess\n                                       FROM {$CFG->prefix}user u, \n                                            {$CFG->prefix}message m \n                                       WHERE m.useridto = '{$USER->id}' \n                                         AND u.id = m.useridfrom\n                                    GROUP BY m.useridfrom, u.firstname,u.lastname,u.picture,u.lastaccess");
     //Now, we have in users, the list of users to show
     //Because they are online
     if (!empty($users)) {
         $this->content->text .= '<ul class="list">';
         foreach ($users as $user) {
             $timeago = format_time(time() - $user->lastaccess);
             $this->content->text .= '<li class="listentry"><div class="user"><a href="' . $CFG->wwwroot . '/user/view.php?id=' . $user->id . '&amp;course=' . $this->instance->pageid . '" title="' . $timeago . '">';
             $this->content->text .= print_user_picture($user->id, $this->instance->pageid, $user->picture, 0, true, false, '', false);
             $this->content->text .= fullname($user) . '</a></div>';
             $this->content->text .= '<div class="message"><a href="' . $CFG->wwwroot . '/message/discussion.php?id=' . $user->id . '" onclick="this.target=\'message_' . $user->id . '\'; return openpopup(\'/message/discussion.php?id=' . $user->id . '\', \'message_' . $user->id . '\', \'menubar=0,location=0,scrollbars,status,resizable,width=400,height=500\', 0);"><img class="iconsmall" src="' . $CFG->pixpath . '/t/message.gif" alt="" />&nbsp;' . $user->count . '</a>';
             $this->content->text .= '</div></li>';
         }
         $this->content->text .= '</ul>';
     } else {
         $this->content->text .= '<div class="info">';
         $this->content->text .= get_string('nomessages', 'message');
         $this->content->text .= '</div>';
     }
     return $this->content;
 }

/**
 * Retrieve the wall for a given userid.
 *
 * @return mixed Array of comment objects, else returns false.
 * @param unknown_type $userid The user / wall we are retrieving
 * @param unknown_type $limit Limit on the search
 * @param unknown_type $offset Offset
 */
function commentwall_getwall($userid, $limit = 3, $offset = 0)
{
    global $CFG;
    $query = "SELECT * from {$CFG->prefix}commentwall where wallowner={$userid} order by posted desc limit {$offset},{$limit}";
    // echo $query;
    return get_records_sql($query);
}

 function doListAnnotations($url, $username, $block, $all)
 {
     $handler = annotation_summary_query::handler_for_url($url);
     $user = get_record('user', 'username', $username);
     $summary = new annotation_summary_query($url, $handler, null, $user, null, false, $all);
     if ($summary->error) {
         $this->httpError(400, 'Bad Request', 'Bad URL 1');
         return null;
     } elseif (!isloggedin() && ANNOTATION_REQUIRE_USER) {
         $this->httpError(403, 'Forbidden', 'Anonymous listing not allowed');
         return null;
     } else {
         $querysql = $summary->sql('section_type, section_name, quote_title, start_block, start_line, start_word, start_char, end_block, end_line, end_word, end_char');
         $annotation_set = get_records_sql($querysql);
         $annotations = array();
         if ($annotation_set) {
             $i = 0;
             foreach ($annotation_set as $r) {
                 $annotations[$i++] = annotation_globals::record_to_annotation($r);
             }
         }
         $format = $this->getQueryParam('format', 'atom');
         $logurl = 'annotate.php?format=' . $format . ($user ? '&user=' . $user->id : '') . '&url=' . $url;
         add_to_log($summary->handler->courseid, 'annotation', 'list', $logurl);
         return $annotations;
     }
 }