PHP db_ilike函数代码示例

    /**
     * Populates this object with the user record identified by a mobile 'token'
     *
     * @throws AuthUnknownUserException If the user cannot be found.
     */
    public function find_by_mobileuploadtoken($token, $username)
    {
        if (!is_string($token)) {
            throw new InvalidArgumentException('Input parameters must be strings to create a User object from token');
        }
        $sql = 'SELECT
                        u.*,
                        ' . db_format_tsfield('u.expiry', 'expiry') . ',
                        ' . db_format_tsfield('u.lastlogin', 'lastlogin') . ',
                        ' . db_format_tsfield('u.lastlastlogin', 'lastlastlogin') . ',
                        ' . db_format_tsfield('u.lastaccess', 'lastaccess') . ',
                        ' . db_format_tsfield('u.suspendedctime', 'suspendedctime') . ',
                        ' . db_format_tsfield('u.ctime', 'ctime') . '
                FROM
                    {usr} u
                    LEFT JOIN {usr_account_preference} p ON u.id = p.usr
                            WHERE p.field=\'mobileuploadtoken\'
                              AND p.value ' . db_ilike() . ' \'%|\' || ? || \'|%\' AND u.username = ?
		';
        $user = get_record_sql($sql, array($token, $username));
        if (false == $user) {
            throw new AuthUnknownUserException("User with mobile upload token \"{$token}\" is not known");
        }
        $this->populate($user);
        $this->accountprefs = load_account_preferences($user->id);
        return $this;
    }

        }
    }
    // END OVERWRITE 2
    if (isset($options[$user->authinstance])) {
        $elements['authinstance'] = array('type' => 'select', 'title' => get_string('authenticatedby', 'admin'), 'description' => get_string('authenticatedbydescription', 'admin'), 'options' => $options, 'defaultvalue' => $user->authinstance, 'help' => true);
        $un = get_field('auth_remote_user', 'remoteusername', 'authinstance', $user->authinstance, 'localusr', $user->id);
        $elements['remoteusername'] = array('type' => 'text', 'title' => get_string('remoteusername', 'admin'), 'description' => get_string('remoteusernamedescription1', 'admin', hsc(get_config('sitename'))), 'help' => true);
        if ($un) {
            $elements['remoteusername']['defaultvalue'] = $un;
        }
    }
    $remoteusernames = json_encode(get_records_menu('auth_remote_user', 'localusr', $id));
    $js = "<script type='text/javascript'>\n          var externalauths = ['" . implode("','", $externalauthjs) . "'];\n          var remoteusernames = " . $remoteusernames . ";\n          jQuery(document).ready(function() {\n          // set up initial display\n          var authinstanceid = jQuery('#edituser_site_authinstance :selected').val();\n          is_external(authinstanceid);\n\n          // update display as auth method dropdown changes\n          jQuery('#edituser_site_authinstance').change(function() {\n              authinstanceid = jQuery('#edituser_site_authinstance :selected').val();\n              is_external(authinstanceid);\n          });\n\n          function is_external(id) {\n              if (jQuery.inArray(authinstanceid,externalauths) != -1) {\n                  // is external option so show external auth field and help text rows\n                  jQuery('#edituser_site_remoteusername_container').css('display','table-row');\n                  jQuery('#edituser_site_remoteusername_container').next('tr').css('display','table-row');\n                  if (remoteusernames[id]) {\n                      // if value exists in auth_remote_user display it\n                      jQuery('#edituser_site_remoteusername').val(remoteusernames[id]);\n}\n                  else {\n                      jQuery('#edituser_site_remoteusername').val('');\n                  }\n              }\n              else {\n                  // is internal option so hide external auth field and help text rows\n                  jQuery('#edituser_site_remoteusername_container').css('display','none');\n                  jQuery('#edituser_site_remoteusername_container').next('tr').css('display','none');\n              }\n          }\n      });\n      </script>";
    $elements['externalauthjs'] = array('type' => 'html', 'value' => $js);
}
$tags = get_column_sql('SELECT tag FROM {usr_tag} WHERE usr = ? AND NOT tag ' . db_ilike() . " 'lastinstitution:%'", array($user->id));
$elements['tags'] = array('defaultvalue' => $tags, 'type' => 'tags', 'title' => get_string('tags'), 'description' => get_string('tagsdesc'), 'help' => true);
$elements['submit'] = array('type' => 'submit', 'value' => get_string('savechanges', 'admin'));
$siteform = pieform(array('name' => 'edituser_site', 'renderer' => 'table', 'plugintype' => 'core', 'pluginname' => 'admin', 'elements' => $elements));
function edituser_site_validate(Pieform $form, $values)
{
    global $USER, $SESSION;
    if (!($user = get_record('usr', 'id', $values['id']))) {
        return false;
    }
    if ($USER->get('admin') || get_config_plugin('artefact', 'file', 'institutionaloverride')) {
        $maxquotaenabled = get_config_plugin('artefact', 'file', 'maxquotaenabled');
        $maxquota = get_config_plugin('artefact', 'file', 'maxquota');
        if ($maxquotaenabled && $values['quota'] > $maxquota) {
            $form->set_error('quota', get_string('maxquotaexceededform', 'artefact.file', display_size($maxquota)));
            $SESSION->add_error_msg(get_string('maxquotaexceeded', 'artefact.file', display_size($maxquota)));

//.........这里部分代码省略.........
    }
    if ($oldversion < 2012070300) {
        $table = new XMLDBTable('group');
        $field = new XMLDBField('groupparticipationreports');
        $field->setAttributes(XMLDB_TYPE_INTEGER, 1, null, XMLDB_NOTNULL, null, null, null, 0);
        add_field($table, $field);
    }
    if ($oldversion < 2012080200) {
        $sql = "\n            FROM {usr} u JOIN {auth_instance} ai ON (u.authinstance = ai.id)\n            WHERE u.deleted = 0 AND ai.authname = 'internal' AND u.password != '*' AND u.salt != '*'\n            AND u.password NOT LIKE '\$%'";
        $pwcount = count_records_sql("SELECT COUNT(*) " . $sql);
        $sql = "\n            SELECT u.id, u.password, u.salt" . $sql . " AND u.id > ?\n            ORDER BY u.id";
        $done = 0;
        $lastid = 0;
        $limit = 2000;
        while ($users = get_records_sql_array($sql, array($lastid), 0, $limit)) {
            foreach ($users as $user) {
                // Wrap the old hashed password inside a SHA512 hash ($6$ is the identifier for SHA512)
                $user->password = crypt($user->password, '$6$' . substr(md5(get_config('passwordsaltmain') . $user->salt), 0, 16));
                // Drop the salt from the password as it may contain secrets that are not stored in the db
                // for example, the passwordsaltmain value
                $user->password = substr($user->password, 0, 3) . substr($user->password, 3 + 16);
                set_field('usr', 'password', $user->password, 'id', $user->id);
                remove_user_sessions($user->id);
                $lastid = $user->id;
            }
            $done += count($users);
            log_debug("Upgrading stored passwords: {$done}/{$pwcount}");
            set_time_limit(30);
        }
    }
    if ($oldversion < 2012080300) {
        // For multi-tokens we need '|' aka pipe characters either side of their old single token
        execute_sql('UPDATE {usr_account_preference} SET value = \'|\' || value || \'|\'
                            WHERE field=\'mobileuploadtoken\' AND NOT value ' . db_ilike() . '\'|%|\'');
    }
    if ($oldversion < 2012080600) {
        // Every minute, poll an imap mailbox to see if there are new mail bounces
        $cron = new StdClass();
        $cron->callfunction = 'check_imap_for_bounces';
        $cron->minute = '*';
        $cron->hour = '*';
        $cron->day = '*';
        $cron->month = '*';
        $cron->dayofweek = '*';
        insert_record('cron', $cron);
    }
    if ($oldversion < 2012080601) {
        $table = new XMLDBTable('group');
        $field = new XMLDBField('editwindowstart');
        $field->setAttributes(XMLDB_TYPE_DATETIME);
        add_field($table, $field);
        $field = new XMLDBField('editwindowend');
        $field->setAttributes(XMLDB_TYPE_DATETIME);
        add_field($table, $field);
    }
    if ($oldversion < 2013011700) {
        set_config('defaultregistrationexpirylifetime', 1209600);
    }
    if ($oldversion < 2013012100) {
        $event = (object) array('name' => 'loginas');
        ensure_record_exists('event_type', $event, $event);
    }
    if ($oldversion < 2013012101) {
        $table = new XMLDBTable('event_log');
        $table->addFieldInfo('usr', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL);
        $table->addFieldInfo('realusr', XMLDB_TYPE_INTEGER, 10, null, XMLDB_NOTNULL);

 /**
  * Search view owners.
  */
 public static function search_view_owners($query = null, $template = null, $limit = null, $offset = 0)
 {
     if ($template) {
         $tsql = ' AND v.template = 1';
     } else {
         if ($template === false) {
             $tsql = ' AND v.template = 0';
         } else {
             $tsql = '';
         }
     }
     if ($query) {
         $ph = array($query);
         $qsql = ' WHERE display ' . db_ilike() . " '%' || ? || '%' ";
     } else {
         $ph = array();
         $qsql = '';
     }
     if (is_mysql()) {
         $uid = 'u.id';
         $gid = 'g.id';
     } else {
         $uid = 'CAST (u.id AS TEXT)';
         $gid = 'CAST (g.id AS TEXT)';
     }
     $sql = "\n                SELECT\n                    'user' AS ownertype,\n                    CASE WHEN u.preferredname IS NULL OR u.preferredname = '' THEN u.firstname || ' ' || u.lastname\n                    ELSE u.preferredname END AS display,\n                    {$uid}, COUNT(v.id)\n                FROM {usr} u INNER JOIN {view} v ON (v.owner = u.id AND v.type = 'portfolio')\n                WHERE u.deleted = 0 {$tsql}\n                GROUP BY ownertype, display, u.id\n            UNION\n                SELECT 'group' AS ownertype, g.name AS display, {$gid}, COUNT(v.id)\n                FROM {group} g INNER JOIN {view} v ON (g.id = v.group)\n                WHERE g.deleted = 0 {$tsql}\n                GROUP BY ownertype, display, g.id\n            UNION\n                SELECT 'institution' AS ownertype, i.displayname AS display, i.name AS id, COUNT(v.id)\n                FROM {institution} i INNER JOIN {view} v ON (i.name = v.institution)\n                WHERE TRUE {$tsql}\n                GROUP BY ownertype, display, i.name ORDER BY display";
     $count = count_records_sql("SELECT COUNT(*) FROM ({$sql}) q {$qsql}", $ph);
     $data = get_records_sql_array("SELECT * FROM ({$sql}) q {$qsql} ORDER BY ownertype != 'institution', id != 'mahara', ownertype", $ph, $offset, $limit);
     foreach ($data as &$r) {
         if ($r->ownertype == 'institution' && $r->id == 'mahara') {
             $r->display = get_config('sitename');
         }
     }
     return array('data' => array_values($data), 'count' => $count, 'limit' => $limit, 'offset' => $offset);
 }

/**
 * Return a username which isn't taken and which is similar to a desired username
 * 
 * @param string $desired
 */
function get_new_username($desired)
{
    $maxlen = 30;
    $desired = strtolower(substr($desired, 0, $maxlen));
    $taken = get_column_sql('
        SELECT username FROM {usr}
        WHERE username ' . db_ilike() . " ?", array(substr($desired, 0, $maxlen - 6) . '%'));
    if (!$taken) {
        return $desired;
    }
    $taken = array_flip($taken);
    $i = '';
    $newname = substr($desired, 0, $maxlen - 1) . $i;
    while (isset($taken[$newname])) {
        $i++;
        $newname = substr($desired, 0, $maxlen - strlen($i)) . $i;
    }
    return $newname;
}

/**
 * Return a username which isn't taken and which is similar to a desired username
 *
 * @param string $desired
 * @param string $maxlen    Maximum length of desired username
 */
function get_new_username($desired, $maxlen = 30)
{
    if (function_exists('mb_strtolower')) {
        $desired = mb_strtolower(mb_substr($desired, 0, $maxlen, 'UTF-8'), 'UTF-8');
    } else {
        $desired = strtolower(substr($desired, 0, $maxlen));
    }
    if (function_exists('mb_substr')) {
        $taken = get_column_sql('
            SELECT LOWER(username) FROM {usr}
            WHERE username ' . db_ilike() . " ?", array(mb_substr($desired, 0, $maxlen - 6, 'UTF-8') . '%'));
    } else {
        $taken = get_column_sql('
            SELECT LOWER(username) FROM {usr}
            WHERE username ' . db_ilike() . " ?", array(substr($desired, 0, $maxlen - 6) . '%'));
    }
    if (!$taken) {
        return $desired;
    }
    $taken = array_flip($taken);
    $i = '';
    if (function_exists('mb_substr')) {
        $newname = mb_substr($desired, 0, $maxlen - 1, 'UTF-8') . $i;
    } else {
        $newname = substr($desired, 0, $maxlen - 1) . $i;
    }
    while (isset($taken[$newname])) {
        $i++;
        if (function_exists('mb_substr')) {
            $newname = mb_substr($desired, 0, $maxlen - strlen($i), 'UTF-8') . $i;
        } else {
            $newname = substr($desired, 0, $maxlen - strlen($i)) . $i;
        }
    }
    return $newname;
}

function xmldb_artefact_file_upgrade($oldversion = 0)
{
    $status = true;
    if ($oldversion < 2009033000) {
        if (!get_record('artefact_config', 'plugin', 'file', 'field', 'uploadagreement')) {
            insert_record('artefact_config', (object) array('plugin' => 'file', 'field' => 'uploadagreement', 'value' => 1));
            insert_record('artefact_config', (object) array('plugin' => 'file', 'field' => 'usecustomagreement', 'value' => 1));
        }
    }
    if ($oldversion < 2009091700) {
        execute_sql("DELETE FROM {artefact_file_files} WHERE artefact IN (SELECT id FROM {artefact} WHERE artefacttype = 'folder')");
    }
    if ($oldversion < 2009091701) {
        $table = new XMLDBTable('artefact_file_files');
        $key = new XMLDBKey('artefactpk');
        $key->setAttributes(XMLDB_KEY_PRIMARY, array('artefact'));
        add_key($table, $key);
        $table = new XMLDBTable('artefact_file_image');
        $key = new XMLDBKey('artefactpk');
        $key->setAttributes(XMLDB_KEY_PRIMARY, array('artefact'));
        add_key($table, $key);
    }
    if ($oldversion < 2009092300) {
        insert_record('artefact_installed_type', (object) array('plugin' => 'file', 'name' => 'archive'));
        // update old files
        if (function_exists('zip_open')) {
            $files = get_records_select_array('artefact_file_files', "filetype IN ('application/zip', 'application/x-zip')");
            if ($files) {
                $checked = array();
                foreach ($files as $file) {
                    $path = get_config('dataroot') . 'artefact/file/originals/' . $file->fileid % 256 . '/' . $file->fileid;
                    $zip = zip_open($path);
                    if (is_resource($zip)) {
                        $checked[] = $file->artefact;
                        zip_close($zip);
                    }
                }
                if (!empty($checked)) {
                    set_field_select('artefact', 'artefacttype', 'archive', "artefacttype = 'file' AND id IN (" . join(',', $checked) . ')', array());
                }
            }
        }
    }
    if ($oldversion < 2010012702) {
        if ($records = get_records_sql_array("SELECT * FROM {artefact_file_files} WHERE filetype='application/octet-stream'", array())) {
            require_once 'file.php';
            foreach ($records as &$r) {
                $path = get_config('dataroot') . 'artefact/file/originals/' . $r->fileid % 256 . '/' . $r->fileid;
                set_field('artefact_file_files', 'filetype', file_mime_type($path), 'fileid', $r->fileid, 'artefact', $r->artefact);
            }
        }
    }
    if ($oldversion < 2011052500) {
        // Set default quota to 50MB
        set_config_plugin('artefact', 'file', 'defaultgroupquota', 52428800);
    }
    if ($oldversion < 2011070700) {
        // Create an images folder for everyone with a profile icon
        $imagesdir = get_string('imagesdir', 'artefact.file');
        $imagesdirdesc = get_string('imagesdirdesc', 'artefact.file');
        execute_sql("\n            INSERT INTO {artefact} (artefacttype, container, owner, ctime, mtime, atime, title, description, author)\n            SELECT 'folder', 1, owner, current_timestamp, current_timestamp, current_timestamp, ?, ?, owner\n            FROM {artefact} WHERE owner IS NOT NULL AND artefacttype = 'profileicon'\n            GROUP BY owner", array($imagesdir, $imagesdirdesc));
        // Put profileicons into the images folder and update the description
        $profileicondesc = get_string('uploadedprofileicon', 'artefact.file');
        if (is_postgres()) {
            execute_sql("\n                UPDATE {artefact}\n                SET parent = f.folderid, description = ?\n                FROM (\n                    SELECT owner, MAX(id) AS folderid\n                    FROM {artefact}\n                    WHERE artefacttype = 'folder' AND title = ? AND description = ?\n                    GROUP BY owner\n                ) f\n                WHERE artefacttype = 'profileicon' AND {artefact}.owner = f.owner", array($profileicondesc, $imagesdir, $imagesdirdesc));
        } else {
            execute_sql("\n                UPDATE {artefact}, (\n                    SELECT owner, MAX(id) AS folderid\n                    FROM {artefact}\n                    WHERE artefacttype = 'folder' AND title = ? AND description = ?\n                    GROUP BY owner\n                ) f\n                SET parent = f.folderid, description = ?\n                WHERE artefacttype = 'profileicon' AND {artefact}.owner = f.owner", array($imagesdir, $imagesdirdesc, $profileicondesc));
        }
    }
    if ($oldversion < 2011082200) {
        // video file type
        if (!get_record('artefact_installed_type', 'plugin', 'file', 'name', 'video')) {
            insert_record('artefact_installed_type', (object) array('plugin' => 'file', 'name' => 'video'));
        }
        // update existing records
        $videotypes = get_records_sql_array('
            SELECT DISTINCT description
            FROM {artefact_file_mime_types}
            WHERE mimetype ' . db_ilike() . ' \'%video%\'', array());
        if ($videotypes) {
            $mimetypes = array();
            foreach ($videotypes as $type) {
                $mimetypes[] = $type->description;
            }
            $files = get_records_sql_array('
                SELECT *
                FROM {artefact_file_files}
                WHERE filetype IN (
                    SELECT mimetype
                    FROM {artefact_file_mime_types}
                    WHERE description IN (' . join(',', array_map('db_quote', array_values($mimetypes))) . ')
                )', array());
            if ($files) {
                $checked = array();
                foreach ($files as $file) {
                    $checked[] = $file->artefact;
                }
                if (!empty($checked)) {
                    set_field_select('artefact', 'artefacttype', 'video', "artefacttype = 'file' AND id IN (" . join(',', $checked) . ')', array());
                }
//.........这里部分代码省略.........

 /**
  * Returns the list of institutions, implements institution searching
  *
  * @param array   Limit the output to only institutions in this array (used for institution admins).
  * @param bool    Whether default institution should be listed in results.
  * @param string  Searching query string.
  * @param int     Limit of results (used for pagination).
  * @param int     Offset of results (used for pagination).
  * @param int     Returns the total number of results.
  * @return array  A data structure containing results looking like ...
  *   $institutions = array(
  *                       name => array(
  *                           displayname     => string
  *                           maxuseraccounts => integer
  *                           members         => integer
  *                           staff           => integer
  *                           admins          => integer
  *                           name            => string
  *                       ),
  *                       name => array(...),
  *                   );
  */
 public static function count_members($filter, $showdefault, $query = '', $limit = null, $offset = null, &$count = null)
 {
     if ($filter) {
         $where = '
         AND ii.name IN (' . join(',', array_map('db_quote', $filter)) . ')';
     } else {
         $where = '';
     }
     $querydata = split(' ', preg_replace('/\\s\\s+/', ' ', strtolower(trim($query))));
     $namesql = '(
             ii.name ' . db_ilike() . ' \'%\' || ? || \'%\'
         )
         OR (
             ii.displayname ' . db_ilike() . ' \'%\' || ? || \'%\'
         )';
     $namesql = join(' OR ', array_fill(0, count($querydata), $namesql));
     $queryvalues = array();
     foreach ($querydata as $w) {
         $queryvalues = array_pad($queryvalues, count($queryvalues) + 2, $w);
     }
     $count = count_records_sql('SELECT COUNT(ii.name)
         FROM {institution} ii
         WHERE' . $namesql, $queryvalues);
     $institutions = get_records_sql_assoc('
         SELECT
             ii.name,
             ii.displayname,
             ii.maxuseraccounts,
             ii.suspended,
             COALESCE(a.members, 0) AS members,
             COALESCE(a.staff, 0) AS staff,
             COALESCE(a.admins, 0) AS admins
         FROM
             {institution} ii
             LEFT JOIN
                 (SELECT
                     i.name, i.displayname, i.maxuseraccounts,
                     COUNT(ui.usr) AS members, SUM(ui.staff) AS staff, SUM(ui.admin) AS admins
                 FROM
                     {institution} i
                     LEFT OUTER JOIN {usr_institution} ui ON (ui.institution = i.name)
                     LEFT OUTER JOIN {usr} u ON (u.id = ui.usr)
                 WHERE
                     (u.deleted = 0 OR u.id IS NULL)
                 GROUP BY
                     i.name, i.displayname, i.maxuseraccounts
                 ) a ON (a.name = ii.name)
                 WHERE (' . $namesql . ')' . $where . '
                 ORDER BY
                     ii.name = \'mahara\', ii.displayname', $queryvalues, $offset, $limit);
     if ($showdefault && $institutions && array_key_exists('mahara', $institutions)) {
         $defaultinstmembers = count_records_sql('
             SELECT COUNT(u.id) FROM {usr} u LEFT OUTER JOIN {usr_institution} i ON u.id = i.usr
             WHERE u.deleted = 0 AND i.usr IS NULL AND u.id != 0
         ');
         $institutions['mahara']->members = $defaultinstmembers;
         $institutions['mahara']->staff = '';
         $institutions['mahara']->admins = '';
     }
     return $institutions;
 }

/**
 * Get raw results for webservices log search
 *
 * @param object $search - see build_webservice_log_search_results() for
 *                         list of variables
 */
function get_log_search_results($search)
{
    $sort = 'TRUE';
    if (preg_match('/^[a-zA-Z_0-9"]+$/', $search->sortby)) {
        $sort = $search->sortby;
        if (strtoupper($search->sortdir) != 'DESC') {
            $sort .= ' ASC';
        } else {
            $sort .= ' DESC';
        }
    }
    $where = '';
    $ilike = db_ilike();
    $wheres = array();
    $params = array();
    if ($search->protocol != 'all') {
        $wheres[] = ' el.protocol = ? ';
        $params[] = $search->protocol;
    }
    if ($search->authtype != 'all') {
        $wheres[] = ' el.auth = ? ';
        $params[] = $search->authtype;
    }
    if ($search->institution != 'all') {
        $wheres[] = ' el.institution = ? ';
        $params[] = $search->institution;
    }
    if ($search->onlyerrors == 1) {
        $wheres[] = ' TRIM(el.info) > \' \' ';
    }
    if ($search->userquery) {
        $userwheres = array();
        $terms = split_query_string(strtolower(trim($search->userquery)));
        foreach ($terms as $term) {
            foreach (array('u.username', 'u.firstname', 'u.lastname') as $tests) {
                $userwheres[] = ' ' . $tests . ' ' . $ilike . ' \'%' . addslashes($term) . '%\'';
            }
        }
        if (!empty($userwheres)) {
            $wheres[] = ' ( ' . implode(' OR ', $userwheres) . ' ) ';
        }
    }
    if ($search->functionquery) {
        $functionwheres = array();
        $terms = split_query_string(strtolower(trim($search->functionquery)));
        foreach ($terms as $term) {
            $functionwheres[] = ' el.functionname ' . $ilike . ' \'%' . addslashes($term) . '%\'';
        }
        if (!empty($functionwheres)) {
            $wheres[] = ' ( ' . implode(' OR ', $functionwheres) . ' ) ';
        }
    }
    if (empty($wheres)) {
        $wheres[] = ' TRUE ';
    }
    $where = ' WHERE ' . implode(' AND ', $wheres);
    $count = count_records_sql('
            SELECT  COUNT(*)
            FROM {external_services_logs} el
            JOIN {usr} u
                ON el.userid = u.id
            ' . $where, $params);
    $data = get_records_sql_array('
            SELECT  u.username,
                    u.firstname,
                    u.lastname,
                    u.email,
                    el.*
            FROM {external_services_logs} el
            JOIN {usr} u
                ON el.userid = u.id
            ' . $where . ' ORDER BY ' . $search->sortby, $params, $search->offset);
    $results = array('count' => $count, 'limit' => $search->limit, 'offset' => $search->offset, 'data' => array());
    if (!empty($data)) {
        foreach ($data as $row) {
            $row->timelogged = format_date($row->timelogged, 'strftimedatetime');
            $row->institution = institution_display_name($row->institution);
            $results['data'][] = (array) $row;
        }
    }
    return $results;
}

 /**
  * Helper function for search_parse_query();
  */
 private static function _search_parse_query(&$word, &$scores, $not = FALSE)
 {
     $count = 0;
     // Determine the scorewords of this word/phrase
     if (!$not) {
         $split = explode(' ', $word);
         foreach ($split as $s) {
             $num = is_numeric($s);
             if ($num || strlen($s) >= 3) {
                 $s = $num ? (int) ltrim($s, '-0') : $s;
                 if (!isset($scores[$s])) {
                     $scores[$s] = $s;
                     $count++;
                 }
             }
         }
     }
     // Return matching snippet and number of added words
     return array("a.title " . ($not ? 'NOT ' : '') . db_ilike() . " '%' || ? || '%'" . ($not ? ' AND ' : ' OR ') . 'a.description ' . ($not ? 'NOT ' : '') . db_ilike() . " '%' || ? || '%'", $count);
 }

 /**
  * Implement group searching with SQL
  *
  * @param string  The query string
  * @param integer How many results to return
  * @param integer What result to start at (0 == first result)
  * @param string  Which groups to search (all, member, notmember)
  * @return array  A data structure containing results looking like ...
  *         $results = array(
  *               count   => integer, // total number of results
  *               limit   => integer, // how many results are returned
  *               offset  => integer, // starting from which result
  *               data    => array(   // the result records
  *                   array(
  *                       id            => integer,
  *                       name          => string,
  *                       description   => string,
  *                       jointype      => string,
  *                       owner         => string,
  *                       ctime         => string,
  *                       mtime         => string,
  *                   ),
  *                   array(
  *                       id            => integer,
  *                       name          => string,
  *                       description   => string,
  *                       jointype      => string,
  *                       owner         => string,
  *                       ctime         => string,
  *                       mtime         => string,
  *                   ),
  *                   array(...),
  *               ),
  *           );
  */
 public static function search_group($query_string, $limit, $offset = 0, $type = 'member', $category = '')
 {
     global $USER;
     $data = array();
     $sql = "\n            FROM\n                {group}\n            WHERE (\n                name " . db_ilike() . " '%' || ? || '%'\n                OR description " . db_ilike() . " '%' || ? || '%'\n            ) AND deleted = 0 ";
     $values = array($query_string, $query_string);
     if (!($grouproles = join(',', array_keys($USER->get('grouproles'))))) {
         $grouproles = '-1';
     }
     if ($type == 'member') {
         $sql .= 'AND id IN (' . $grouproles . ')';
     } else {
         if ($type == 'notmember') {
             $sql .= 'AND id NOT IN (' . $grouproles . ')';
         }
     }
     if (!empty($category)) {
         if ($category == -1) {
             //find unassigned groups
             $sql .= " AND category IS NULL";
         } else {
             $sql .= ' AND category = ?';
             $values[] = $category;
         }
     }
     $count = get_field_sql('SELECT COUNT(*) ' . $sql, $values);
     if ($count > 0) {
         $sql = 'SELECT id, name, description, grouptype, jointype, public, ctime, mtime, category ' . $sql . ' ORDER BY name';
         $data = get_records_sql_array($sql, $values, $offset, $limit);
     }
     return array('count' => $count, 'limit' => $limit, 'offset' => $offset, 'data' => $data);
 }

 public static function institutional_admin_search_user($query, $institution, $limit)
 {
     $sql = '
         FROM {usr} u ';
     $where = '
         WHERE u.id <> 0 AND u.deleted = 0 ';
     $values = array();
     if (!empty($query)) {
         $query = preg_replace('/\\s\\s+/', ' ', $query);
         $words = explode(' ', $query);
         foreach ($words as &$word) {
             $values[] = $word;
             $values[] = $word;
             $word = 'u.firstname ' . db_ilike() . ' \'%\' || ? || \'%\' OR u.lastname ' . db_ilike() . ' \'%\' || ? || \'%\'';
         }
         $where .= '
             AND (' . join(' OR ', $words) . ') ';
     }
     $studentid = '';
     if (!is_null($institution->member)) {
         $sql .= '
             LEFT OUTER JOIN {usr_institution} member ON (member.usr = u.id
                 AND member.institution = ' . db_quote($institution->name) . ')';
         $where .= '
             AND ' . ($institution->member ? ' NOT ' : '') . ' member.usr IS NULL';
         $studentid = ', member.studentid';
     }
     if (!is_null($institution->requested) || !is_null($institution->invitedby)) {
         $sql .= '
             LEFT OUTER JOIN {usr_institution_request} req ON (req.usr = u.id
                 AND req.institution = ' . db_quote($institution->name) . ')';
         if (!is_null($institution->requested)) {
             if ($institution->requested == 1) {
                 $where .= ' AND req.confirmedusr = 1';
             } else {
                 $where .= ' AND (req.confirmedusr = 0 OR req.confirmedusr IS NULL)';
             }
             $studentid = ', req.studentid';
         }
         if (!is_null($institution->invitedby)) {
             if ($institution->requested == 1) {
                 $where .= ' AND req.confirmedinstitution = 1';
             } else {
                 $where .= ' AND (req.confirmedinstitution = 0 OR req.confirmedinstitution IS NULL)';
             }
         }
     }
     $count = get_field_sql('SELECT COUNT(*) ' . $sql . $where, $values);
     if ($count > 0) {
         $data = get_records_sql_array('
             SELECT 
                 u.id, u.firstname, u.lastname, u.username, u.preferredname, 
                 u.admin, u.staff' . $studentid . $sql . $where . '
             ORDER BY u.firstname ASC', $values, 0, $limit);
         foreach ($data as &$item) {
             $item = (array) $item;
         }
     } else {
         $data = false;
     }
     return array('count' => $count, 'limit' => $limit, 'offset' => 0, 'data' => $data);
 }