示例#1
0
function copy_resource($from,$resource_type=-1)
	{
	# Create a new resource, copying all data from the resource with reference $from.
	# Note this copies only the data and not any attached file. It's very unlikely the
	# same file would be in the system twice, however users may want to clone an existing resource
	# to avoid reentering data if the resource is very similar.
	# If $resource_type if specified then the resource type for the new resource will be set to $resource_type
	# rather than simply copied from the $from resource.
	
	# Check that the resource exists
	if (sql_value("select count(*) value from resource where ref='$from'",0)==0) {return false;}
	
	# copy joined fields to the resource column
	$joins=get_resource_table_joins();
	$joins_sql="";
	foreach ($joins as $join){
		$joins_sql.=",field$join ";
	}
	
	$add="";

	# Work out the archive status
	$archive=sql_value("select archive value from resource where ref='$from'",0);
	if (!checkperm("e" . $archive))
		{
		# Find the right permission mode to use
		for ($n=-2;$n<3;$n++)
			{
			if (checkperm("e" . $n)) {$archive=$n;break;}
			}
		}

	# First copy the resources row
	sql_query("insert into resource($add resource_type,creation_date,rating,archive,access,created_by $joins_sql) select $add" . (($resource_type==-1)?"resource_type":("'" . $resource_type . "'")) . ",now(),rating,'" . $archive . "',access,created_by $joins_sql from resource where ref='$from';");
	$to=sql_insert_id();
	
	# Copying a resource of the 'pending review' state? Notify, if configured.
	$archive=sql_value("select archive value from resource where ref='$from'",0);
	if ($archive==-1)
		{
		notify_user_contributed_submitted(array($to));
		}
	
	# Set that this resource was created by this user. 
	# This needs to be done if either:
	# 1) The user does not have direct 'resource create' permissions and is therefore contributing using My Contributions directly into the active state
	# 2) The user is contributiting via My Contributions to the standard User Contributed pre-active states.
	global $userref;
	global $always_record_resource_creator;
	if ((!checkperm("c")) || $archive<0 || (isset($always_record_resource_creator) && $always_record_resource_creator))
		{
		# Update the user record
		sql_query("update resource set created_by='$userref' where ref='$to'");

		# Also add the user's username and full name to the keywords index so the resource is searchable using this name.
		global $username,$userfullname;
		add_keyword_mappings($to,$username . " " . $userfullname,-1);
		}
	
	# Now copy all data
	sql_query("insert into resource_data(resource,resource_type_field,value) select '$to',rd.resource_type_field,rd.value from resource_data rd join resource r on rd.resource=r.ref join resource_type_field rtf on rd.resource_type_field=rtf.ref and (rtf.resource_type=r.resource_type or rtf.resource_type=999 or rtf.resource_type=0) where rd.resource='$from'");
	
	# Copy relationships
	sql_query("insert into resource_related(resource,related) select '$to',related from resource_related where resource='$from'");

	# Copy access
	sql_query("insert into resource_custom_access(resource,usergroup,access) select '$to',usergroup,access from resource_custom_access where resource='$from'");

	# Set any resource defaults
	set_resource_defaults($to);

	# Reindex the resource so the resource_keyword entries are created
	reindex_resource($to);
	
	# Log this			
	daily_stat("Create resource",$to);
	resource_log($to,'c',0);

	hook("afternewresource", "", array($to));
	
	return $to;
	}
function copy_resource($from, $resource_type = -1)
{
    # Create a new resource, copying all data from the resource with reference $from.
    # Note this copies only the data and not any attached file. It's very unlikely the
    # same file would be in the system twice, however users may want to clone an existing resource
    # to avoid reentering data if the resource is very similar.
    # If $resource_type if specified then the resource type for the new resource will be set to $resource_type
    # rather than simply copied from the $from resource.
    # Check that the resource exists
    if (sql_value("select count(*) value from resource where ref='{$from}'", 0) == 0) {
        return false;
    }
    # copy joined fields to the resource column
    $joins = get_resource_table_joins();
    // Filter the joined columns so we only have the ones relevant to this resource type
    $query = sprintf('
			    SELECT rtf.ref AS value
			      FROM resource_type_field AS rtf
			INNER JOIN resource AS r ON (rtf.resource_type != r.resource_type AND rtf.resource_type != 0)
			     WHERE r.ref = "%s";
		', $from);
    $irrelevant_rtype_fields = sql_array($query);
    $irrelevant_rtype_fields = array_values(array_intersect($joins, $irrelevant_rtype_fields));
    $filtered_joins = array_values(array_diff($joins, $irrelevant_rtype_fields));
    $joins_sql = "";
    foreach ($filtered_joins as $join) {
        $joins_sql .= ",field{$join} ";
    }
    $add = "";
    # Determine if the user has access to the template archive status
    $archive = sql_value("select archive value from resource where ref='{$from}'", 0);
    if (!checkperm("e" . $archive)) {
        # Find the right permission mode to use
        for ($n = -2; $n < 3; $n++) {
            if (checkperm("e" . $n)) {
                $archive = $n;
                break;
            }
        }
    }
    # First copy the resources row
    sql_query("insert into resource({$add} resource_type,creation_date,rating,archive,access,created_by {$joins_sql}) select {$add}" . ($resource_type == -1 ? "resource_type" : "'" . $resource_type . "'") . ",now(),rating,'" . $archive . "',access,created_by {$joins_sql} from resource where ref='{$from}';");
    $to = sql_insert_id();
    # Set that this resource was created by this user.
    # This needs to be done if either:
    # 1) The user does not have direct 'resource create' permissions and is therefore contributing using My Contributions directly into the active state
    # 2) The user is contributiting via My Contributions to the standard User Contributed pre-active states.
    global $userref;
    global $always_record_resource_creator;
    if (!checkperm("c") || $archive < 0 || isset($always_record_resource_creator) && $always_record_resource_creator) {
        # Update the user record
        sql_query("update resource set created_by='{$userref}' where ref='{$to}'");
        # Also add the user's username and full name to the keywords index so the resource is searchable using this name.
        global $username, $userfullname;
        add_keyword_mappings($to, $username . " " . $userfullname, -1);
    }
    # Now copy all data
    sql_query("insert into resource_data(resource,resource_type_field,value) select '{$to}',rd.resource_type_field,rd.value from resource_data rd join resource r on rd.resource=r.ref join resource_type_field rtf on rd.resource_type_field=rtf.ref and (rtf.resource_type=r.resource_type or rtf.resource_type=999 or rtf.resource_type=0) where rd.resource='{$from}'");
    # Copy relationships
    sql_query("insert into resource_related(resource,related) select '{$to}',related from resource_related where resource='{$from}'");
    # Copy access
    sql_query("insert into resource_custom_access(resource,usergroup,access) select '{$to}',usergroup,access from resource_custom_access where resource='{$from}'");
    # Set any resource defaults
    set_resource_defaults($to);
    # Autocomplete any blank fields.
    autocomplete_blank_fields($to);
    # Reindex the resource so the resource_keyword entries are created
    reindex_resource($to);
    # Copying a resource of the 'pending review' state? Notify, if configured.
    global $send_collection_to_admin;
    if ($archive == -1 && !$send_collection_to_admin) {
        notify_user_contributed_submitted(array($to));
    }
    # Log this
    daily_stat("Create resource", $to);
    resource_log($to, 'c', 0);
    hook("afternewresource", "", array($to));
    return $to;
}
示例#3
0
function CheckDBStruct($path)
{
    # Check the database structure against the text files stored in $path.
    # Add tables / columns / data / indices as necessary.
    global $mysql_db, $resource_field_column_limit;
    # Check for path
    $path = dirname(__FILE__) . "/../" . $path;
    # Make sure this works when called from non-root files..
    if (!file_exists($path)) {
        return false;
    }
    # Tables first.
    # Load existing tables list
    $ts = sql_query("show tables", false, -1, false);
    $tables = array();
    for ($n = 0; $n < count($ts); $n++) {
        $tables[] = $ts[$n]["Tables_in_" . $mysql_db];
    }
    $dh = opendir($path);
    while (($file = readdir($dh)) !== false) {
        if (substr($file, 0, 6) == "table_") {
            $table = str_replace(".txt", "", substr($file, 6));
            # Check table exists
            if (!in_array($table, $tables)) {
                # Create Table
                $sql = "";
                $f = fopen($path . "/" . $file, "r");
                $hasPrimaryKey = false;
                $pk_sql = "PRIMARY KEY (";
                while (($col = fgetcsv($f, 5000)) !== false) {
                    if ($sql .= "") {
                        $sql .= ", ";
                    }
                    $sql .= $col[0] . " " . str_replace("§", ",", $col[1]);
                    if ($col[4] != "") {
                        $sql .= " default " . $col[4];
                    }
                    if ($col[3] == "PRI") {
                        if ($hasPrimaryKey) {
                            $pk_sql .= ",";
                        }
                        $pk_sql .= $col[0];
                        $hasPrimaryKey = true;
                    }
                    if ($col[5] == "auto_increment") {
                        $sql .= " auto_increment ";
                    }
                }
                $pk_sql .= ")";
                if ($hasPrimaryKey) {
                    $sql .= "," . $pk_sql;
                }
                debug($sql);
                sql_query("create table {$table} ({$sql})", false, -1, false);
                # Add initial data
                $data = str_replace("table_", "data_", $file);
                if (file_exists($path . "/" . $data)) {
                    $f = fopen($path . "/" . $data, "r");
                    while (($row = fgetcsv($f, 5000)) !== false) {
                        # Escape values
                        for ($n = 0; $n < count($row); $n++) {
                            $row[$n] = escape_check($row[$n]);
                            $row[$n] = "'" . $row[$n] . "'";
                            if ($row[$n] == "''") {
                                $row[$n] = "null";
                            }
                        }
                        sql_query("insert into {$table} values (" . join(",", $row) . ")", false, -1, false);
                    }
                }
            } else {
                # Table already exists, so check all columns exist
                # Load existing table definition
                $existing = sql_query("describe {$table}", false, -1, false);
                ##########
                # Copy needed resource_data into resource for search displays
                if ($table == "resource") {
                    $joins = get_resource_table_joins();
                    for ($m = 0; $m < count($joins); $m++) {
                        # Look for this column in the existing columns.
                        $found = false;
                        for ($n = 0; $n < count($existing); $n++) {
                            if ("field" . $joins[$m] == $existing[$n]["Field"]) {
                                $found = true;
                            }
                        }
                        if (!$found) {
                            # Add this column.
                            $sql = "alter table {$table} add column ";
                            $sql .= "field" . $joins[$m] . " VARCHAR(" . $resource_field_column_limit . ")";
                            sql_query($sql, false, -1, false);
                            $values = sql_query("select resource,value from resource_data where resource_type_field={$joins[$m]}", false, -1, false);
                            for ($x = 0; $x < count($values); $x++) {
                                $value = $values[$x]['value'];
                                $resource = $values[$x]['resource'];
                                sql_query("update resource set field{$joins[$m]}='" . escape_check($value) . "' where ref={$resource}", false, -1, false);
                            }
                        }
                    }
                }
                ##########
                ##########
                ## RS-specific mod:
                # add theme columns to collection table as needed.
                global $theme_category_levels;
                if ($table == "collection") {
                    for ($m = 1; $m <= $theme_category_levels; $m++) {
                        if ($m == 1) {
                            $themeindex = "";
                        } else {
                            $themeindex = $m;
                        }
                        # Look for this column in the existing columns.
                        $found = false;
                        for ($n = 0; $n < count($existing); $n++) {
                            if ("theme" . $themeindex == $existing[$n]["Field"]) {
                                $found = true;
                            }
                        }
                        if (!$found) {
                            # Add this column.
                            $sql = "alter table {$table} add column ";
                            $sql .= "theme" . $themeindex . " VARCHAR(100)";
                            sql_query($sql, false, -1, false);
                        }
                    }
                }
                ##########
                if (file_exists($path . "/" . $file)) {
                    $f = fopen($path . "/" . $file, "r");
                    while (($col = fgetcsv($f, 5000)) !== false) {
                        if (count($col) > 1) {
                            # Look for this column in the existing columns.
                            $found = false;
                            for ($n = 0; $n < count($existing); $n++) {
                                if ($existing[$n]["Field"] == $col[0]) {
                                    $found = true;
                                }
                            }
                            if (!$found) {
                                # Add this column.
                                $sql = "alter table {$table} add column ";
                                $sql .= $col[0] . " " . str_replace("§", ",", $col[1]);
                                # Allow commas to be entered using '§', necessary for a type such as decimal(2,10)
                                if ($col[4] != "") {
                                    $sql .= " default " . $col[4];
                                }
                                if ($col[3] == "PRI") {
                                    $sql .= " primary key";
                                }
                                if ($col[5] == "auto_increment") {
                                    $sql .= " auto_increment ";
                                }
                                sql_query($sql, false, -1, false);
                            }
                        }
                    }
                }
            }
            # Check all indices exist
            # Load existing indexes
            $existing = sql_query("show index from {$table}", false, -1, false);
            $file = str_replace("table_", "index_", $file);
            if (file_exists($path . "/" . $file)) {
                $done = array();
                # List of indices already processed.
                $f = fopen($path . "/" . $file, "r");
                while (($col = fgetcsv($f, 5000)) !== false) {
                    # Look for this index in the existing indices.
                    $found = false;
                    for ($n = 0; $n < count($existing); $n++) {
                        if ($existing[$n]["Key_name"] == $col[2]) {
                            $found = true;
                        }
                    }
                    if (!$found && !in_array($col[2], $done)) {
                        # Add this index.
                        # Fetch list of columns for this index
                        $cols = array();
                        $f2 = fopen($path . "/" . $file, "r");
                        while (($col2 = fgetcsv($f2, 5000)) !== false) {
                            if ($col2[2] == $col[2]) {
                                $cols[] = $col2[4];
                            }
                        }
                        $sql = "create index " . $col[2] . " on {$table} (" . join(",", $cols) . ")";
                        sql_query($sql, false, -1, false);
                        $done[] = $col[2];
                    }
                }
            }
        }
    }
}
<?php

# Quick script to fix database entries that need commas at the beginning (dropdown fields edited using collection edit before r1940).
# When some values have commas and others don't, sorting doesn't work correctly!!!
include "../../include/db.php";
include "../../include/general.php";
include "../../include/authenticate.php";
if (!checkperm("a")) {
    exit("Permission denied");
}
include "../../include/resource_functions.php";
$resource_type_fields = sql_query("select ref from resource_type_field where type=3");
$joins = get_resource_table_joins();
for ($n = 0; $n < count($resource_type_fields); $n++) {
    $values = sql_query("select resource,resource_type_field,value from resource_data where resource_type_field=" . $resource_type_fields[$n]['ref']);
    for ($x = 0; $x < count($values); $x++) {
        if (substr($values[$x]['value'], 0, 1) != ',') {
            echo "updating resource_data- ref: " . $values[$x]['resource'] . ", field: " . $values[$x]['resource_type_field'] . ", value: ," . $values[$x]['value'] . "<br>";
            sql_query("update resource_data set value='," . escape_check($values[$x]['value']) . "' where resource='" . $values[$x]['resource'] . "' and resource_type_field='" . $values[$x]['resource_type_field'] . "'");
            if (in_array($values[$x]['resource_type_field'], $joins)) {
                echo "updating resource- ref: " . $values[$x]['resource'] . ", field" . $values[$x]['resource_type_field'] . ", value: ," . $values[$x]['value'] . "<br>";
                #echo("update resource set field".$values[$x]['resource_type_field']."=',".mysql_escape_string($values[$x]['value'])."' where ref='".$values[$x]['resource']."'");
                sql_query("update resource set field" . $values[$x]['resource_type_field'] . "='," . escape_check($values[$x]['value']) . "' where ref='" . $values[$x]['resource'] . "'");
            }
        }
    }
}
 function do_search($search, $restypes = "", $order_by = "relevance", $archive = 0, $fetchrows = -1, $sort = "desc", $access_override = false, $starsearch = 0, $ignore_filters = false, $return_disk_usage = false)
 {
     debug("search={$search} restypes={$restypes} archive={$archive}");
     # globals needed for hooks
     global $sql, $order, $select, $sql_join, $sql_filter, $orig_order, $checkbox_and, $collections_omit_archived, $search_sql_double_pass_mode;
     # Takes a search string $search, as provided by the user, and returns a results set
     # of matching resources.
     # If there are no matches, instead returns an array of suggested searches.
     # $restypes is optionally used to specify which resource types to search.
     # $access_override is used by smart collections, so that all all applicable resources can be judged regardless of the final access-based results
     # resolve $order_by to something meaningful in sql
     $orig_order = $order_by;
     global $date_field;
     $order = array("relevance" => "score {$sort}, user_rating {$sort}, hit_count {$sort}, field{$date_field} {$sort},r.ref {$sort}", "popularity" => "user_rating {$sort},hit_count {$sort},field{$date_field} {$sort},r.ref {$sort}", "rating" => "r.rating {$sort}, user_rating {$sort}, score {$sort},r.ref {$sort}", "date" => "field{$date_field} {$sort},r.ref {$sort}", "colour" => "has_image {$sort},image_blue {$sort},image_green {$sort},image_red {$sort},field{$date_field} {$sort},r.ref {$sort}", "country" => "country {$sort},r.ref {$sort}", "title" => "title {$sort},r.ref {$sort}", "file_path" => "file_path {$sort},r.ref {$sort}", "resourceid" => "r.ref {$sort}", "resourcetype" => "resource_type {$sort},r.ref {$sort}", "titleandcountry" => "title {$sort},country {$sort}", "random" => "RAND()");
     if (!in_array($order_by, $order) && substr($order_by, 0, 5) == "field") {
         $order[$order_by] = "{$order_by} {$sort}";
     }
     hook("modifyorderarray");
     # Recognise a quoted search, which is a search for an exact string
     $quoted_string = false;
     if (substr($search, 0, 1) == "\"" && substr($search, -1, 1) == "\"") {
         $quoted_string = true;
         $search = substr($search, 1, -1);
     }
     $order_by = $order[$order_by];
     $keywords = split_keywords($search);
     $search = trim($search);
     # -- Build up filter SQL that will be used for all queries
     $sql_filter = "";
     # append resource type filtering
     if ($restypes != "") {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $restypes_x = explode(",", $restypes);
         $sql_filter .= "resource_type in ('" . join("','", $restypes_x) . "')";
     }
     if ($starsearch != "" && $starsearch != 0) {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= "user_rating >= '{$starsearch}'";
     }
     # If returning disk used by the resources in the search results ($return_disk_usage=true) then wrap the returned SQL in an outer query that sums disk usage.
     $sql_prefix = "";
     $sql_suffix = "";
     if ($return_disk_usage) {
         $sql_prefix = "select sum(disk_usage) total_disk_usage,count(*) total_resources from (";
         $sql_suffix = ") resourcelist";
     }
     # append resource type restrictions based on 'T' permission
     # look for all 'T' permissions and append to the SQL filter.
     global $userpermissions;
     $rtfilter = array();
     for ($n = 0; $n < count($userpermissions); $n++) {
         if (substr($userpermissions[$n], 0, 1) == "T") {
             $rt = substr($userpermissions[$n], 1);
             if (is_numeric($rt) && !$access_override) {
                 $rtfilter[] = $rt;
             }
         }
     }
     if (count($rtfilter) > 0) {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= "resource_type not in (" . join(",", $rtfilter) . ")";
     }
     # append "use" access rights, do not show restricted resources unless admin
     if (!checkperm("v") && !$access_override) {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= "r.access<>'2'";
     }
     # append archive searching (don't do this for collections or !listall, archived resources can still appear in these searches)
     if (substr($search, 0, 8) != "!listall" && substr($search, 0, 11) != "!collection" || $collections_omit_archived && !checkperm("e2")) {
         global $pending_review_visible_to_all;
         if ($archive == 0 && $pending_review_visible_to_all) {
             # If resources pending review are visible to all, when listing only active resources include
             # pending review (-1) resources too.
             if ($sql_filter != "") {
                 $sql_filter .= " and ";
             }
             $sql_filter .= "(archive='0' or archive=-1)";
         } else {
             # Append normal filtering.
             if ($sql_filter != "") {
                 $sql_filter .= " and ";
             }
             $sql_filter .= "archive='{$archive}'";
         }
     }
     # append ref filter - never return the batch upload template (negative refs)
     if ($sql_filter != "") {
         $sql_filter .= " and ";
     }
     $sql_filter .= "r.ref>0";
     # ------ Advanced 'custom' permissions, need to join to access table.
     $sql_join = "";
     global $k;
     if (!checkperm("v") && !$access_override) {
         global $usergroup;
         global $userref;
         # one extra join (rca2) is required for user specific permissions (enabling more intelligent watermarks in search view)
         # the original join is used to gather group access into the search query as well.
         $sql_join = " left outer join resource_custom_access rca2 on r.ref=rca2.resource and rca2.user='******'  and (rca2.user_expires is null or rca2.user_expires>now()) and rca2.access<>2  ";
         $sql_join .= " left outer join resource_custom_access rca on r.ref=rca.resource and rca.usergroup='{$usergroup}' and rca.access<>2 ";
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         # If rca.resource is null, then no matching custom access record was found
         # If r.access is also 3 (custom) then the user is not allowed access to this resource.
         # Note that it's normal for null to be returned if this is a resource with non custom permissions (r.access<>3).
         $sql_filter .= " not(rca.resource is null and r.access=3)";
     }
     # Join thumbs_display_fields to resource table
     $select = "r.ref, r.resource_type, r.has_image, r.is_transcoding, r.hit_count, r.creation_date, r.rating, r.user_rating, r.user_rating_count, r.user_rating_total, r.file_extension, r.preview_extension, r.image_red, r.image_green, r.image_blue, r.thumb_width, r.thumb_height, r.archive, r.access, r.colour_key, r.created_by, r.file_modified, r.file_checksum, r.request_count, r.new_hit_count, r.expiry_notification_sent, r.preview_tweaks, r.file_path ";
     $modified_select = hook("modifyselect");
     if ($modified_select) {
         $select .= $modified_select;
     }
     $modified_select2 = hook("modifyselect2");
     if ($modified_select2) {
         $select .= $modified_select2;
     }
     # Return disk usage for each resource if returning sum of disk usage.
     if ($return_disk_usage) {
         $select .= ",r.disk_usage";
     }
     # select group and user access rights if available, otherwise select null values so columns can still be used regardless
     # this makes group and user specific access available in the basic search query, which can then be passed through access functions
     # in order to eliminate many single queries.
     if (!checkperm("v") && !$access_override) {
         $select .= ",rca.access group_access,rca2.access user_access ";
     } else {
         $select .= ",null group_access, null user_access ";
     }
     # add 'joins' to select (adding them
     $joins = get_resource_table_joins();
     foreach ($joins as $datajoin) {
         $select .= ",r.field" . $datajoin . " ";
     }
     # Prepare SQL to add join table for all provided keywods
     $suggested = $keywords;
     # a suggested search
     $fullmatch = true;
     $c = 0;
     $t = "";
     $t2 = "";
     $score = "";
     $keysearch = true;
     # Do not process if a numeric search is provided (resource ID)
     global $config_search_for_number, $category_tree_search_use_and;
     if ($config_search_for_number && is_numeric($search)) {
         $keysearch = false;
     }
     if ($keysearch) {
         for ($n = 0; $n < count($keywords); $n++) {
             $keyword = $keywords[$n];
             if (substr($keyword, 0, 1) != "!") {
                 global $date_field;
                 $field = 0;
                 #echo "<li>$keyword<br/>";
                 if (strpos($keyword, ":") !== false && !$ignore_filters) {
                     $kw = explode(":", $keyword, 2);
                     if ($kw[0] == "day") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '____-__-" . $kw[1] . "%' ";
                     } elseif ($kw[0] == "month") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '____-" . $kw[1] . "%' ";
                     } elseif ($kw[0] == "year") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '" . $kw[1] . "%' ";
                     } else {
                         $ckeywords = explode(";", $kw[1]);
                         # Fetch field info
                         $fieldinfo = sql_query("select ref,type from resource_type_field where name='" . escape_check($kw[0]) . "'", 0);
                         if (count($fieldinfo) == 0) {
                             debug("Field short name not found.");
                             return false;
                         } else {
                             $fieldinfo = $fieldinfo[0];
                         }
                         # Special handling for dates
                         if ($fieldinfo["type"] == 4 || $fieldinfo["type"] == 6) {
                             $ckeywords = array(str_replace(" ", "-", $kw[1]));
                         }
                         $field = $fieldinfo["ref"];
                         #special SQL generation for category trees to use AND instead of OR
                         if ($fieldinfo["type"] == 7 && $category_tree_search_use_and || $fieldinfo["type"] == 2 && $checkbox_and) {
                             for ($m = 0; $m < count($ckeywords); $m++) {
                                 $keyref = resolve_keyword($ckeywords[$m]);
                                 if (!($keyref === false)) {
                                     $c++;
                                     # Add related keywords
                                     $related = get_related_keywords($keyref);
                                     $relatedsql = "";
                                     for ($r = 0; $r < count($related); $r++) {
                                         $relatedsql .= " or k" . $c . ".keyword='" . $related[$r] . "'";
                                     }
                                     # Form join
                                     //$sql_join.=" join (SELECT distinct k".$c.".resource,k".$c.".hit_count from resource_keyword k".$c." where k".$c.".keyword='$keyref' $relatedsql) t".$c." ";
                                     $sql_join .= " join resource_keyword k" . $c . " on k" . $c . ".resource=r.ref and k" . $c . ".resource_type_field='" . $field . "' and (k" . $c . ".keyword='{$keyref}' {$relatedsql})";
                                     if ($score != "") {
                                         $score .= "+";
                                     }
                                     $score .= "k" . $c . ".hit_count";
                                     # Log this
                                     daily_stat("Keyword usage", $keyref);
                                 }
                             }
                         } else {
                             $c++;
                             $sql_join .= " join resource_keyword k" . $c . " on k" . $c . ".resource=r.ref and k" . $c . ".resource_type_field='" . $field . "'";
                             if ($score != "") {
                                 $score .= "+";
                             }
                             $score .= "k" . $c . ".hit_count";
                             # work through all options in an OR approach for multiple selects on the same field
                             # where k.resource=type_field=$field and (k*.keyword=3 or k*.keyword=4) etc
                             $keyjoin = "";
                             for ($m = 0; $m < count($ckeywords); $m++) {
                                 $keyref = resolve_keyword($ckeywords[$m]);
                                 if ($keyref === false) {
                                     $keyref = -1;
                                 }
                                 if ($m != 0) {
                                     $keyjoin .= " OR ";
                                 }
                                 $keyjoin .= "k" . $c . ".keyword='{$keyref}'";
                                 # Also add related.
                                 $related = get_related_keywords($keyref);
                                 for ($o = 0; $o < count($related); $o++) {
                                     $keyjoin .= " OR k" . $c . ".keyword='" . $related[$o] . "'";
                                 }
                                 # Log this
                                 daily_stat("Keyword usage", $keyref);
                             }
                             if ($keyjoin != "") {
                                 $sql_join .= " and (" . $keyjoin . ")";
                             }
                         }
                     }
                 } else {
                     # Normal keyword (not tied to a field) - searches all fields
                     # If ignoring field specifications then remove them.
                     if (strpos($keyword, ":") !== false && $ignore_filters) {
                         $s = explode(":", $keyword);
                         $keyword = $s[1];
                     }
                     # Omit resources containing this keyword?
                     $omit = false;
                     if (substr($keyword, 0, 1) == "-") {
                         $omit = true;
                         $keyword = substr($keyword, 1);
                     }
                     global $noadd, $wildcard_always_applied;
                     if (in_array($keyword, $noadd)) {
                         $skipped_last = true;
                     } else {
                         # Handle wildcards
                         if (strpos($keyword, "*") !== false || $wildcard_always_applied) {
                             if ($wildcard_always_applied && strpos($keyword, "*") === false) {
                                 $keyword .= "*";
                             }
                             # Suffix asterisk if none supplied and using $wildcard_always_applied mode.
                             # Keyword contains a wildcard. Expand.
                             $c++;
                             global $use_temp_tables;
                             if (!$use_temp_tables) {
                                 global $wildcard_expand_limit;
                                 $wildcards = sql_array("select ref value from keyword where keyword like '" . escape_check(str_replace("*", "%", $keyword)) . "' order by hit_count desc limit " . $wildcard_expand_limit);
                                 # Form join
                                 if (!$omit) {
                                     # Include in query
                                     $sql_join .= " join resource_keyword k" . $c . " on k" . $c . ".resource=r.ref and k" . $c . ".keyword in ('" . join("','", $wildcards) . "')";
                                     $sql_exclude_fields = hook("excludefieldsfromkeywordsearch");
                                     if (!empty($sql_exclude_fields)) {
                                         $sql_join .= " and k" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                                     }
                                 } else {
                                     # Exclude matching resources from query (omit feature)
                                     if ($sql_filter != "") {
                                         $sql_filter .= " and ";
                                     }
                                     $sql_filter .= "r.ref not in (select resource from resource_keyword where keyword in ('" . join("','", $wildcards) . "'))";
                                     # Filter out resources that do contain the keyword.
                                 }
                                 #echo $sql_join;
                             } else {
                                 //begin code for temporary table wildcard expansion
                                 // use a global counter to avoide temporary table naming collisions
                                 global $temptable_counter;
                                 if (!isset($temptable_counter)) {
                                     $temptable_counter = 0;
                                 }
                                 $temptable_counter++;
                                 $thetemptable = 'wcql' . $c . '_' . $temptable_counter;
                                 $sql_exclude_fields = hook("excludefieldsfromkeywordsearch");
                                 $temptable_exclude = '';
                                 if (!empty($sql_exclude_fields)) {
                                     $temptable_exclude = "and rk.resource_type_field not in (" . $sql_exclude_fields . ")";
                                 }
                                 sql_query("create temporary table {$thetemptable} (resource bigint unsigned)");
                                 sql_query("insert into {$thetemptable} select distinct r.ref from resource r\n                                                                        left join resource_keyword rk on r.ref = rk.resource {$temptable_exclude}\n                                                                        left join keyword k  on rk.keyword = k.ref\n                                                                        where k.keyword like '" . escape_check(str_replace("*", "%", $keyword)) . "'");
                                 if (!$omit) {
                                     # Include in query
                                     $sql_join .= " join {$thetemptable} on {$thetemptable}.resource = r.ref ";
                                 } else {
                                     # Exclude matching resources from query (omit feature)
                                     if ($sql_filter != "") {
                                         $sql_filter .= " and ";
                                     }
                                     $sql_filter .= "r.ref not in (select resource from {$thetemptable})";
                                     # Filter out resources that do contain the keyword.
                                 }
                             }
                         } else {
                             # Not a wildcard. Normal matching.
                             $keyref = resolve_keyword($keyword);
                             # Resolve keyword. Ignore any wildcards when resolving. We need wildcards to be present later but not here.
                             if ($keyref === false && !$omit) {
                                 $fullmatch = false;
                                 $soundex = resolve_soundex($keyword);
                                 if ($soundex === false) {
                                     # No keyword match, and no keywords sound like this word. Suggest dropping this word.
                                     $suggested[$n] = "";
                                 } else {
                                     # No keyword match, but there's a word that sounds like this word. Suggest this word instead.
                                     $suggested[$n] = "<i>" . $soundex . "</i>";
                                 }
                             } else {
                                 # Key match, add to query.
                                 $c++;
                                 # Add related keywords
                                 $related = get_related_keywords($keyref);
                                 $relatedsql = "";
                                 for ($m = 0; $m < count($related); $m++) {
                                     $relatedsql .= " or k" . $c . ".keyword='" . $related[$m] . "'";
                                 }
                                 # Form join
                                 global $use_temp_tables, $use_temp_tables_for_keyword_joins;
                                 if (substr($search, 0, 8) == "!related") {
                                     $use_temp_tables_for_keyword_joins = false;
                                 }
                                 // temp tables can't be used twice (unions)
                                 $sql_exclude_fields = hook("excludefieldsfromkeywordsearch");
                                 if (!$use_temp_tables_for_keyword_joins || !$use_temp_tables) {
                                     // Not using temporary tables
                                     # Quoted string support
                                     $positionsql = "";
                                     if ($quoted_string) {
                                         if ($c > 1) {
                                             $last_key_offset = 1;
                                             if (isset($skipped_last) && $skipped_last) {
                                                 $last_key_offset = 2;
                                             }
                                             # Support skipped keywords - if the last keyword was skipped (listed in $noadd), increase the allowed position from the previous keyword. Useful for quoted searches that contain $noadd words, e.g. "black and white" where "and" is a skipped keyword.
                                             $positionsql = "and k" . $c . ".position=k" . ($c - 1) . ".position+" . $last_key_offset;
                                         }
                                     }
                                     if (!empty($sql_exclude_fields)) {
                                         $sql_join .= " and k" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                                     }
                                     if (!$omit) {
                                         # Include in query
                                         $sql_join .= " join resource_keyword k" . $c . " on k" . $c . ".resource=r.ref and (k" . $c . ".keyword='{$keyref}' {$relatedsql}) {$positionsql}";
                                         if ($score != "") {
                                             $score .= "+";
                                         }
                                         $score .= "k" . $c . ".hit_count";
                                     } else {
                                         # Exclude matching resources from query (omit feature)
                                         if ($sql_filter != "") {
                                             $sql_filter .= " and ";
                                         }
                                         $sql_filter .= "r.ref not in (select resource from resource_keyword where keyword='{$keyref}')";
                                         # Filter out resources that do contain the keyword.
                                     }
                                 } else {
                                     //use temp tables
                                     if (!isset($temptable_counter)) {
                                         $temptable_counter = 0;
                                     }
                                     $temptable_counter++;
                                     $jtemptable = 'jtt' . $c . '_' . $temptable_counter;
                                     sql_query("drop table IF EXISTS {$jtemptable} ", false);
                                     $exclude_sql = '';
                                     # Quoted string support
                                     $positionsql = "";
                                     if ($quoted_string) {
                                         if ($c > 1) {
                                             $last_key_offset = 1;
                                             if (isset($skipped_last) && $skipped_last) {
                                                 $last_key_offset = 2;
                                             }
                                             # Support skipped keywords - if the last keyword was skipped (listed in $noadd), increase the allowed position from the previous keyword. Useful for quoted searches that contain $noadd words, e.g. "black and white" where "and" is a skipped keyword.
                                             $positionsql = "and {$jtemptable}.position=" . 'jtt' . ($c - 1) . '_' . ($temptable_counter - 1) . ".position+" . $last_key_offset;
                                         }
                                     }
                                     if (!empty($sql_exclude_fields)) {
                                         $exclude_sql = "and k" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                                     }
                                     $test = sql_query("create temporary table {$jtemptable} SELECT distinct k" . $c . ".resource,k" . $c . ".hit_count,k" . $c . ".position from \tresource_keyword k" . $c . " where (k" . $c . ".keyword='{$keyref}' {$relatedsql})  {$exclude_sql}");
                                     if (!$omit) {
                                         # Include in query
                                         $sql_join .= " join {$jtemptable} on {$jtemptable}.resource = r.ref {$positionsql}";
                                         if ($score != "") {
                                             $score .= "+";
                                         }
                                         $score .= $jtemptable . ".hit_count";
                                     } else {
                                         # Exclude matching resources from query (omit feature)
                                         if ($sql_filter != "") {
                                             $sql_filter .= " and ";
                                         }
                                         $sql_filter .= "r.ref not in (select resource from {$jtemptable})";
                                         # Filter out resources that do contain the keyword.
                                     }
                                 }
                                 # Log this
                                 daily_stat("Keyword usage", $keyref);
                             }
                         }
                         $skipped_last = false;
                     }
                 }
             }
         }
     }
     # Could not match on provided keywords? Attempt to return some suggestions.
     if ($fullmatch == false) {
         if ($suggested == $keywords) {
             # Nothing different to suggest.
             debug("No alternative keywords to suggest.");
             return "";
         } else {
             # Suggest alternative spellings/sound-a-likes
             $suggest = "";
             if (strpos($search, ",") === false) {
                 $suggestjoin = " ";
             } else {
                 $suggestjoin = ", ";
             }
             for ($n = 0; $n < count($suggested); $n++) {
                 if ($suggested[$n] != "") {
                     if ($suggest != "") {
                         $suggest .= $suggestjoin;
                     }
                     $suggest .= $suggested[$n];
                 }
             }
             debug("Suggesting {$suggest}");
             return $suggest;
         }
     }
     # Some useful debug.
     #echo("keywordjoin=" . $sql_join);
     #echo("<br>Filter=" . $sql_filter);
     #echo("<br>Search=" . $search);
     hook("additionalsqlfilter");
     # ------ Search filtering: If search_filter is specified on the user group, then we must always apply this filter.
     global $usersearchfilter;
     $sf = explode(";", $usersearchfilter);
     if (strlen($usersearchfilter) > 0) {
         for ($n = 0; $n < count($sf); $n++) {
             $s = explode("=", $sf[$n]);
             if (count($s) != 2) {
                 exit("Search filter is not correctly configured for this user group.");
             }
             # Find field(s) - multiple fields can be returned to support several fields with the same name.
             $f = sql_array("select ref value from resource_type_field where name='" . escape_check($s[0]) . "'");
             if (count($f) == 0) {
                 exit("Field(s) with short name '" . $s[0] . "' not found in user group search filter.");
             }
             # Find keyword(s)
             $ks = explode("|", strtolower(escape_check($s[1])));
             $modifiedsearchfilter = hook("modifysearchfilter");
             if ($modifiedsearchfilter) {
                 $ks = $modifiedsearchfilter;
             }
             $kw = sql_array("select ref value from keyword where keyword in ('" . join("','", $ks) . "')");
             #if (count($k)==0) {exit ("At least one of keyword(s) '" . join("', '",$ks) . "' not found in user group search filter.");}
             $sql_join .= " join resource_keyword filter" . $n . " on r.ref=filter" . $n . ".resource and filter" . $n . ".resource_type_field in ('" . join("','", $f) . "') and filter" . $n . ".keyword in ('" . join("','", $kw) . "') ";
         }
     }
     $userownfilter = hook("userownfilter");
     if ($userownfilter) {
         $sql_join .= $userownfilter;
     }
     # Handle numeric searches when $config_search_for_number=false, i.e. perform a normal search but include matches for resource ID first
     global $config_search_for_number;
     if (!$config_search_for_number && is_numeric($search)) {
         # Always show exact resource matches first.
         $order_by = "(r.ref='" . $search . "') desc," . $order_by;
     }
     # --------------------------------------------------------------------------------
     # Special Searches (start with an exclamation mark)
     # --------------------------------------------------------------------------------
     # Can only search for resources that belong to themes
     if (checkperm("J")) {
         $sql_join .= " join collection_resource jcr on jcr.resource=r.ref join collection jc on jcr.collection=jc.ref and length(jc.theme)>0 ";
     }
     # ------ Special searches ------
     # View Last
     if (substr($search, 0, 5) == "!last") {
         # Replace r2.ref with r.ref for the alternative query used here.
         $order_by = str_replace("r.ref", "r2.ref", $order_by);
         if ($orig_order == "relevance") {
             $order_by = "r2.ref desc";
         }
         # Extract the number of records to produce
         $last = explode(",", $search);
         $last = str_replace("!last", "", $last[0]);
         if (!is_numeric($last)) {
             $last = 1000;
         }
         # 'Last' must be a number. SQL injection filter.
         # Fix the order by for this query (special case due to inner query)
         $order_by = str_replace("r.rating", "rating", $order_by);
         return sql_query($sql_prefix . "select distinct *,r2.hit_count score from (select {$select} from resource r {$sql_join}  where {$sql_filter} order by ref desc limit {$last} ) r2 order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # View Resources With No Downloads
     if (substr($search, 0, 12) == "!nodownloads") {
         if ($orig_order == "relevance") {
             $order_by = "ref desc";
         }
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where {$sql_filter} and ref not in (select distinct object_ref from daily_stat where activity_type='Resource download') order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Duplicate Resources (based on file_checksum)
     if (substr($search, 0, 11) == "!duplicates") {
         // old code disabled due to performance issues
         //return sql_query("select distinct r.hit_count score, $select from resource r $sql_join  where $sql_filter and file_checksum in (select file_checksum from (select file_checksum,count(*) dupecount from resource group by file_checksum) r2 where r2.dupecount>1) order by file_checksum",false,$fetchrows);
         // new code relies on MySQL temporary tables being enabled, as well as checksums
         // if either is not turned on, just give up.
         global $use_temp_tables;
         global $file_checksums;
         if ($use_temp_tables && $file_checksums) {
             global $temptable_counter;
             if (!isset($temptable_counter)) {
                 $temptable_counter = 0;
             }
             $temptable_counter++;
             $thetemptable = 'dupehashx' . '_' . $temptable_counter;
             $dupequery = "select distinct r.hit_count score, {$select} from resource r {$sql_join} join {$thetemptable} on r.file_checksum = {$thetemptable}.hash where {$sql_filter} order by file_checksum";
             sql_query("create temporary table {$thetemptable} (`hash` varchar(255) NOT NULL,`hashcount` int(10) default NULL, KEY `Index 1` (`hash`))", false);
             sql_query("insert into {$thetemptable} select file_checksum, count(file_checksum) from resource where archive = 0 and ref > 0 and file_checksum <> '' and file_checksum is not null group by file_checksum having count(file_checksum) > 1", false);
             $duperesult = sql_query($dupequery, false, $fetchrows);
             return $duperesult;
         } else {
             return false;
         }
     }
     # View Collection
     if (substr($search, 0, 11) == "!collection") {
         if ($orig_order == "relevance") {
             $order_by = "c.sortorder asc,c.date_added desc,r.ref";
         }
         $colcustperm = $sql_join;
         if (getval("k", "") != "") {
             $sql_filter = "ref>0";
         }
         # Special case if a key has been provided.
         # Extract the collection number
         $collection = explode(" ", $search);
         $collection = str_replace("!collection", "", $collection[0]);
         $collection = explode(",", $collection);
         // just get the number
         $collection = $collection[0];
         # smart collections update
         global $allow_smart_collections;
         if ($allow_smart_collections) {
             $smartsearch_ref = sql_value("select savedsearch value from collection where ref={$collection}", "");
             if ($smartsearch_ref != "") {
                 $smartsearch = sql_query("select * from collection_savedsearch where ref={$smartsearch_ref}");
                 if (isset($smartsearch[0]['search'])) {
                     $smartsearch = $smartsearch[0];
                     $results = do_search($smartsearch['search'], $smartsearch['restypes'], "relevance", $smartsearch['archive'], -1, "desc", true, $smartsearch['starsearch']);
                     # results is a list of the current search without any restrictions
                     # we need to compare against the current collection contents to minimize inserts and deletions
                     $current = sql_query("select resource from collection_resource where collection={$collection}");
                     $current_contents = array();
                     $results_contents = array();
                     if (!empty($current)) {
                         foreach ($current as $current_item) {
                             $current_contents[] = $current_item['resource'];
                         }
                     }
                     if (!empty($results) && is_array($results)) {
                         foreach ($results as $results_item) {
                             $results_contents[] = $results_item['ref'];
                         }
                     }
                     for ($n = 0; $n < count($results_contents); $n++) {
                         if (!in_array($results_contents[$n], $current_contents)) {
                             add_resource_to_collection($results_contents[$n], $collection, true);
                         }
                     }
                     for ($n = 0; $n < count($current_contents); $n++) {
                         if (!in_array($current_contents[$n], $results_contents)) {
                             remove_resource_from_collection($current_contents[$n], $collection, true);
                         }
                     }
                 }
             }
         }
         return sql_query($sql_prefix . "select distinct c.date_added,c.comment,c.purchase_size,c.purchase_complete,r.hit_count score,length(c.comment) commentset, {$select} from resource r  join collection_resource c on r.ref=c.resource {$colcustperm}  where c.collection='" . $collection . "' and {$sql_filter} group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # View Related
     if (substr($search, 0, 8) == "!related") {
         # Extract the resource number
         $resource = explode(" ", $search);
         $resource = str_replace("!related", "", $resource[0]);
         $order_by = str_replace("r.", "", $order_by);
         # UNION below doesn't like table aliases in the order by.
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r join resource_related t on (t.related=r.ref and t.resource='" . $resource . "') {$sql_join}  where 1=1 and {$sql_filter} group by r.ref \n\t\tUNION\n\t\tselect distinct r.hit_count score, {$select} from resource r join resource_related t on (t.resource=r.ref and t.related='" . $resource . "') {$sql_join}  where 1=1 and {$sql_filter} group by r.ref \n\t\torder by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Geographic search
     if (substr($search, 0, 4) == "!geo") {
         $geo = explode("t", str_replace(array("m", "p"), array("-", "."), substr($search, 4)));
         # Specially encoded string to avoid keyword splitting
         $bl = explode("b", $geo[0]);
         $tr = explode("b", $geo[1]);
         $sql = "select r.hit_count score, {$select} from resource r {$sql_join} where \n\n\t\t\t\t\tgeo_lat > '" . escape_check($bl[0]) . "'\n              and   geo_lat < '" . escape_check($tr[0]) . "'\t\t\n              and   geo_long > '" . escape_check($bl[1]) . "'\t\t\n              and   geo_long < '" . escape_check($tr[1]) . "'\t\t\n                          \n\t\t and {$sql_filter} group by r.ref order by {$order_by}";
         return sql_query($sql_prefix . $sql . $sql_suffix, false, $fetchrows);
     }
     # Colour search
     if (substr($search, 0, 7) == "!colour") {
         $colour = explode(" ", $search);
         $colour = str_replace("!colour", "", $colour[0]);
         $sql = "select r.hit_count score, {$select} from resource r {$sql_join}\n\t\t\t\twhere \n\t\t\t\t\tcolour_key like '" . escape_check($colour) . "%'\n              \tor  colour_key like '_" . escape_check($colour) . "%'\n                          \n\t\t and {$sql_filter} group by r.ref order by {$order_by}";
         return sql_query($sql_prefix . $sql . $sql_suffix, false, $fetchrows);
     }
     # Similar to a colour
     if (substr($search, 0, 4) == "!rgb") {
         $rgb = explode(":", $search);
         $rgb = explode(",", $rgb[1]);
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where has_image=1 and {$sql_filter} group by r.ref order by (abs(image_red-" . $rgb[0] . ")+abs(image_green-" . $rgb[1] . ")+abs(image_blue-" . $rgb[2] . ")) asc limit 500" . $sql_suffix, false, $fetchrows);
     }
     # Similar to a colour by key
     if (substr($search, 0, 10) == "!colourkey") {
         # Extract the colour key
         $colourkey = explode(" ", $search);
         $colourkey = str_replace("!colourkey", "", $colourkey[0]);
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where has_image=1 and left(colour_key,4)='" . $colourkey . "' and {$sql_filter} group by r.ref" . $sql_suffix, false, $fetchrows);
     }
     global $config_search_for_number;
     if ($config_search_for_number && is_numeric($search) || substr($search, 0, 9) == "!resource") {
         $theref = escape_check($search);
         $theref = preg_replace("/[^0-9]/", "", $theref);
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where r.ref='{$theref}' and {$sql_filter} group by r.ref" . $sql_suffix);
     }
     # Searching for pending archive
     if (substr($search, 0, 15) == "!archivepending") {
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where archive=1 and ref>0 group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     if (substr($search, 0, 12) == "!userpending") {
         if ($orig_order == "rating") {
             $order_by = "request_count desc," . $order_by;
         }
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where archive=-1 and ref>0 group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # View Contributions
     if (substr($search, 0, 14) == "!contributions") {
         global $userref;
         # Extract the user ref
         $cuser = explode(" ", $search);
         $cuser = str_replace("!contributions", "", $cuser[0]);
         if ($userref == $cuser) {
             $sql_filter = "archive='{$archive}'";
             $sql_join = "";
         }
         # Disable permissions when viewing your own contributions - only restriction is the archive status
         $select = str_replace(",rca.access group_access,rca2.access user_access ", ",null group_access, null user_access ", $select);
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where created_by='" . $cuser . "' and r.ref > 0 and {$sql_filter} group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Search for resources with images
     if ($search == "!images") {
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where has_image=1 group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Search for resources not used in Collections
     if (substr($search, 0, 7) == "!unused") {
         return sql_query($sql_prefix . "SELECT distinct {$select} FROM resource r {$sql_join}  where r.ref>0 and r.ref not in (select c.resource from collection_resource c) and {$sql_filter}" . $sql_suffix, false, $fetchrows);
     }
     # Search for a list of resources
     # !listall = archive state is not applied as a filter to the list of resources.
     if (substr($search, 0, 5) == "!list") {
         $resources = explode(" ", $search);
         if (substr($search, 0, 8) == "!listall") {
             $resources = str_replace("!listall", "", $resources[0]);
         } else {
             $resources = str_replace("!list", "", $resources[0]);
         }
         $resources = explode(",", $resources);
         // separate out any additional keywords
         $resources = escape_check($resources[0]);
         if (strlen(trim($resources)) == 0) {
             $resources = "where r.ref IS NULL";
         } else {
             $resources = "where (r.ref='" . str_replace(":", "' OR r.ref='", $resources) . "')";
         }
         return sql_query($sql_prefix . "SELECT distinct r.hit_count score, {$select} FROM resource r {$sql_join} {$resources} and {$sql_filter} order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Within this hook implementation, set the value of the global $sql variable:
     # Since there will only be one special search executed at a time, only one of the
     # hook implementations will set the value.  So, you know that the value set
     # will always be the correct one (unless two plugins use the same !<type> value).
     $sql = "";
     hook("addspecialsearch");
     if ($sql != "") {
         debug("Addspecialsearch hook returned useful results.");
         return sql_query($sql_prefix . $sql . $sql_suffix, false, $fetchrows);
     }
     # -------------------------------------------------------------------------------------
     # Standard Searches
     # -------------------------------------------------------------------------------------
     # We've reached this far without returning.
     # This must be a standard (non-special) search.
     # Construct and perform the standard search query.
     #$sql="";
     if ($sql_filter != "") {
         if ($sql != "") {
             $sql .= " and ";
         }
         $sql .= $sql_filter;
     }
     # Append custom permissions
     $t .= $sql_join;
     if ($score == "") {
         $score = "r.hit_count";
     }
     # In case score hasn't been set (i.e. empty search)
     global $max_results;
     if ($t2 != "" && $sql != "") {
         $sql = " and " . $sql;
     }
     # Compile final SQL
     # Performance enhancement - set return limit to number of rows required
     if ($search_sql_double_pass_mode && $fetchrows != -1) {
         $max_results = $fetchrows;
     }
     $results_sql = $sql_prefix . "select distinct {$score} score, {$select} from resource r" . $t . "  where {$t2} {$sql} group by r.ref order by {$order_by} limit {$max_results}" . $sql_suffix;
     # Debug
     debug("\n" . $results_sql);
     # Execute query
     $result = sql_query($results_sql, false, $fetchrows);
     # Performance improvement - perform a second count-only query and pad the result array as necessary
     if ($search_sql_double_pass_mode && count($result) > 0 && count($result) >= $max_results) {
         $count_sql = "select count(distinct r.ref) value from resource r" . $t . "  where {$t2} {$sql}";
         $count = sql_value($count_sql, 0);
         $result = array_pad($result, $count, 0);
     }
     debug("Search found " . count($result) . " results");
     if (count($result) > 0) {
         return $result;
     }
     # (temp) - no suggestion for field-specific searching for now - TO DO: modify function below to support this
     if (strpos($search, ":") !== false) {
         return "";
     }
     # All keywords resolved OK, but there were no matches
     # Remove keywords, least used first, until we get results.
     $lsql = "";
     $omitmatch = false;
     for ($n = 0; $n < count($keywords); $n++) {
         if (substr($keywords[$n], 0, 1) == "-") {
             $omitmatch = true;
             $omit = $keywords[$n];
         }
         if ($lsql != "") {
             $lsql .= " or ";
         }
         $lsql .= "keyword='" . escape_check($keywords[$n]) . "'";
     }
     if ($omitmatch) {
         return trim_spaces(str_replace(" " . $omit . " ", " ", " " . join(" ", $keywords) . " "));
     }
     if ($lsql != "") {
         $least = sql_value("select keyword value from keyword where {$lsql} order by hit_count asc limit 1", "");
         return trim_spaces(str_replace(" " . $least . " ", " ", " " . join(" ", $keywords) . " "));
     } else {
         return array();
     }
 }
 function upload_file($ref, $no_exif = false, $revert = false, $autorotate = false)
 {
     hook("beforeuploadfile", "", array($ref));
     hook("clearaltfiles", "", array($ref));
     // optional: clear alternative files before uploading new resource
     # revert is mainly for metadata reversion, removing all metadata and simulating a reupload of the file from scratch.
     hook("removeannotations", "", array($ref));
     $exiftool_fullpath = get_utility_path("exiftool");
     # Process file upload for resource $ref
     if ($revert == true) {
         global $filename_field;
         $original_filename = get_data_by_field($ref, $filename_field);
         # Field 8 is used in a special way for staticsync, don't overwrite.
         $test_for_staticsync = get_resource_data($ref);
         if ($test_for_staticsync['file_path'] != "") {
             $staticsync_mod = " and resource_type_field != 8";
         } else {
             $staticsync_mod = "";
         }
         sql_query("delete from resource_data where resource={$ref} {$staticsync_mod}");
         sql_query("delete from resource_keyword where resource={$ref} {$staticsync_mod}");
         #clear 'joined' display fields which are based on metadata that is being deleted in a revert (original filename is reinserted later)
         $display_fields = get_resource_table_joins();
         if ($staticsync_mod != "") {
             $display_fields_new = array();
             for ($n = 0; $n < count($display_fields); $n++) {
                 if ($display_fields[$n] != 8) {
                     $display_fields_new[] = $display_fields[$n];
                 }
             }
             $display_fields = $display_fields_new;
         }
         $clear_fields = "";
         for ($x = 0; $x < count($display_fields); $x++) {
             $clear_fields .= "field" . $display_fields[$x] . "=''";
             if ($x < count($display_fields) - 1) {
                 $clear_fields .= ",";
             }
         }
         sql_query("update resource set " . $clear_fields . " where ref={$ref}");
         #also add the ref back into keywords:
         add_keyword_mappings($ref, $ref, -1);
         $extension = sql_value("select file_extension value from resource where ref={$ref}", "");
         $filename = get_resource_path($ref, true, "", false, $extension);
         $processfile['tmp_name'] = $filename;
     } else {
         # Work out which file has been posted
         if (isset($_FILES['userfile'])) {
             $processfile = $_FILES['userfile'];
         } elseif (isset($_FILES['Filedata'])) {
             $processfile = $_FILES['Filedata'];
         }
         # Java upload (at least) needs this
         # Plupload needs this
         if (isset($_REQUEST['name'])) {
             $filename = $_REQUEST['name'];
         } else {
             $filename = $processfile['name'];
         }
         global $filename_field;
         if ($no_exif && isset($filename_field)) {
             $user_set_filename = get_data_by_field($ref, $filename_field);
             if (trim($user_set_filename) != '') {
                 // Get extension of file just in case the user didn't provide one
                 $path_parts = pathinfo($filename);
                 $original_extension = $path_parts['extension'];
                 $filename = $user_set_filename;
                 // If the user filename doesn't have an extension add the original one
                 $path_parts = pathinfo($filename);
                 if (!isset($path_parts['extension'])) {
                     $filename .= '.' . $original_extension;
                 }
             }
         }
     }
     # Work out extension
     if (!isset($extension)) {
         # first try to get it from the filename
         $extension = explode(".", $filename);
         if (count($extension) > 1) {
             $extension = escape_check(trim(strtolower($extension[count($extension) - 1])));
         } else {
             if ($exiftool_fullpath != false) {
                 $file_type_by_exiftool = run_command($exiftool_fullpath . " -filetype -s -s -s " . escapeshellarg($processfile['tmp_name']));
                 if (strlen($file_type_by_exiftool) > 0) {
                     $extension = str_replace(" ", "_", trim(strtolower($file_type_by_exiftool)));
                     $filename = $filename;
                 } else {
                     return false;
                 }
             } else {
                 return false;
             }
         }
     }
     # Banned extension?
     global $banned_extensions;
     if (in_array($extension, $banned_extensions)) {
         return false;
     }
     $status = "Please provide a file name.";
     $filepath = get_resource_path($ref, true, "", true, $extension);
     if (!$revert) {
         # Remove existing file, if present
         hook("beforeremoveexistingfile", "", array("resourceId" => $ref));
         $old_extension = sql_value("select file_extension value from resource where ref='{$ref}'", "");
         if ($old_extension != "") {
             $old_path = get_resource_path($ref, true, "", true, $old_extension);
             if (file_exists($old_path)) {
                 unlink($old_path);
             }
         }
         // also remove any existing extracted icc profiles
         $icc_path = get_resource_path($ref, true, "", true, $extension . '.icc');
         if (file_exists($icc_path)) {
             unlink($icc_path);
         }
         global $pdf_pages;
         $iccx = 0;
         // if there is a -0.icc page, run through and delete as many as necessary.
         $finished = false;
         $badicc_path = str_replace(".icc", "-{$iccx}.icc", $icc_path);
         while (!$finished) {
             if (file_exists($badicc_path)) {
                 unlink($badicc_path);
                 $iccx++;
                 $badicc_path = str_replace(".icc", "-{$iccx}.icc", $icc_path);
             } else {
                 $finished = true;
             }
         }
         $iccx = 0;
     }
     if (!$revert) {
         if ($filename != "") {
             global $jupload_alternative_upload_location, $plupload_upload_location;
             if (isset($plupload_upload_location)) {
                 # PLUpload - file was sent chunked and reassembled - use the reassembled file location
                 $result = rename($plupload_upload_location, $filepath);
             } elseif (isset($jupload_alternative_upload_location)) {
                 # JUpload - file was sent chunked and reassembled - use the reassembled file location
                 $result = rename($jupload_alternative_upload_location, $filepath);
             } else {
                 # Standard upload.
                 if (!$revert) {
                     $result = move_uploaded_file($processfile['tmp_name'], $filepath);
                 } else {
                     $result = true;
                 }
             }
             if ($result == false) {
                 $status = "File upload error. Please check the size of the file you are trying to upload.";
                 return false;
             } else {
                 global $camera_autorotation;
                 global $ffmpeg_audio_extensions;
                 if ($camera_autorotation) {
                     if ($autorotate && !in_array($extension, $ffmpeg_audio_extensions)) {
                         AutoRotateImage($filepath);
                     }
                 }
                 chmod($filepath, 0777);
                 global $icc_extraction;
                 global $ffmpeg_supported_extensions;
                 if ($icc_extraction && $extension != "pdf" && !in_array($extension, $ffmpeg_supported_extensions)) {
                     extract_icc_profile($ref, $extension);
                 }
                 $status = "Your file has been uploaded.";
             }
         }
     }
     # Store extension in the database and update file modified time.
     if ($revert) {
         $has_image = "";
     } else {
         $has_image = ",has_image=0";
     }
     sql_query("update resource set file_extension='{$extension}',preview_extension='jpg',file_modified=now() {$has_image} where ref='{$ref}'");
     # delete existing resource_dimensions
     sql_query("delete from resource_dimensions where resource='{$ref}'");
     # get file metadata
     if (!$no_exif) {
         extract_exif_comment($ref, $extension);
     } else {
         global $merge_filename_with_title, $lang;
         if ($merge_filename_with_title) {
             $merge_filename_with_title_option = urlencode(getval('merge_filename_with_title_option', ''));
             $merge_filename_with_title_include_extensions = urlencode(getval('merge_filename_with_title_include_extensions', ''));
             $merge_filename_with_title_spacer = urlencode(getval('merge_filename_with_title_spacer', ''));
             $original_filename = '';
             if (isset($_REQUEST['name'])) {
                 $original_filename = $_REQUEST['name'];
             } else {
                 $original_filename = $processfile['name'];
             }
             if ($merge_filename_with_title_include_extensions == 'yes') {
                 $merged_filename = $original_filename;
             } else {
                 $merged_filename = strip_extension($original_filename);
             }
             // Get title field:
             $resource = get_resource_data($ref);
             $read_from = get_exiftool_fields($resource['resource_type']);
             for ($i = 0; $i < count($read_from); $i++) {
                 if ($read_from[$i]['name'] == 'title') {
                     $oldval = get_data_by_field($ref, $read_from[$i]['ref']);
                     if (strpos($oldval, $merged_filename) !== FALSE) {
                         continue;
                     }
                     switch ($merge_filename_with_title_option) {
                         case $lang['merge_filename_title_do_not_use']:
                             // Do nothing since the user doesn't want to use this feature
                             break;
                         case $lang['merge_filename_title_replace']:
                             $newval = $merged_filename;
                             break;
                         case $lang['merge_filename_title_prefix']:
                             $newval = $merged_filename . $merge_filename_with_title_spacer . $oldval;
                             if ($oldval == '') {
                                 $newval = $merged_filename;
                             }
                             break;
                         case $lang['merge_filename_title_suffix']:
                             $newval = $oldval . $merge_filename_with_title_spacer . $merged_filename;
                             if ($oldval == '') {
                                 $newval = $merged_filename;
                             }
                             break;
                         default:
                             // Do nothing
                             break;
                     }
                     update_field($ref, $read_from[$i]['ref'], $newval);
                 }
             }
         }
     }
     # extract text from documents (e.g. PDF, DOC).
     global $extracted_text_field;
     if (isset($extracted_text_field) && !$no_exif) {
         if (isset($unoconv_path) && in_array($extension, $unoconv_extensions)) {
             // omit, since the unoconv process will do it during preview creation below
         } else {
             extract_text($ref, $extension);
         }
     }
     # Store original filename in field, if set
     global $filename_field, $amended_filename;
     if (isset($filename_field)) {
         if (isset($amended_filename)) {
             $filename = $amended_filename;
         }
     }
     if (!$revert) {
         update_field($ref, $filename_field, $filename);
     } else {
         update_field($ref, $filename_field, $original_filename);
     }
     if (!$revert) {
         # Clear any existing FLV file or multi-page previews.
         global $pdf_pages;
         for ($n = 2; $n <= $pdf_pages; $n++) {
             # Remove preview page.
             $path = get_resource_path($ref, true, "scr", false, "jpg", -1, $n, false);
             if (file_exists($path)) {
                 unlink($path);
             }
             # Also try the watermarked version.
             $path = get_resource_path($ref, true, "scr", false, "jpg", -1, $n, true);
             if (file_exists($path)) {
                 unlink($path);
             }
         }
         # Remove any FLV video preview (except if the actual resource is an FLV file).
         global $ffmpeg_preview_extension;
         if ($extension != $ffmpeg_preview_extension) {
             $path = get_resource_path($ref, true, "", false, $ffmpeg_preview_extension);
             if (file_exists($path)) {
                 unlink($path);
             }
         }
         # Remove any FLV preview-only file
         $path = get_resource_path($ref, true, "pre", false, $ffmpeg_preview_extension);
         if (file_exists($path)) {
             unlink($path);
         }
         # Remove any MP3 (except if the actual resource is an MP3 file).
         if ($extension != "mp3") {
             $path = get_resource_path($ref, true, "", false, "mp3");
             if (file_exists($path)) {
                 unlink($path);
             }
         }
         # Create previews
         global $enable_thumbnail_creation_on_upload;
         if ($enable_thumbnail_creation_on_upload) {
             create_previews($ref, false, $extension);
         } else {
             # Offline thumbnail generation is being used. Set 'has_image' to zero so the offline create_previews.php script picks this up.
             sql_query("update resource set has_image=0 where ref='{$ref}'");
         }
     }
     # Update file dimensions
     get_original_imagesize($ref, $filepath, $extension);
     hook("Uploadfilesuccess", "", array("resourceId" => $ref));
     # Update disk usage
     update_disk_usage($ref);
     # Log this activity.
     $log_ref = resource_log($ref, "u", 0);
     hook("upload_image_after_log_write", "", array($ref, $log_ref));
     return $status;
 }
 function do_search($search, $restypes = "", $order_by = "relevance", $archive = 0, $fetchrows = -1, $sort = "desc", $access_override = false, $starsearch = 0, $ignore_filters = false, $return_disk_usage = false, $recent_search_daylimit = "", $go = false, $stats_logging = true)
 {
     debug("search={$search} {$go} {$fetchrows} restypes={$restypes} archive={$archive} daylimit={$recent_search_daylimit}");
     # globals needed for hooks
     global $sql, $order, $select, $sql_join, $sql_filter, $orig_order, $checkbox_and, $collections_omit_archived, $search_sql_double_pass_mode, $usergroup, $search_filter_strict, $default_sort, $search_sql_optimization;
     $alternativeresults = hook("alternativeresults", "", array($go));
     if ($alternativeresults) {
         return $alternativeresults;
     }
     $modifyfetchrows = hook("modifyfetchrows", "", array($fetchrows));
     if ($modifyfetchrows) {
         $fetchrows = $modifyfetchrows;
     }
     # Takes a search string $search, as provided by the user, and returns a results set
     # of matching resources.
     # If there are no matches, instead returns an array of suggested searches.
     # $restypes is optionally used to specify which resource types to search.
     # $access_override is used by smart collections, so that all all applicable resources can be judged regardless of the final access-based results
     # Check valid sort
     if (!in_array(strtolower($sort), array("asc", "desc"))) {
         $sort = "asc";
     }
     # resolve $order_by to something meaningful in sql
     $orig_order = $order_by;
     global $date_field;
     $order = array("relevance" => "score {$sort}, user_rating {$sort}, hit_count {$sort}, field{$date_field} {$sort},r.ref {$sort}", "popularity" => "user_rating {$sort},hit_count {$sort},field{$date_field} {$sort},r.ref {$sort}", "rating" => "r.rating {$sort}, user_rating {$sort}, score {$sort},r.ref {$sort}", "date" => "field{$date_field} {$sort},r.ref {$sort}", "colour" => "has_image {$sort},image_blue {$sort},image_green {$sort},image_red {$sort},field{$date_field} {$sort},r.ref {$sort}", "country" => "country {$sort},r.ref {$sort}", "title" => "title {$sort},r.ref {$sort}", "file_path" => "file_path {$sort},r.ref {$sort}", "resourceid" => "r.ref {$sort}", "resourcetype" => "resource_type {$sort},r.ref {$sort}", "titleandcountry" => "title {$sort},country {$sort}", "random" => "RAND()", "status" => "archive {$sort}");
     if (!in_array($order_by, $order) && substr($order_by, 0, 5) == "field") {
         if (!is_numeric(str_replace("field", "", $order_by))) {
             exit("Order field incorrect.");
         }
         $order[$order_by] = "{$order_by} {$sort}";
     }
     hook("modifyorderarray");
     # Recognise a quoted search, which is a search for an exact string
     global $quoted_string;
     $quoted_string = false;
     if (substr($search, 0, 1) == "\"" && substr($search, -1, 1) == "\"") {
         $quoted_string = true;
         $search = substr($search, 1, -1);
     }
     $order_by = $order[$order_by];
     $keywords = split_keywords($search);
     foreach (get_indexed_resource_type_fields() as $resource_type_field) {
         add_verbatim_keywords($keywords, $search, $resource_type_field);
         // add any regex matched verbatim keywords for those indexed resource type fields
     }
     $search = trim($search);
     # Dedupe keywords (not for quoted strings as the user may be looking for the same word multiple times together in this instance)
     if (!$quoted_string) {
         $keywords = array_values(array_unique($keywords));
     }
     $modified_keywords = hook('dosearchmodifykeywords', '', array($keywords));
     if ($modified_keywords) {
         $keywords = $modified_keywords;
     }
     # -- Build up filter SQL that will be used for all queries
     $sql_filter = "";
     $sql_keyword_union_whichkeys = array();
     $sql_keyword_union = array();
     $sql_keyword_union_aggregation = array();
     $sql_keyword_union_criteria = array();
     $sql_keyword_union_sub_query = array();
     # append resource type filtering
     if ($restypes != "" && substr($restypes, 0, 6) != "Global") {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $restypes_x = explode(",", $restypes);
         $sql_filter .= "resource_type in ('" . join("','", $restypes_x) . "')";
     }
     if ($starsearch != "" && $starsearch != 0 && $starsearch != -1) {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= "user_rating >= '{$starsearch}'";
     }
     if ($starsearch == -1) {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= "user_rating = '-1'";
     }
     if ($recent_search_daylimit != "") {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= "creation_date > (curdate() - interval " . $recent_search_daylimit . " DAY)";
     }
     # The ability to restrict access by the user that created the resource.
     global $resource_created_by_filter;
     if (isset($resource_created_by_filter) && count($resource_created_by_filter) > 0) {
         $created_filter = "";
         foreach ($resource_created_by_filter as $filter_user) {
             if ($filter_user == -1) {
                 global $userref;
                 $filter_user = $userref;
             }
             # '-1' can be used as an alias to the current user. I.e. they can only see their own resources in search results.
             if ($created_filter != "") {
                 $created_filter .= " or ";
             }
             $created_filter .= "created_by = '" . $filter_user . "'";
         }
         if ($created_filter != "") {
             if ($sql_filter != "") {
                 $sql_filter .= " and ";
             }
             $sql_filter .= "(" . $created_filter . ")";
         }
     }
     # Geo zone exclusion
     # A list of upper/lower long/lat bounds, defining areas that will be excluded from geo search results.
     # Areas are defined as southwest lat, southwest long, northeast lat, northeast long
     global $geo_search_restrict;
     if (count($geo_search_restrict) > 0 && substr($search, 0, 4) == "!geo") {
         foreach ($geo_search_restrict as $zone) {
             if ($sql_filter != "") {
                 $sql_filter .= " and ";
             }
             $sql_filter .= "(geo_lat is null or geo_long is null or not(geo_lat >= '" . $zone[0] . "' and geo_lat<= '" . $zone[2] . "'";
             $sql_filter .= "and geo_long >= '" . $zone[1] . "' and geo_long<= '" . $zone[3] . "'))";
         }
     }
     # If returning disk used by the resources in the search results ($return_disk_usage=true) then wrap the returned SQL in an outer query that sums disk usage.
     $sql_prefix = "";
     $sql_suffix = "";
     if ($return_disk_usage) {
         $sql_prefix = "select sum(disk_usage) total_disk_usage,count(*) total_resources from (";
         $sql_suffix = ") resourcelist";
     }
     # append resource type restrictions based on 'T' permission
     # look for all 'T' permissions and append to the SQL filter.
     global $userpermissions;
     $rtfilter = array();
     for ($n = 0; $n < count($userpermissions); $n++) {
         if (substr($userpermissions[$n], 0, 1) == "T") {
             $rt = substr($userpermissions[$n], 1);
             if (is_numeric($rt) && !$access_override) {
                 $rtfilter[] = $rt;
             }
         }
     }
     if (count($rtfilter) > 0) {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= "resource_type not in (" . join(",", $rtfilter) . ")";
     }
     # append "use" access rights, do not show restricted resources unless admin
     if (!checkperm("v") && !$access_override) {
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= "r.access<>'2'";
     }
     # append archive searching (don't do this for collections or !listall, archived resources can still appear in these searches)
     if (!$access_override && (substr($search, 0, 8) != "!listall" && substr($search, 0, 11) != "!collection" || $collections_omit_archived && !checkperm("e2"))) {
         global $pending_review_visible_to_all, $search_all_workflow_states;
         if ($search_all_workflow_states) {
             # Nothing to append, as we're searching all states.
             hook("search_all_workflow_states_filter");
         } elseif ($archive == 0 && $pending_review_visible_to_all) {
             # If resources pending review are visible to all, when listing only active resources include
             # pending review (-1) resources too.
             if ($sql_filter != "") {
                 $sql_filter .= " and ";
             }
             $sql_filter .= "archive in('0','-1')";
         } else {
             # Append normal filtering - extended as advanced search now allows searching by archive state
             if ($sql_filter != "") {
                 $sql_filter .= " and ";
             }
             $sql_filter .= "archive = '{$archive}'";
             global $userref, $pending_submission_searchable_to_all;
             if (!$pending_submission_searchable_to_all && $archive == "-2" && !(checkperm("e-2") && checkperm("t") || checkperm("v"))) {
                 $sql_filter .= " and created_by='" . $userref . "'";
             }
             if (!$pending_review_visible_to_all && $archive == "-1" && !(checkperm("e-1") && checkperm("t") || checkperm("v"))) {
                 $sql_filter .= " and created_by='" . $userref . "'";
             }
         }
     }
     # Add code to filter out resoures in archive states that the user does not have access to due to a 'z' permission
     $filterblockstates = "";
     for ($n = -2; $n <= 3; $n++) {
         if (checkperm("z" . $n) && !$access_override) {
             if ($filterblockstates != "") {
                 $filterblockstates .= "','";
             }
             $filterblockstates .= $n;
         }
     }
     global $additional_archive_states;
     foreach ($additional_archive_states as $additional_archive_state) {
         if (checkperm("z" . $additional_archive_state)) {
             if ($filterblockstates != "") {
                 $filterblockstates .= "','";
             }
             $filterblockstates .= $additional_archive_state;
         }
     }
     if ($filterblockstates != "" && !$access_override) {
         global $uploader_view_override, $userref;
         if ($uploader_view_override) {
             if ($sql_filter != "") {
                 $sql_filter .= " and ";
             }
             $sql_filter .= "(archive not in ('{$filterblockstates}') or created_by='" . $userref . "')";
         } else {
             if ($sql_filter != "") {
                 $sql_filter .= " and ";
             }
             $sql_filter .= "archive not in ('{$filterblockstates}')";
         }
     }
     # append ref filter - never return the batch upload template (negative refs)
     if ($sql_filter != "") {
         $sql_filter .= " and ";
     }
     $sql_filter .= "r.ref>0";
     # ------ Advanced 'custom' permissions, need to join to access table.
     $sql_join = "";
     global $k;
     if (!checkperm("v") && !$access_override) {
         global $usergroup;
         global $userref;
         # one extra join (rca2) is required for user specific permissions (enabling more intelligent watermarks in search view)
         # the original join is used to gather group access into the search query as well.
         $sql_join = " left outer join resource_custom_access rca2 on r.ref=rca2.resource and rca2.user='******'  and (rca2.user_expires is null or rca2.user_expires>now()) and rca2.access<>2  ";
         $sql_join .= " left outer join resource_custom_access rca on r.ref=rca.resource and rca.usergroup='{$usergroup}' and rca.access<>2 ";
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         # If rca.resource is null, then no matching custom access record was found
         # If r.access is also 3 (custom) then the user is not allowed access to this resource.
         # Note that it's normal for null to be returned if this is a resource with non custom permissions (r.access<>3).
         $sql_filter .= " not(rca.resource is null and r.access=3)";
     }
     # Join thumbs_display_fields to resource table
     $select = "r.ref, r.resource_type, r.has_image, r.is_transcoding, r.hit_count, r.creation_date, r.rating, r.user_rating, r.user_rating_count, r.user_rating_total, r.file_extension, r.preview_extension, r.image_red, r.image_green, r.image_blue, r.thumb_width, r.thumb_height, r.archive, r.access, r.colour_key, r.created_by, r.file_modified, r.file_checksum, r.request_count, r.new_hit_count, r.expiry_notification_sent, r.preview_tweaks, r.file_path ";
     $modified_select = hook("modifyselect");
     if ($modified_select) {
         $select .= $modified_select;
     }
     $modified_select2 = hook("modifyselect2");
     if ($modified_select2) {
         $select .= $modified_select2;
     }
     # Return disk usage for each resource if returning sum of disk usage.
     if ($return_disk_usage) {
         $select .= ",r.disk_usage";
     }
     # select group and user access rights if available, otherwise select null values so columns can still be used regardless
     # this makes group and user specific access available in the basic search query, which can then be passed through access functions
     # in order to eliminate many single queries.
     if (!checkperm("v") && !$access_override) {
         $select .= ",rca.access group_access,rca2.access user_access ";
     } else {
         $select .= ",null group_access, null user_access ";
     }
     # add 'joins' to select (adding them
     $joins = get_resource_table_joins();
     foreach ($joins as $datajoin) {
         $select .= ",r.field" . $datajoin . " ";
     }
     # Prepare SQL to add join table for all provided keywods
     $suggested = $keywords;
     # a suggested search
     $fullmatch = true;
     $c = 0;
     $t = "";
     $t2 = "";
     $score = "";
     $skipped_last = false;
     $keysearch = true;
     # Do not process if a numeric search is provided (resource ID)
     global $config_search_for_number, $category_tree_search_use_and;
     if ($config_search_for_number && is_numeric($search)) {
         $keysearch = false;
     }
     # Fetch a list of fields that are not available to the user - these must be omitted from the search.
     $hidden_indexed_fields = get_hidden_indexed_fields();
     # This is a performance enhancement that will discard any keyword matches for fields that are not supposed to be indexed.
     $sql_restrict_by_field_types = "";
     global $search_sql_force_field_index_check;
     if (isset($search_sql_force_field_index_check) && $search_sql_force_field_index_check && $restypes != "") {
         $sql_restrict_by_field_types = sql_value("select group_concat(ref) as value from resource_type_field where keywords_index=1 and resource_type in ({$restypes})", "");
         if ($sql_restrict_by_field_types != "") {
             $sql_restrict_by_field_types = "-1," . $sql_restrict_by_field_types;
             // -1 needed for global search
         }
     }
     if ($keysearch) {
         for ($n = 0; $n < count($keywords); $n++) {
             $keyword = $keywords[$n];
             if (substr($keyword, 0, 1) != "!" || substr($keyword, 0, 6) == "!empty") {
                 global $date_field;
                 $field = 0;
                 #echo "<li>$keyword<br/>";
                 if (strpos($keyword, ":") !== false && !$ignore_filters) {
                     $kw = explode(":", $keyword, 2);
                     global $datefieldinfo_cache;
                     if (isset($datefieldinfo_cache[$kw[0]])) {
                         $datefieldinfo = $datefieldinfo_cache[$kw[0]];
                     } else {
                         $datefieldinfo = sql_query("select ref from resource_type_field where name='" . escape_check($kw[0]) . "' and type IN (4,6,10)", 0);
                         $datefieldinfo_cache[$kw[0]] = $datefieldinfo;
                     }
                     if (count($datefieldinfo) && substr($kw[1], 0, 5) != "range") {
                         $c++;
                         $datefieldinfo = $datefieldinfo[0];
                         $datefield = $datefieldinfo["ref"];
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $val = str_replace("n", "_", $kw[1]);
                         $val = str_replace("|", "-", $val);
                         $sql_filter .= "rd" . $c . ".value like '" . $val . "%' ";
                         $sql_join .= " join resource_data rd" . $c . " on rd" . $c . ".resource=r.ref and rd" . $c . ".resource_type_field='" . $datefield . "'";
                     } elseif ($kw[0] == "day") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '____-__-" . $kw[1] . "%' ";
                     } elseif ($kw[0] == "month") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '____-" . $kw[1] . "%' ";
                     } elseif ($kw[0] == "year") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '" . $kw[1] . "%' ";
                     } elseif ($kw[0] == "startdate") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} >= '" . $kw[1] . "' ";
                     } elseif ($kw[0] == "enddate") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} <= '" . $kw[1] . " 23:59:59' ";
                     } elseif (count($datefieldinfo) && substr($kw[1], 0, 5) == "range") {
                         $c++;
                         $rangefield = $datefieldinfo[0]["ref"];
                         $daterange = false;
                         $rangestring = substr($kw[1], 5);
                         if (strpos($rangestring, "start") !== FALSE) {
                             $rangestart = str_replace(" ", "-", $rangestring);
                             if ($sql_filter != "") {
                                 $sql_filter .= " and ";
                             }
                             $sql_filter .= "rd" . $c . ".value >= '" . substr($rangestart, strpos($rangestart, "start") + 5, 10) . "'";
                         }
                         if (strpos($kw[1], "end") !== FALSE) {
                             $rangeend = str_replace(" ", "-", $rangestring);
                             if ($sql_filter != "") {
                                 $sql_filter .= " and ";
                             }
                             $sql_filter .= "rd" . $c . ".value <= '" . substr($rangeend, strpos($rangeend, "end") + 3, 10) . " 23:59:59'";
                         }
                         $sql_join .= " join resource_data rd" . $c . " on rd" . $c . ".resource=r.ref and rd" . $c . ".resource_type_field='" . $rangefield . "'";
                     } elseif (!hook('customsearchkeywordfilter', null, array($kw))) {
                         $ckeywords = explode(";", $kw[1]);
                         # Fetch field info
                         global $fieldinfo_cache;
                         if (isset($fieldinfo_cache[$kw[0]])) {
                             $fieldinfo = $fieldinfo_cache[$kw[0]];
                         } else {
                             $fieldinfo = sql_query("select ref,type from resource_type_field where name='" . escape_check($kw[0]) . "'", 0);
                             $fieldinfo_cache[$kw[0]] = $fieldinfo;
                         }
                         if (count($fieldinfo) == 0) {
                             debug("Field short name not found.");
                             return false;
                         }
                         # Create an array of matching field IDs.
                         $fields = array();
                         foreach ($fieldinfo as $fi) {
                             if (in_array($fi["ref"], $hidden_indexed_fields)) {
                                 # Attempt to directly search field that the user does not have access to.
                                 return false;
                             }
                             # Add to search array
                             $fields[] = $fi["ref"];
                         }
                         # Special handling for dates
                         if ($fieldinfo[0]["type"] == 4 || $fieldinfo[0]["type"] == 6 || $fieldinfo[0]["type"] == 10) {
                             $ckeywords = array(str_replace(" ", "-", $kw[1]));
                         }
                         #special SQL generation for category trees to use AND instead of OR
                         if ($fieldinfo[0]["type"] == 7 && $category_tree_search_use_and || $fieldinfo[0]["type"] == 2 && $checkbox_and) {
                             for ($m = 0; $m < count($ckeywords); $m++) {
                                 $keyref = resolve_keyword($ckeywords[$m]);
                                 if (!($keyref === false)) {
                                     $c++;
                                     # Add related keywords
                                     $related = get_related_keywords($keyref);
                                     $relatedsql = "";
                                     for ($r = 0; $r < count($related); $r++) {
                                         $relatedsql .= " or k" . $c . ".keyword='" . $related[$r] . "'";
                                     }
                                     # Form join
                                     $sql_join .= " join resource_keyword k" . $c . " on k" . $c . ".resource=r.ref and k" . $c . ".resource_type_field in ('" . join("','", $fields) . "') and (k" . $c . ".keyword='{$keyref}' {$relatedsql})";
                                     if ($score != "") {
                                         $score .= "+";
                                     }
                                     $score .= "k" . $c . ".hit_count";
                                     # Log this
                                     if ($stats_logging) {
                                         daily_stat("Keyword usage", $keyref);
                                     }
                                 }
                             }
                         } else {
                             $c++;
                             # work through all options in an OR approach for multiple selects on the same field
                             $searchkeys = array();
                             for ($m = 0; $m < count($ckeywords); $m++) {
                                 $keyref = resolve_keyword($ckeywords[$m]);
                                 if ($keyref === false) {
                                     $keyref = -1;
                                 }
                                 $searchkeys[] = $keyref;
                                 # Also add related.
                                 $related = get_related_keywords($keyref);
                                 for ($o = 0; $o < count($related); $o++) {
                                     $searchkeys[] = $related[$o];
                                 }
                                 # Log this
                                 if ($stats_logging) {
                                     daily_stat("Keyword usage", $keyref);
                                 }
                             }
                             $union = "select resource,";
                             for ($p = 1; $p <= count($keywords); $p++) {
                                 if ($p == $c) {
                                     $union .= "true";
                                 } else {
                                     $union .= "false";
                                 }
                                 $union .= " as keyword_" . $p . "_found,";
                             }
                             $union .= "hit_count as score from resource_keyword k" . $c . " where (k" . $c . ".keyword='{$keyref}' or k" . $c . ".keyword in ('" . join("','", $searchkeys) . "')) and k" . $c . ".resource_type_field in ('" . join("','", $fields) . "')";
                             if (!empty($sql_exclude_fields)) {
                                 $union .= " and k" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                             }
                             if (count($hidden_indexed_fields) > 0) {
                                 $union .= " and k" . $c . ".resource_type_field not in ('" . join("','", $hidden_indexed_fields) . "')";
                             }
                             $sql_keyword_union_aggregation[] = "bit_or(keyword_" . $c . "_found) as keyword_" . $c . "_found";
                             $sql_keyword_union_criteria[] = "h.keyword_" . $c . "_found";
                             $sql_keyword_union[] = $union;
                         }
                     }
                 } else {
                     # Normal keyword (not tied to a field) - searches all fields that the user has access to
                     # If ignoring field specifications then remove them.
                     if (strpos($keyword, ":") !== false && $ignore_filters) {
                         $s = explode(":", $keyword);
                         $keyword = $s[1];
                     }
                     # Omit resources containing this keyword?
                     $omit = false;
                     if (substr($keyword, 0, 1) == "-") {
                         $omit = true;
                         $keyword = substr($keyword, 1);
                     }
                     # Search for resources with an empty field, ex: !empty18  or  !emptycaption
                     $empty = false;
                     if (substr($keyword, 0, 6) == "!empty") {
                         $nodatafield = str_replace("!empty", "", $keyword);
                         if (!is_numeric($nodatafield)) {
                             $nodatafield = sql_value("select ref value from resource_type_field where name='" . escape_check($nodatafield) . "'", "");
                         }
                         if ($nodatafield == "" || !is_numeric($nodatafield)) {
                             exit('invalid !empty search');
                         }
                         $empty = true;
                     }
                     global $noadd, $wildcard_always_applied;
                     if (in_array($keyword, $noadd)) {
                         $skipped_last = true;
                     } else {
                         # Handle wildcards
                         $wildcards = array();
                         if (strpos($keyword, "*") !== false || $wildcard_always_applied) {
                             if ($wildcard_always_applied && strpos($keyword, "*") === false) {
                                 # Suffix asterisk if none supplied and using $wildcard_always_applied mode.
                                 $keyword = $keyword . "*";
                             }
                             # Keyword contains a wildcard. Expand.
                             global $wildcard_expand_limit;
                             $wildcards = sql_array("select ref value from keyword where keyword like '" . escape_check(str_replace("*", "%", $keyword)) . "' order by hit_count desc limit " . $wildcard_expand_limit);
                         }
                         $keyref = resolve_keyword(str_replace('*', '', $keyword));
                         # Resolve keyword. Ignore any wildcards when resolving. We need wildcards to be present later but not here.
                         if ($keyref === false && !$omit && !$empty && count($wildcards) == 0) {
                             $fullmatch = false;
                             $soundex = resolve_soundex($keyword);
                             if ($soundex === false) {
                                 # No keyword match, and no keywords sound like this word. Suggest dropping this word.
                                 $suggested[$n] = "";
                             } else {
                                 # No keyword match, but there's a word that sounds like this word. Suggest this word instead.
                                 $suggested[$n] = "<i>" . $soundex . "</i>";
                             }
                         } else {
                             if ($keyref === false) {
                                 # make a new keyword
                                 $keyref = resolve_keyword(str_replace('*', '', $keyword), true);
                             }
                             # Key match, add to query.
                             $c++;
                             $relatedsql = "";
                             if (!$quoted_string) {
                                 # Add related keywords
                                 $related = get_related_keywords($keyref);
                                 # Merge wildcard expansion with related keywords
                                 $related = array_merge($related, $wildcards);
                                 if (count($related) > 0) {
                                     $relatedsql = " or k" . $c . ".keyword IN ('" . join("','", $related) . "')";
                                 }
                             }
                             # Form join
                             $sql_exclude_fields = hook("excludefieldsfromkeywordsearch");
                             if (!$omit) {
                                 # Include in query
                                 // --------------------------------------------------------------------------------
                                 // Start of normal union for resource keywords
                                 // --------------------------------------------------------------------------------
                                 // add false for keyword matches other than the current one
                                 $bit_or_condition = "";
                                 for ($p = 1; $p <= count($keywords); $p++) {
                                     if ($p == $c) {
                                         $bit_or_condition .= "true";
                                     } else {
                                         $bit_or_condition .= "false";
                                     }
                                     $bit_or_condition .= " as keyword_" . $p . "_found,";
                                 }
                                 // these restrictions apply to both !empty searches as well as normal keyword searches (i.e. both branches of next if statement)
                                 $union_restriction_clause = "";
                                 if (!empty($sql_exclude_fields)) {
                                     $union_restriction_clause .= " and k" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                                 }
                                 if (count($hidden_indexed_fields) > 0) {
                                     $union_restriction_clause .= " and k" . $c . ".resource_type_field not in ('" . join("','", $hidden_indexed_fields) . "')";
                                 }
                                 if ($empty) {
                                     $rtype = sql_value("select resource_type value from resource_type_field where ref='{$nodatafield}'", 0);
                                     if ($rtype != 0) {
                                         if ($rtype == 999) {
                                             $restypesql = "and (r" . $c . ".archive=1 or r" . $c . ".archive=2) and ";
                                             if ($sql_filter != "") {
                                                 $sql_filter .= " and ";
                                             }
                                             $sql_filter .= str_replace("r" . $c . ".archive='0'", "(r" . $c . ".archive=1 or r" . $c . ".archive=2)", $sql_filter);
                                         } else {
                                             $restypesql = "and r" . $c . ".resource_type ='{$rtype}' ";
                                         }
                                     } else {
                                         $restypesql = "";
                                     }
                                     $union = "select ref as resource, {$bit_or_condition} 1 as score from resource r" . $c . " left outer join resource_data rd" . $c . " on r" . $c . ".ref=rd" . $c . ".resource and rd" . $c . ".resource_type_field='{$nodatafield}' where  (rd" . $c . ".value ='' or rd" . $c . ".value is null or rd" . $c . ".value=',') {$restypesql}  and r" . $c . ".ref>0 group by r" . $c . ".ref ";
                                     $union .= $union_restriction_clause;
                                     $sql_keyword_union[] = $union;
                                 } else {
                                     if ($search_sql_optimization) {
                                         $sql_keyword_union_sub_query[$c] = $keyref;
                                     } else {
                                         $filter_by_resource_field_type = "";
                                         if ($sql_restrict_by_field_types != "") {
                                             $filter_by_resource_field_type = "and k{$c}.resource_type_field in ({$sql_restrict_by_field_types})";
                                             // -1 needed for global search
                                         }
                                         $union = "SELECT resource, {$bit_or_condition} SUM(hit_count) AS score FROM resource_keyword k{$c}\n\t\t\t\t\t\t\t\t\t\tWHERE (k{$c}.keyword={$keyref} {$filter_by_resource_field_type} {$relatedsql} {$union_restriction_clause})\n\t\t\t\t\t\t\t\t\t\tGROUP BY resource";
                                         $sql_keyword_union[] = $union;
                                     }
                                 }
                                 $sql_keyword_union_aggregation[] = "bit_or(keyword_" . $c . "_found) as keyword_" . $c . "_found";
                                 $sql_keyword_union_criteria[] = "h.keyword_" . $c . "_found";
                                 // --------------------------------------------------------------------------------
                                 # Quoted search? Also add a specific join to check that the positions add up.
                                 # The UNION / bit_or() approach doesn't support position checking hence the need for additional joins to do this.
                                 if ($quoted_string) {
                                     $sql_join .= " join resource_keyword qrk_{$c} on qrk_{$c}.resource=r.ref and qrk_{$c}.keyword='{$keyref}' ";
                                     # Exclude fields from the quoted search join also
                                     if (!empty($sql_exclude_fields)) {
                                         $sql_join .= " and qrk_" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                                     }
                                     if (count($hidden_indexed_fields) > 0) {
                                         $sql_join .= " and qrk_" . $c . ".resource_type_field not in ('" . join("','", $hidden_indexed_fields) . "')";
                                     }
                                     # For keywords other than the first one, check the position is next to the previous keyword.
                                     if ($c > 1) {
                                         $last_key_offset = 1;
                                         if (isset($skipped_last) && $skipped_last) {
                                             $last_key_offset = 2;
                                         }
                                         # Support skipped keywords - if the last keyword was skipped (listed in $noadd), increase the allowed position from the previous keyword. Useful for quoted searches that contain $noadd words, e.g. "black and white" where "and" is a skipped keyword.
                                         # Also check these occurances are within the same field.
                                         $sql_join .= " and qrk_" . $c . ".position>0 and qrk_" . $c . ".position=qrk_" . ($c - 1) . ".position+" . $last_key_offset . " and qrk_" . $c . ".resource_type_field=qrk_" . ($c - 1) . ".resource_type_field";
                                     }
                                 }
                             } else {
                                 if ($omit) {
                                     # Exclude matching resources from query (omit feature)
                                     if ($sql_filter != "") {
                                         $sql_filter .= " and ";
                                     }
                                     $sql_filter .= "r.ref not in (select resource from resource_keyword where keyword='{$keyref}')";
                                     # Filter out resources that do contain the keyword.
                                 }
                             }
                             # Log this
                             if ($stats_logging) {
                                 daily_stat("Keyword usage", $keyref);
                             }
                         }
                     }
                     $skipped_last = false;
                 }
             }
         }
     }
     # Could not match on provided keywords? Attempt to return some suggestions.
     if ($fullmatch == false) {
         if ($suggested == $keywords) {
             # Nothing different to suggest.
             debug("No alternative keywords to suggest.");
             return "";
         } else {
             # Suggest alternative spellings/sound-a-likes
             $suggest = "";
             if (strpos($search, ",") === false) {
                 $suggestjoin = " ";
             } else {
                 $suggestjoin = ", ";
             }
             for ($n = 0; $n < count($suggested); $n++) {
                 if ($suggested[$n] != "") {
                     if ($suggest != "") {
                         $suggest .= $suggestjoin;
                     }
                     $suggest .= $suggested[$n];
                 }
             }
             debug("Suggesting {$suggest}");
             return $suggest;
         }
     }
     hook("additionalsqlfilter");
     hook("parametricsqlfilter", '', array($search));
     # ------ Search filtering: If search_filter is specified on the user group, then we must always apply this filter.
     global $usersearchfilter;
     $sf = explode(";", $usersearchfilter);
     if (strlen($usersearchfilter) > 0) {
         for ($n = 0; $n < count($sf); $n++) {
             $s = explode("=", $sf[$n]);
             if (count($s) != 2) {
                 exit("Search filter is not correctly configured for this user group.");
             }
             # Support for "NOT" matching. Return results only where the specified value or values are NOT set.
             $filterfield = $s[0];
             $filter_not = false;
             if (substr($filterfield, -1) == "!") {
                 $filter_not = true;
                 $filterfield = substr($filterfield, 0, -1);
                 # Strip off the exclamation mark.
             }
             # Support for multiple fields on the left hand side, pipe separated - allows OR matching across multiple fields in a basic way
             $filterfields = explode("|", escape_check($filterfield));
             # Find field(s) - multiple fields can be returned to support several fields with the same name.
             $f = sql_array("select ref value from resource_type_field where name in ('" . join("','", $filterfields) . "')");
             if (count($f) == 0) {
                 exit("Field(s) with short name '" . $filterfield . "' not found in user group search filter.");
             }
             # Find keyword(s)
             $ks = explode("|", strtolower(escape_check($s[1])));
             for ($x = 0; $x < count($ks); $x++) {
                 # Cleanse the string as keywords are stored without special characters
                 $ks[$x] = cleanse_string($ks[$x], true);
                 global $stemming;
                 if ($stemming && function_exists("GetStem")) {
                     $ks[$x] = GetStem($ks[$x]);
                 }
             }
             $modifiedsearchfilter = hook("modifysearchfilter");
             if ($modifiedsearchfilter) {
                 $ks = $modifiedsearchfilter;
             }
             $kw = sql_array("select ref value from keyword where keyword in ('" . join("','", $ks) . "')");
             if (!$filter_not) {
                 # Standard operation ('=' syntax)
                 $sql_join .= " join resource_keyword filter" . $n . " on r.ref=filter" . $n . ".resource and filter" . $n . ".resource_type_field in ('" . join("','", $f) . "') and filter" . $n . ".keyword in ('" . join("','", $kw) . "') ";
                 if ($search_filter_strict > 1) {
                     $sql_join .= " join resource_data dfilter" . $n . " on r.ref=dfilter" . $n . ".resource and dfilter" . $n . ".resource_type_field in ('" . join("','", $f) . "') and (find_in_set('" . join("', dfilter" . $n . ".value) or find_in_set('", explode("|", escape_check($s[1]))) . "', dfilter" . $n . ".value))";
                 }
             } else {
                 # Inverted NOT operation ('!=' syntax)
                 if ($sql_filter != "") {
                     $sql_filter .= " and ";
                 }
                 $sql_filter .= "r.ref not in (select resource from resource_keyword where resource_type_field in ('" . join("','", $f) . "') and keyword in ('" . join("','", $kw) . "'))";
                 # Filter out resources that do contain the keyword(s)
             }
         }
     }
     $userownfilter = hook("userownfilter");
     if ($userownfilter) {
         $sql_join .= $userownfilter;
     }
     # Handle numeric searches when $config_search_for_number=false, i.e. perform a normal search but include matches for resource ID first
     global $config_search_for_number;
     if (!$config_search_for_number && is_numeric($search)) {
         # Always show exact resource matches first.
         $order_by = "(r.ref='" . $search . "') desc," . $order_by;
     }
     # ---------------------------------------------------------------
     # union with inner join
     #
     # Populating the following array:
     #
     # $sql_keyword_union_sub_query[<keyword number>]=<keyword ref>
     #
     # will combine keyword searches in join within BIT_OR union
     # This saves on data copying to temporary MySQL tables
     # ---------------------------------------------------------------
     if ($search_sql_optimization && count($sql_keyword_union_sub_query) > 0) {
         $sql_keyword_union_sub_query_keys = array_keys($sql_keyword_union_sub_query);
         $union = "SELECT keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[0]] . ".resource";
         for ($p = 1; $p <= count($keywords); $p++) {
             $union .= "," . (isset($sql_keyword_union_sub_query[$p]) ? "TRUE" : "FALSE") . " as keyword_{$p}_found";
         }
         $union .= ",SUM(keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[0]] . ".hit_count) AS score";
         $union .= " FROM resource_keyword keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[0]];
         for ($i = 1; $i < count($sql_keyword_union_sub_query_keys); $i++) {
             $union .= " JOIN resource_keyword keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[$i]];
             $union .= " ON keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[$i - 1]];
             $union .= ".resource=keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[$i]] . ".resource";
             if ($sql_restrict_by_field_types != "") {
                 $union .= " AND keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[$i]] . ".resource_type_field IN ({$sql_restrict_by_field_types})";
             }
         }
         $union .= " WHERE";
         for ($i = 0; $i < count($sql_keyword_union_sub_query_keys); $i++) {
             if ($i > 0) {
                 $union .= " AND";
             }
             $union .= " keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[$i]] . ".keyword=" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[$i]];
         }
         $union .= " GROUP BY keyword" . $sql_keyword_union_sub_query[$sql_keyword_union_sub_query_keys[0]] . ".resource";
         $sql_keyword_union[] = $union;
         // add to normal union array
     }
     # ---------------------------------------------------------------
     # Keyword union assembly.
     # Use UNIONs for keyword matching instead of the older JOIN technique - much faster
     # Assemble the new join from the stored unions
     # ---------------------------------------------------------------
     if (count($sql_keyword_union) > 0) {
         $sql_join .= " join (\n\t\tselect resource,sum(score) as score,\n\t\t" . join(", ", $sql_keyword_union_aggregation) . " from\n\t\t(" . join(" union ", $sql_keyword_union) . ") as hits group by resource) as h on h.resource=r.ref ";
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= join(" and ", $sql_keyword_union_criteria);
         # Use amalgamated resource_keyword hitcounts for scoring (relevance matching based on previous user activity)
         $score = "h.score";
     }
     # --------------------------------------------------------------------------------
     # Special Searches (start with an exclamation mark)
     # --------------------------------------------------------------------------------
     # Can only search for resources that belong to themes
     if (checkperm("J")) {
         $sql_join = " join collection_resource jcr on jcr.resource=r.ref join collection jc on jcr.collection=jc.ref and length(jc.theme)>0 " . $sql_join;
     }
     # ------ Special searches ------
     # View Last
     if (substr($search, 0, 5) == "!last") {
         # Replace r2.ref with r.ref for the alternative query used here.
         $order_by = str_replace("r.ref", "r2.ref", $order_by);
         if ($orig_order == "relevance") {
             $order_by = "r2.ref desc";
         }
         # Extract the number of records to produce
         $last = explode(",", $search);
         $last = str_replace("!last", "", $last[0]);
         if (!is_numeric($last)) {
             $last = 1000;
             $search = "!last1000";
         }
         # 'Last' must be a number. SQL injection filter.
         # Fix the order by for this query (special case due to inner query)
         $order_by = str_replace("r.rating", "rating", $order_by);
         return sql_query($sql_prefix . "select distinct *,r2.hit_count score from (select {$select} from resource r {$sql_join} where {$sql_filter} order by ref desc limit {$last} ) r2 order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # View Resources With No Downloads
     if (substr($search, 0, 12) == "!nodownloads") {
         if ($orig_order == "relevance") {
             $order_by = "ref desc";
         }
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where {$sql_filter} and ref not in (select distinct object_ref from daily_stat where activity_type='Resource download') order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Duplicate Resources (based on file_checksum)
     if (substr($search, 0, 11) == "!duplicates") {
         # find duplicates of a given resource
         # Extract the resource ID
         $ref = explode(" ", $search);
         $ref = str_replace("!duplicates", "", $ref[0]);
         $ref = explode(",", $ref);
         // just get the number
         $ref = escape_check($ref[0]);
         if ($ref != "") {
             $results = sql_query("select distinct r.hit_count score, {$select} from resource r {$sql_join}  where {$sql_filter} and file_checksum= (select file_checksum from (select file_checksum from resource where archive = 0 and ref={$ref} and file_checksum is not null)r2) order by file_checksum", false, $fetchrows);
             $count = count($results);
             if ($count > 1) {
                 return $results;
             } else {
                 return false;
             }
         } else {
             return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where {$sql_filter} and file_checksum in (select file_checksum from (select file_checksum from resource where archive = 0 and file_checksum <> '' and file_checksum is not null group by file_checksum having count(file_checksum)>1)r2) order by file_checksum" . $sql_suffix, false, $fetchrows);
         }
     }
     # View Collection
     if (substr($search, 0, 11) == '!collection') {
         if ($orig_order == 'relevance') {
             $order_by = 'c.sortorder ASC, c.date_added DESC, r.ref DESC';
         }
         $colcustperm = $sql_join;
         $colcustfilter = $sql_filter;
         // to avoid allowing this sql_filter to be modified by the $access_override search in the smart collection update below!!!
         # Special case if a key has been provided.
         if (getval('k', '') != '') {
             $sql_filter = 'r.ref > 0';
         }
         # Extract the collection number
         $collection = explode(' ', $search);
         $collection = str_replace('!collection', '', $collection[0]);
         $collection = explode(',', $collection);
         // just get the number
         $collection = escape_check($collection[0]);
         # Check access
         if (!collection_readable($collection)) {
             return false;
         }
         # Smart collections update
         global $allow_smart_collections, $smart_collections_async;
         if ($allow_smart_collections) {
             global $smartsearch_ref_cache;
             if (isset($smartsearch_ref_cache[$collection])) {
                 $smartsearch_ref = $smartsearch_ref_cache[$collection];
                 // this value is pretty much constant
             } else {
                 $smartsearch_ref = sql_value('SELECT savedsearch value FROM collection WHERE ref="' . $collection . '"', '');
                 $smartsearch_ref_cache[$collection] = $smartsearch_ref;
             }
             global $php_path;
             if ($smartsearch_ref != '' && !$return_disk_usage) {
                 if ($smart_collections_async && isset($php_path) && file_exists($php_path . '/php')) {
                     exec($php_path . '/php ' . dirname(__FILE__) . '/../pages/ajax/update_smart_collection.php ' . escapeshellarg($collection) . ' ' . '> /dev/null 2>&1 &');
                 } else {
                     include dirname(__FILE__) . '/../pages/ajax/update_smart_collection.php';
                 }
             }
         }
         $result = sql_query($sql_prefix . "select distinct c.date_added,c.comment,c.purchase_size,c.purchase_complete,r.hit_count score,length(c.comment) commentset, {$select} from resource r  join collection_resource c on r.ref=c.resource {$colcustperm}  where c.collection='" . $collection . "' and {$colcustfilter} group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
         hook('beforereturnresults', '', array($result, $archive));
         return $result;
     }
     # View Related
     if (substr($search, 0, 8) == "!related") {
         # Extract the resource number
         $resource = explode(" ", $search);
         $resource = str_replace("!related", "", $resource[0]);
         $order_by = str_replace("r.", "", $order_by);
         # UNION below doesn't like table aliases in the order by.
         global $pagename, $related_search_show_self;
         $sql_self = '';
         if ($related_search_show_self && $pagename == 'search') {
             $sql_self = " select distinct r.hit_count score, {$select} from resource r {$sql_join} where r.ref={$resource} and {$sql_filter} group by r.ref UNION ";
         }
         return sql_query($sql_prefix . $sql_self . "select distinct r.hit_count score, {$select} from resource r join resource_related t on (t.related=r.ref and t.resource='" . $resource . "') {$sql_join}  where 1=1 and {$sql_filter} group by r.ref \n        UNION\n        select distinct r.hit_count score, {$select} from resource r join resource_related t on (t.resource=r.ref and t.related='" . $resource . "') {$sql_join}  where 1=1 and {$sql_filter} group by r.ref \n        order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Geographic search
     if (substr($search, 0, 4) == "!geo") {
         $geo = explode("t", str_replace(array("m", "p"), array("-", "."), substr($search, 4)));
         # Specially encoded string to avoid keyword splitting
         $bl = explode("b", $geo[0]);
         $tr = explode("b", $geo[1]);
         $sql = "select r.hit_count score, {$select} from resource r {$sql_join} where \n\n                    geo_lat > '" . escape_check($bl[0]) . "'\n              and   geo_lat < '" . escape_check($tr[0]) . "'        \n              and   geo_long > '" . escape_check($bl[1]) . "'       \n              and   geo_long < '" . escape_check($tr[1]) . "'       \n                          \n         and {$sql_filter} group by r.ref order by {$order_by}";
         return sql_query($sql_prefix . $sql . $sql_suffix, false, $fetchrows);
     }
     # Colour search
     if (substr($search, 0, 7) == "!colour") {
         $colour = explode(" ", $search);
         $colour = str_replace("!colour", "", $colour[0]);
         $sql = "select r.hit_count score, {$select} from resource r {$sql_join}\n                where \n                    colour_key like '" . escape_check($colour) . "%'\n                or  colour_key like '_" . escape_check($colour) . "%'\n                          \n         and {$sql_filter} group by r.ref order by {$order_by}";
         return sql_query($sql_prefix . $sql . $sql_suffix, false, $fetchrows);
     }
     # Similar to a colour
     if (substr($search, 0, 4) == "!rgb") {
         $rgb = explode(":", $search);
         $rgb = explode(",", $rgb[1]);
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where has_image=1 and {$sql_filter} group by r.ref order by (abs(image_red-" . $rgb[0] . ")+abs(image_green-" . $rgb[1] . ")+abs(image_blue-" . $rgb[2] . ")) asc limit 500" . $sql_suffix, false, $fetchrows);
     }
     # Has no preview image
     if (substr($search, 0, 10) == "!nopreview") {
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where has_image=0 and {$sql_filter} group by r.ref" . $sql_suffix, false, $fetchrows);
     }
     # Similar to a colour by key
     if (substr($search, 0, 10) == "!colourkey") {
         # Extract the colour key
         $colourkey = explode(" ", $search);
         $colourkey = str_replace("!colourkey", "", $colourkey[0]);
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where has_image=1 and left(colour_key,4)='" . $colourkey . "' and {$sql_filter} group by r.ref" . $sql_suffix, false, $fetchrows);
     }
     global $config_search_for_number;
     if ($config_search_for_number && is_numeric($search) || substr($search, 0, 9) == "!resource") {
         $theref = escape_check($search);
         $theref = preg_replace("/[^0-9]/", "", $theref);
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where r.ref='{$theref}' and {$sql_filter} group by r.ref" . $sql_suffix);
     }
     # Searching for pending archive
     if (substr($search, 0, 15) == "!archivepending") {
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where archive=1 and ref>0 group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     if (substr($search, 0, 12) == "!userpending") {
         if ($orig_order == "rating") {
             $order_by = "request_count desc," . $order_by;
         }
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where archive=-1 and ref>0 group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # View Contributions
     if (substr($search, 0, 14) == "!contributions") {
         global $userref;
         # Extract the user ref
         $cuser = explode(" ", $search);
         $cuser = str_replace("!contributions", "", $cuser[0]);
         if ($userref == $cuser) {
             $sql_filter = "archive='{$archive}'";
             $sql_join = "";
         }
         # Disable permissions when viewing your own contributions - only restriction is the archive status
         $select = str_replace(",rca.access group_access,rca2.access user_access ", ",null group_access, null user_access ", $select);
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where created_by='" . $cuser . "' and r.ref > 0 and {$sql_filter} group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Search for resources with images
     if ($search == "!images") {
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join}  where has_image=1 group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Search for resources not used in Collections
     if (substr($search, 0, 7) == "!unused") {
         return sql_query($sql_prefix . "SELECT distinct {$select} FROM resource r {$sql_join}  where r.ref>0 and r.ref not in (select c.resource from collection_resource c) and {$sql_filter}" . $sql_suffix, false, $fetchrows);
     }
     # Search for a list of resources
     # !listall = archive state is not applied as a filter to the list of resources.
     if (substr($search, 0, 5) == "!list") {
         $resources = explode(" ", $search);
         if (substr($search, 0, 8) == "!listall") {
             $resources = str_replace("!listall", "", $resources[0]);
         } else {
             $resources = str_replace("!list", "", $resources[0]);
         }
         $resources = explode(",", $resources);
         // separate out any additional keywords
         $resources = escape_check($resources[0]);
         if (strlen(trim($resources)) == 0) {
             $resources = "where r.ref IS NULL";
         } else {
             $resources = "where (r.ref='" . str_replace(":", "' OR r.ref='", $resources) . "')";
         }
         return sql_query($sql_prefix . "SELECT distinct r.hit_count score, {$select} FROM resource r {$sql_join} {$resources} and {$sql_filter} order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # View resources that have data in the specified field reference - useful if deleting unused fields
     if (substr($search, 0, 8) == "!hasdata") {
         $fieldref = intval(trim(substr($search, 8)));
         $sql_join .= " join resource_data on r.ref=resource_data.resource and resource_data.resource_type_field={$fieldref} and resource_data.value<>'' ";
         return sql_query($sql_prefix . "select distinct r.hit_count score, {$select} from resource r {$sql_join} and r.ref > 0 and {$sql_filter} group by r.ref order by {$order_by}" . $sql_suffix, false, $fetchrows);
     }
     # Within this hook implementation, set the value of the global $sql variable:
     # Since there will only be one special search executed at a time, only one of the
     # hook implementations will set the value.  So, you know that the value set
     # will always be the correct one (unless two plugins use the same !<type> value).
     $sql = "";
     hook("addspecialsearch", "", array($search));
     if ($sql != "") {
         debug("Addspecialsearch hook returned useful results.");
         return sql_query($sql_prefix . $sql . $sql_suffix, false, $fetchrows);
     }
     # -------------------------------------------------------------------------------------
     # Standard Searches
     # -------------------------------------------------------------------------------------
     # We've reached this far without returning.
     # This must be a standard (non-special) search.
     # Construct and perform the standard search query.
     #$sql="";
     if ($sql_filter != "") {
         if ($sql != "") {
             $sql .= " and ";
         }
         $sql .= $sql_filter;
     }
     # Append custom permissions
     $t .= $sql_join;
     if ($score == "") {
         $score = "r.hit_count";
     }
     # In case score hasn't been set (i.e. empty search)
     global $max_results;
     if ($t2 != "" && $sql != "") {
         $sql = " and " . $sql;
     }
     # Compile final SQL
     # Performance enhancement - set return limit to number of rows required
     if ($search_sql_double_pass_mode && $fetchrows != -1) {
         $max_results = $fetchrows;
     }
     $results_sql = $sql_prefix . "select distinct {$score} score, {$select} from resource r" . $t . "  where {$t2} {$sql} group by r.ref order by {$order_by} limit {$max_results}" . $sql_suffix;
     # Debug
     debug("altert " . $results_sql);
     # Execute query
     $result = sql_query($results_sql, false, $fetchrows);
     # Performance improvement - perform a second count-only query and pad the result array as necessary
     if ($search_sql_double_pass_mode && count($result) >= $max_results) {
         $count_sql = "select count(distinct r.ref) value from resource r" . $t . "  where {$t2} {$sql}";
         $count = sql_value($count_sql, 0);
         $result = array_pad($result, $count, 0);
     }
     debug("Search found " . count($result) . " results");
     if (count($result) > 0) {
         hook("beforereturnresults", "", array($result, $archive));
         return $result;
     }
     hook('zero_search_results');
     # (temp) - no suggestion for field-specific searching for now - TO DO: modify function below to support this
     if (strpos($search, ":") !== false) {
         return "";
     }
     # All keywords resolved OK, but there were no matches
     # Remove keywords, least used first, until we get results.
     $lsql = "";
     $omitmatch = false;
     for ($n = 0; $n < count($keywords); $n++) {
         if (substr($keywords[$n], 0, 1) == "-") {
             $omitmatch = true;
             $omit = $keywords[$n];
         }
         if ($lsql != "") {
             $lsql .= " or ";
         }
         $lsql .= "keyword='" . escape_check($keywords[$n]) . "'";
     }
     if ($omitmatch) {
         return trim_spaces(str_replace(" " . $omit . " ", " ", " " . join(" ", $keywords) . " "));
     }
     if ($lsql != "") {
         $least = sql_value("select keyword value from keyword where {$lsql} order by hit_count asc limit 1", "");
         return trim_spaces(str_replace(" " . $least . " ", " ", " " . join(" ", $keywords) . " "));
     } else {
         return array();
     }
 }
 function do_search($search, $restypes = "", $order_by = "relevance", $archive = 0, $fetchrows = -1, $sort = "desc", $access_override = false, $starsearch = 0, $ignore_filters = false, $return_disk_usage = false, $recent_search_daylimit = "", $go = false, $stats_logging = true)
 {
     debug("search={$search} {$go} {$fetchrows} restypes={$restypes} archive={$archive} daylimit={$recent_search_daylimit}");
     # globals needed for hooks
     global $sql, $order, $select, $sql_join, $sql_filter, $orig_order, $collections_omit_archived, $search_sql_double_pass_mode, $usergroup, $search_filter_strict, $default_sort;
     $alternativeresults = hook("alternativeresults", "", array($go));
     if ($alternativeresults) {
         return $alternativeresults;
     }
     $modifyfetchrows = hook("modifyfetchrows", "", array($fetchrows));
     if ($modifyfetchrows) {
         $fetchrows = $modifyfetchrows;
     }
     # Takes a search string $search, as provided by the user, and returns a results set
     # of matching resources.
     # If there are no matches, instead returns an array of suggested searches.
     # $restypes is optionally used to specify which resource types to search.
     # $access_override is used by smart collections, so that all all applicable resources can be judged regardless of the final access-based results
     # Check valid sort
     if (!in_array(strtolower($sort), array("asc", "desc"))) {
         $sort = "asc";
     }
     # resolve $order_by to something meaningful in sql
     $orig_order = $order_by;
     global $date_field;
     $order = array("relevance" => "score {$sort}, user_rating {$sort}, hit_count {$sort}, field{$date_field} {$sort},r.ref {$sort}", "popularity" => "user_rating {$sort},hit_count {$sort},field{$date_field} {$sort},r.ref {$sort}", "rating" => "r.rating {$sort}, user_rating {$sort}, score {$sort},r.ref {$sort}", "date" => "field{$date_field} {$sort},r.ref {$sort}", "colour" => "has_image {$sort},image_blue {$sort},image_green {$sort},image_red {$sort},field{$date_field} {$sort},r.ref {$sort}", "country" => "country {$sort},r.ref {$sort}", "title" => "title {$sort},r.ref {$sort}", "file_path" => "file_path {$sort},r.ref {$sort}", "resourceid" => "r.ref {$sort}", "resourcetype" => "resource_type {$sort},r.ref {$sort}", "titleandcountry" => "title {$sort},country {$sort}", "random" => "RAND()", "status" => "archive {$sort}");
     if (!in_array($order_by, $order) && substr($order_by, 0, 5) == "field") {
         if (!is_numeric(str_replace("field", "", $order_by))) {
             exit("Order field incorrect.");
         }
         $order[$order_by] = "{$order_by} {$sort}";
     }
     hook("modifyorderarray");
     # Recognise a quoted search, which is a search for an exact string
     global $quoted_string;
     $quoted_string = false;
     if (substr($search, 0, 1) == "\"" && substr($search, -1, 1) == "\"") {
         $quoted_string = true;
         $search = substr($search, 1, -1);
     }
     $order_by = $order[$order_by];
     $keywords = split_keywords($search);
     foreach (get_indexed_resource_type_fields() as $resource_type_field) {
         add_verbatim_keywords($keywords, $search, $resource_type_field, true);
         // add any regex matched verbatim keywords for those indexed resource type fields
     }
     $search = trim($search);
     # Dedupe keywords (not for quoted strings as the user may be looking for the same word multiple times together in this instance)
     if (!$quoted_string) {
         $keywords = array_values(array_unique($keywords));
     }
     $modified_keywords = hook('dosearchmodifykeywords', '', array($keywords));
     if ($modified_keywords) {
         $keywords = $modified_keywords;
     }
     # -- Build up filter SQL that will be used for all queries
     $sql_filter = search_filter($search, $archive, $restypes, $starsearch, $recent_search_daylimit, $access_override, $return_disk_usage);
     # Initialise variables.
     $sql = "";
     $sql_keyword_union_whichkeys = array();
     $sql_keyword_union = array();
     $sql_keyword_union_aggregation = array();
     $sql_keyword_union_criteria = array();
     $sql_keyword_union_sub_query = array();
     # If returning disk used by the resources in the search results ($return_disk_usage=true) then wrap the returned SQL in an outer query that sums disk usage.
     $sql_prefix = "";
     $sql_suffix = "";
     if ($return_disk_usage) {
         $sql_prefix = "select sum(disk_usage) total_disk_usage,count(*) total_resources from (";
         $sql_suffix = ") resourcelist";
     }
     # ------ Advanced 'custom' permissions, need to join to access table.
     $sql_join = "";
     global $k;
     if (!checkperm("v") && !$access_override) {
         global $usergroup;
         global $userref;
         # one extra join (rca2) is required for user specific permissions (enabling more intelligent watermarks in search view)
         # the original join is used to gather group access into the search query as well.
         $sql_join = " left outer join resource_custom_access rca2 on r.ref=rca2.resource and rca2.user='******'  and (rca2.user_expires is null or rca2.user_expires>now()) and rca2.access<>2  ";
         $sql_join .= " left outer join resource_custom_access rca on r.ref=rca.resource and rca.usergroup='{$usergroup}' and rca.access<>2 ";
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         # If rca.resource is null, then no matching custom access record was found
         # If r.access is also 3 (custom) then the user is not allowed access to this resource.
         # Note that it's normal for null to be returned if this is a resource with non custom permissions (r.access<>3).
         $sql_filter .= " not(rca.resource is null and r.access=3)";
     }
     # Join thumbs_display_fields to resource table
     $select = "r.ref, r.resource_type, r.has_image, r.is_transcoding, r.hit_count, r.creation_date, r.rating, r.user_rating, r.user_rating_count, r.user_rating_total, r.file_extension, r.preview_extension, r.image_red, r.image_green, r.image_blue, r.thumb_width, r.thumb_height, r.archive, r.access, r.colour_key, r.created_by, r.file_modified, r.file_checksum, r.request_count, r.new_hit_count, r.expiry_notification_sent, r.preview_tweaks, r.file_path ";
     $modified_select = hook("modifyselect");
     if ($modified_select) {
         $select .= $modified_select;
     }
     $modified_select2 = hook("modifyselect2");
     if ($modified_select2) {
         $select .= $modified_select2;
     }
     # Return disk usage for each resource if returning sum of disk usage.
     if ($return_disk_usage) {
         $select .= ",r.disk_usage";
     }
     # select group and user access rights if available, otherwise select null values so columns can still be used regardless
     # this makes group and user specific access available in the basic search query, which can then be passed through access functions
     # in order to eliminate many single queries.
     if (!checkperm("v") && !$access_override) {
         $select .= ",rca.access group_access,rca2.access user_access ";
     } else {
         $select .= ",null group_access, null user_access ";
     }
     # add 'joins' to select (adding them
     $joins = get_resource_table_joins();
     foreach ($joins as $datajoin) {
         $select .= ",r.field" . $datajoin . " ";
     }
     # Prepare SQL to add join table for all provided keywods
     $suggested = $keywords;
     # a suggested search
     $fullmatch = true;
     $c = 0;
     $t = "";
     $t2 = "";
     $score = "";
     $skipped_last = false;
     $keysearch = true;
     # Do not process if a numeric search is provided (resource ID)
     global $config_search_for_number, $category_tree_search_use_and;
     if ($config_search_for_number && is_numeric($search)) {
         $keysearch = false;
     }
     # Fetch a list of fields that are not available to the user - these must be omitted from the search.
     $hidden_indexed_fields = get_hidden_indexed_fields();
     # This is a performance enhancement that will discard any keyword matches for fields that are not supposed to be indexed.
     $sql_restrict_by_field_types = "";
     global $search_sql_force_field_index_check;
     if (isset($search_sql_force_field_index_check) && $search_sql_force_field_index_check && $restypes != "") {
         $sql_restrict_by_field_types = sql_value("select group_concat(ref) as value from resource_type_field where keywords_index=1 and resource_type in ({$restypes})", "");
         if ($sql_restrict_by_field_types != "") {
             $sql_restrict_by_field_types = "-1," . $sql_restrict_by_field_types;
             // -1 needed for global search
         }
     }
     if ($keysearch) {
         for ($n = 0; $n < count($keywords); $n++) {
             $keyword = $keywords[$n];
             if (substr($keyword, 0, 1) != "!" || substr($keyword, 0, 6) == "!empty") {
                 global $date_field;
                 $field = 0;
                 #echo "<li>$keyword<br/>";
                 if (strpos($keyword, ":") !== false && !$ignore_filters) {
                     $kw = explode(":", $keyword, 2);
                     global $datefieldinfo_cache;
                     if (isset($datefieldinfo_cache[$kw[0]])) {
                         $datefieldinfo = $datefieldinfo_cache[$kw[0]];
                     } else {
                         $datefieldinfo = sql_query("select ref from resource_type_field where name='" . escape_check($kw[0]) . "' and type IN (4,6,10)", 0);
                         $datefieldinfo_cache[$kw[0]] = $datefieldinfo;
                     }
                     if (count($datefieldinfo) && substr($kw[1], 0, 5) != "range") {
                         $c++;
                         $datefieldinfo = $datefieldinfo[0];
                         $datefield = $datefieldinfo["ref"];
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $val = str_replace("n", "_", $kw[1]);
                         $val = str_replace("|", "-", $val);
                         $sql_filter .= "rd" . $c . ".value like '" . $val . "%' ";
                         $sql_join .= " join resource_data rd" . $c . " on rd" . $c . ".resource=r.ref and rd" . $c . ".resource_type_field='" . $datefield . "'";
                     } elseif ($kw[0] == "day") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '____-__-" . $kw[1] . "%' ";
                     } elseif ($kw[0] == "month") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '____-" . $kw[1] . "%' ";
                     } elseif ($kw[0] == "year") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} like '" . $kw[1] . "%' ";
                     } elseif ($kw[0] == "startdate") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} >= '" . $kw[1] . "' ";
                     } elseif ($kw[0] == "enddate") {
                         if ($sql_filter != "") {
                             $sql_filter .= " and ";
                         }
                         $sql_filter .= "r.field{$date_field} <= '" . $kw[1] . " 23:59:59' ";
                     } elseif (count($datefieldinfo) && substr($kw[1], 0, 5) == "range") {
                         $c++;
                         $rangefield = $datefieldinfo[0]["ref"];
                         $daterange = false;
                         $rangestring = substr($kw[1], 5);
                         if (strpos($rangestring, "start") !== FALSE) {
                             $rangestart = str_replace(" ", "-", $rangestring);
                             if ($sql_filter != "") {
                                 $sql_filter .= " and ";
                             }
                             $sql_filter .= "rd" . $c . ".value >= '" . substr($rangestart, strpos($rangestart, "start") + 5, 10) . "'";
                         }
                         if (strpos($kw[1], "end") !== FALSE) {
                             $rangeend = str_replace(" ", "-", $rangestring);
                             if ($sql_filter != "") {
                                 $sql_filter .= " and ";
                             }
                             $sql_filter .= "rd" . $c . ".value <= '" . substr($rangeend, strpos($rangeend, "end") + 3, 10) . " 23:59:59'";
                         }
                         $sql_join .= " join resource_data rd" . $c . " on rd" . $c . ".resource=r.ref and rd" . $c . ".resource_type_field='" . $rangefield . "'";
                     } elseif (!hook('customsearchkeywordfilter', null, array($kw))) {
                         $ckeywords = explode(";", $kw[1]);
                         # Fetch field info
                         global $fieldinfo_cache;
                         if (isset($fieldinfo_cache[$kw[0]])) {
                             $fieldinfo = $fieldinfo_cache[$kw[0]];
                         } else {
                             $fieldinfo = sql_query("select ref,type from resource_type_field where name='" . escape_check($kw[0]) . "'", 0);
                             $fieldinfo_cache[$kw[0]] = $fieldinfo;
                         }
                         if (count($fieldinfo) == 0) {
                             debug("Field short name not found.");
                             return false;
                         }
                         # Create an array of matching field IDs.
                         $fields = array();
                         foreach ($fieldinfo as $fi) {
                             if (in_array($fi["ref"], $hidden_indexed_fields)) {
                                 # Attempt to directly search field that the user does not have access to.
                                 return false;
                             }
                             # Add to search array
                             $fields[] = $fi["ref"];
                         }
                         # Special handling for dates
                         if ($fieldinfo[0]["type"] == 4 || $fieldinfo[0]["type"] == 6 || $fieldinfo[0]["type"] == 10) {
                             $ckeywords = array(str_replace(" ", "-", $kw[1]));
                         }
                         #special SQL generation for category trees to use AND instead of OR
                         if ($fieldinfo[0]["type"] == 7 && $category_tree_search_use_and) {
                             for ($m = 0; $m < count($ckeywords); $m++) {
                                 $keyref = resolve_keyword($ckeywords[$m]);
                                 if (!($keyref === false)) {
                                     $c++;
                                     # Add related keywords
                                     $related = get_related_keywords($keyref);
                                     $relatedsql = "";
                                     for ($r = 0; $r < count($related); $r++) {
                                         $relatedsql .= " or k" . $c . ".keyword='" . $related[$r] . "'";
                                     }
                                     # Form join
                                     $sql_join .= " join resource_keyword k" . $c . " on k" . $c . ".resource=r.ref and k" . $c . ".resource_type_field in ('" . join("','", $fields) . "') and (k" . $c . ".keyword='{$keyref}' {$relatedsql})";
                                     if ($score != "") {
                                         $score .= "+";
                                     }
                                     $score .= "k" . $c . ".hit_count";
                                     # Log this
                                     if ($stats_logging) {
                                         daily_stat("Keyword usage", $keyref);
                                     }
                                 }
                             }
                         } else {
                             $c++;
                             # work through all options in an OR approach for multiple selects on the same field
                             $searchkeys = array();
                             for ($m = 0; $m < count($ckeywords); $m++) {
                                 $keyref = resolve_keyword($ckeywords[$m]);
                                 if ($keyref === false) {
                                     $keyref = -1;
                                 }
                                 $searchkeys[] = $keyref;
                                 # Also add related.
                                 $related = get_related_keywords($keyref);
                                 for ($o = 0; $o < count($related); $o++) {
                                     $searchkeys[] = $related[$o];
                                 }
                                 # Log this
                                 if ($stats_logging) {
                                     daily_stat("Keyword usage", $keyref);
                                 }
                             }
                             $union = "select resource,";
                             for ($p = 1; $p <= count($keywords); $p++) {
                                 if ($p == $c) {
                                     $union .= "true";
                                 } else {
                                     $union .= "false";
                                 }
                                 $union .= " as keyword_" . $p . "_found,";
                             }
                             $union .= "hit_count as score from resource_keyword k" . $c . " where (k" . $c . ".keyword='{$keyref}' or k" . $c . ".keyword in ('" . join("','", $searchkeys) . "')) and k" . $c . ".resource_type_field in ('" . join("','", $fields) . "')";
                             if (!empty($sql_exclude_fields)) {
                                 $union .= " and k" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                             }
                             if (count($hidden_indexed_fields) > 0) {
                                 $union .= " and k" . $c . ".resource_type_field not in ('" . join("','", $hidden_indexed_fields) . "')";
                             }
                             $sql_keyword_union_aggregation[] = "bit_or(keyword_" . $c . "_found) as keyword_" . $c . "_found";
                             $sql_keyword_union_criteria[] = "h.keyword_" . $c . "_found";
                             $sql_keyword_union[] = $union;
                         }
                     }
                 } else {
                     # Normal keyword (not tied to a field) - searches all fields that the user has access to
                     # If ignoring field specifications then remove them.
                     if (strpos($keyword, ":") !== false && $ignore_filters) {
                         $s = explode(":", $keyword);
                         $keyword = $s[1];
                     }
                     # Omit resources containing this keyword?
                     $omit = false;
                     if (substr($keyword, 0, 1) == "-") {
                         $omit = true;
                         $keyword = substr($keyword, 1);
                     }
                     # Search for resources with an empty field, ex: !empty18  or  !emptycaption
                     $empty = false;
                     if (substr($keyword, 0, 6) == "!empty") {
                         $nodatafield = str_replace("!empty", "", $keyword);
                         if (!is_numeric($nodatafield)) {
                             $nodatafield = sql_value("select ref value from resource_type_field where name='" . escape_check($nodatafield) . "'", "");
                         }
                         if ($nodatafield == "" || !is_numeric($nodatafield)) {
                             exit('invalid !empty search');
                         }
                         $empty = true;
                     }
                     global $noadd, $wildcard_always_applied;
                     if (in_array($keyword, $noadd)) {
                         $skipped_last = true;
                     } else {
                         # Handle wildcards
                         $wildcards = array();
                         if (strpos($keyword, "*") !== false || $wildcard_always_applied) {
                             if ($wildcard_always_applied && strpos($keyword, "*") === false) {
                                 # Suffix asterisk if none supplied and using $wildcard_always_applied mode.
                                 $keyword = $keyword . "*";
                             }
                             # Keyword contains a wildcard. Expand.
                             global $wildcard_expand_limit;
                             $wildcards = sql_array("select ref value from keyword where keyword like '" . escape_check(str_replace("*", "%", $keyword)) . "' order by hit_count desc limit " . $wildcard_expand_limit);
                         }
                         $keyref = resolve_keyword(str_replace('*', '', $keyword));
                         # Resolve keyword. Ignore any wildcards when resolving. We need wildcards to be present later but not here.
                         if ($keyref === false && !$omit && !$empty && count($wildcards) == 0) {
                             $fullmatch = false;
                             $soundex = resolve_soundex($keyword);
                             if ($soundex === false) {
                                 # No keyword match, and no keywords sound like this word. Suggest dropping this word.
                                 $suggested[$n] = "";
                             } else {
                                 # No keyword match, but there's a word that sounds like this word. Suggest this word instead.
                                 $suggested[$n] = "<i>" . $soundex . "</i>";
                             }
                         } else {
                             if ($keyref === false) {
                                 # make a new keyword
                                 $keyref = resolve_keyword(str_replace('*', '', $keyword), true);
                             }
                             # Key match, add to query.
                             $c++;
                             $relatedsql = "";
                             if (!$quoted_string) {
                                 # Add related keywords
                                 $related = get_related_keywords($keyref);
                                 # Merge wildcard expansion with related keywords
                                 $related = array_merge($related, $wildcards);
                                 if (count($related) > 0) {
                                     $relatedsql = " or k" . $c . ".keyword IN ('" . join("','", $related) . "')";
                                 }
                             }
                             # Form join
                             $sql_exclude_fields = hook("excludefieldsfromkeywordsearch");
                             if (!$omit) {
                                 # Include in query
                                 // --------------------------------------------------------------------------------
                                 // Start of normal union for resource keywords
                                 // --------------------------------------------------------------------------------
                                 // add false for keyword matches other than the current one
                                 $bit_or_condition = "";
                                 for ($p = 1; $p <= count($keywords); $p++) {
                                     if ($p == $c) {
                                         $bit_or_condition .= "true";
                                     } else {
                                         $bit_or_condition .= "false";
                                     }
                                     $bit_or_condition .= " as keyword_" . $p . "_found,";
                                 }
                                 // these restrictions apply to both !empty searches as well as normal keyword searches (i.e. both branches of next if statement)
                                 $union_restriction_clause = "";
                                 if (!empty($sql_exclude_fields)) {
                                     $union_restriction_clause .= " and k" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                                 }
                                 if (count($hidden_indexed_fields) > 0) {
                                     $union_restriction_clause .= " and k" . $c . ".resource_type_field not in ('" . join("','", $hidden_indexed_fields) . "')";
                                 }
                                 if ($empty) {
                                     $rtype = sql_value("select resource_type value from resource_type_field where ref='{$nodatafield}'", 0);
                                     if ($rtype != 0) {
                                         if ($rtype == 999) {
                                             $restypesql = "and (r" . $c . ".archive=1 or r" . $c . ".archive=2) and ";
                                             if ($sql_filter != "") {
                                                 $sql_filter .= " and ";
                                             }
                                             $sql_filter .= str_replace("r" . $c . ".archive='0'", "(r" . $c . ".archive=1 or r" . $c . ".archive=2)", $sql_filter);
                                         } else {
                                             $restypesql = "and r" . $c . ".resource_type ='{$rtype}' ";
                                         }
                                     } else {
                                         $restypesql = "";
                                     }
                                     $union = "select ref as resource, {$bit_or_condition} 1 as score from resource r" . $c . " left outer join resource_data rd" . $c . " on r" . $c . ".ref=rd" . $c . ".resource and rd" . $c . ".resource_type_field='{$nodatafield}' where  (rd" . $c . ".value ='' or rd" . $c . ".value is null or rd" . $c . ".value=',') {$restypesql}  and r" . $c . ".ref>0 group by r" . $c . ".ref ";
                                     $union .= $union_restriction_clause;
                                     $sql_keyword_union[] = $union;
                                 } else {
                                     $filter_by_resource_field_type = "";
                                     if ($sql_restrict_by_field_types != "") {
                                         $filter_by_resource_field_type = "and k{$c}.resource_type_field in ({$sql_restrict_by_field_types})";
                                         // -1 needed for global search
                                     }
                                     $union = "SELECT resource, {$bit_or_condition} SUM(hit_count) AS score FROM resource_keyword k{$c}\n\t\t\t\t\t\t\t\t\tWHERE (k{$c}.keyword={$keyref} {$filter_by_resource_field_type} {$relatedsql} {$union_restriction_clause})\n\t\t\t\t\t\t\t\t\tGROUP BY resource";
                                     $sql_keyword_union[] = $union;
                                 }
                                 $sql_keyword_union_aggregation[] = "bit_or(keyword_" . $c . "_found) as keyword_" . $c . "_found";
                                 $sql_keyword_union_criteria[] = "h.keyword_" . $c . "_found";
                                 // --------------------------------------------------------------------------------
                                 # Quoted search? Also add a specific join to check that the positions add up.
                                 # The UNION / bit_or() approach doesn't support position checking hence the need for additional joins to do this.
                                 if ($quoted_string) {
                                     $sql_join .= " join resource_keyword qrk_{$c} on qrk_{$c}.resource=r.ref and qrk_{$c}.keyword='{$keyref}' ";
                                     # Exclude fields from the quoted search join also
                                     if (!empty($sql_exclude_fields)) {
                                         $sql_join .= " and qrk_" . $c . ".resource_type_field not in (" . $sql_exclude_fields . ")";
                                     }
                                     if (count($hidden_indexed_fields) > 0) {
                                         $sql_join .= " and qrk_" . $c . ".resource_type_field not in ('" . join("','", $hidden_indexed_fields) . "')";
                                     }
                                     # For keywords other than the first one, check the position is next to the previous keyword.
                                     if ($c > 1) {
                                         $last_key_offset = 1;
                                         if (isset($skipped_last) && $skipped_last) {
                                             $last_key_offset = 2;
                                         }
                                         # Support skipped keywords - if the last keyword was skipped (listed in $noadd), increase the allowed position from the previous keyword. Useful for quoted searches that contain $noadd words, e.g. "black and white" where "and" is a skipped keyword.
                                         # Also check these occurances are within the same field.
                                         $sql_join .= " and qrk_" . $c . ".position>0 and qrk_" . $c . ".position=qrk_" . ($c - 1) . ".position+" . $last_key_offset . " and qrk_" . $c . ".resource_type_field=qrk_" . ($c - 1) . ".resource_type_field";
                                     }
                                 }
                             } else {
                                 if ($omit) {
                                     # Exclude matching resources from query (omit feature)
                                     if ($sql_filter != "") {
                                         $sql_filter .= " and ";
                                     }
                                     $sql_filter .= "r.ref not in (select resource from resource_keyword where keyword='{$keyref}')";
                                     # Filter out resources that do contain the keyword.
                                 }
                             }
                             # Log this
                             if ($stats_logging) {
                                 daily_stat("Keyword usage", $keyref);
                             }
                         }
                     }
                     $skipped_last = false;
                 }
             }
         }
     }
     # Could not match on provided keywords? Attempt to return some suggestions.
     if ($fullmatch == false) {
         if ($suggested == $keywords) {
             # Nothing different to suggest.
             debug("No alternative keywords to suggest.");
             return "";
         } else {
             # Suggest alternative spellings/sound-a-likes
             $suggest = "";
             if (strpos($search, ",") === false) {
                 $suggestjoin = " ";
             } else {
                 $suggestjoin = ", ";
             }
             for ($n = 0; $n < count($suggested); $n++) {
                 if ($suggested[$n] != "") {
                     if ($suggest != "") {
                         $suggest .= $suggestjoin;
                     }
                     $suggest .= $suggested[$n];
                 }
             }
             debug("Suggesting {$suggest}");
             return $suggest;
         }
     }
     hook("additionalsqlfilter");
     hook("parametricsqlfilter", '', array($search));
     # ------ Search filtering: If search_filter is specified on the user group, then we must always apply this filter.
     global $usersearchfilter;
     $sf = explode(";", $usersearchfilter);
     if (strlen($usersearchfilter) > 0) {
         for ($n = 0; $n < count($sf); $n++) {
             $s = explode("=", $sf[$n]);
             if (count($s) != 2) {
                 exit("Search filter is not correctly configured for this user group.");
             }
             # Support for "NOT" matching. Return results only where the specified value or values are NOT set.
             $filterfield = $s[0];
             $filter_not = false;
             if (substr($filterfield, -1) == "!") {
                 $filter_not = true;
                 $filterfield = substr($filterfield, 0, -1);
                 # Strip off the exclamation mark.
             }
             # Support for multiple fields on the left hand side, pipe separated - allows OR matching across multiple fields in a basic way
             $filterfields = explode("|", escape_check($filterfield));
             # Find field(s) - multiple fields can be returned to support several fields with the same name.
             $f = sql_array("select ref value from resource_type_field where name in ('" . join("','", $filterfields) . "')");
             if (count($f) == 0) {
                 exit("Field(s) with short name '" . $filterfield . "' not found in user group search filter.");
             }
             # Find keyword(s)
             $ks = explode("|", strtolower(escape_check($s[1])));
             for ($x = 0; $x < count($ks); $x++) {
                 # Cleanse the string as keywords are stored without special characters
                 $ks[$x] = cleanse_string($ks[$x], true);
                 global $stemming;
                 if ($stemming && function_exists("GetStem")) {
                     $ks[$x] = GetStem($ks[$x]);
                 }
             }
             $modifiedsearchfilter = hook("modifysearchfilter");
             if ($modifiedsearchfilter) {
                 $ks = $modifiedsearchfilter;
             }
             $kw = sql_array("select ref value from keyword where keyword in ('" . join("','", $ks) . "')");
             if (!$filter_not) {
                 # Standard operation ('=' syntax)
                 $sql_join .= " join resource_keyword filter" . $n . " on r.ref=filter" . $n . ".resource and filter" . $n . ".resource_type_field in ('" . join("','", $f) . "') and ((filter" . $n . ".keyword in ('" . join("','", $kw) . "')) ";
                 # Option for custom access to override search filters.
                 # For this resource, if custom access has been granted for the user or group, nullify the search filter for this particular resource effectively selecting "true".
                 global $custom_access_overrides_search_filter;
                 if (!checkperm("v") && !$access_override && $custom_access_overrides_search_filter) {
                     $sql_join .= "or ((rca.access is not null and rca.access<>2) or (rca2.access is not null and rca2.access<>2))";
                 }
                 $sql_join .= ")";
                 if ($search_filter_strict > 1) {
                     $sql_join .= " join resource_data dfilter" . $n . " on r.ref=dfilter" . $n . ".resource and dfilter" . $n . ".resource_type_field in ('" . join("','", $f) . "') and (find_in_set('" . join("', dfilter" . $n . ".value) or find_in_set('", explode("|", escape_check($s[1]))) . "', dfilter" . $n . ".value))";
                 }
             } else {
                 # Inverted NOT operation ('!=' syntax)
                 if ($sql_filter != "") {
                     $sql_filter .= " and ";
                 }
                 $sql_filter .= "((r.ref not in (select resource from resource_keyword where resource_type_field in ('" . join("','", $f) . "') and keyword in ('" . join("','", $kw) . "'))) ";
                 # Filter out resources that do contain the keyword(s)
                 # Option for custom access to override search filters.
                 # For this resource, if custom access has been granted for the user or group, nullify the search filter for this particular resource effectively selecting "true".
                 global $custom_access_overrides_search_filter;
                 if (!checkperm("v") && !$access_override && $custom_access_overrides_search_filter) {
                     $sql_filter .= "or ((rca.access is not null and rca.access<>2) or (rca2.access is not null and rca2.access<>2))";
                 }
                 $sql_filter .= ")";
             }
         }
     }
     $userownfilter = hook("userownfilter");
     if ($userownfilter) {
         $sql_join .= $userownfilter;
     }
     # Handle numeric searches when $config_search_for_number=false, i.e. perform a normal search but include matches for resource ID first
     global $config_search_for_number;
     if (!$config_search_for_number && is_numeric($search)) {
         # Always show exact resource matches first.
         $order_by = "(r.ref='" . $search . "') desc," . $order_by;
     }
     # ---------------------------------------------------------------
     # Keyword union assembly.
     # Use UNIONs for keyword matching instead of the older JOIN technique - much faster
     # Assemble the new join from the stored unions
     # ---------------------------------------------------------------
     if (count($sql_keyword_union) > 0) {
         $sql_join .= " join (\n\t\tselect resource,sum(score) as score,\n\t\t" . join(", ", $sql_keyword_union_aggregation) . " from\n\t\t(" . join(" union ", $sql_keyword_union) . ") as hits group by resource) as h on h.resource=r.ref ";
         if ($sql_filter != "") {
             $sql_filter .= " and ";
         }
         $sql_filter .= join(" and ", $sql_keyword_union_criteria);
         # Use amalgamated resource_keyword hitcounts for scoring (relevance matching based on previous user activity)
         $score = "h.score";
     }
     # Can only search for resources that belong to themes
     if (checkperm("J")) {
         $sql_join = " join collection_resource jcr on jcr.resource=r.ref join collection jc on jcr.collection=jc.ref and length(jc.theme)>0 " . $sql_join;
     }
     # --------------------------------------------------------------------------------
     # Special Searches (start with an exclamation mark)
     # --------------------------------------------------------------------------------
     $special_results = search_special($search, $sql_join, $fetchrows, $sql_prefix, $sql_suffix, $order_by, $orig_order, $select, $sql_filter, $archive, $return_disk_usage);
     if ($special_results !== false) {
         return $special_results;
     }
     # -------------------------------------------------------------------------------------
     # Standard Searches
     # -------------------------------------------------------------------------------------
     # We've reached this far without returning.
     # This must be a standard (non-special) search.
     # Construct and perform the standard search query.
     #$sql="";
     if ($sql_filter != "") {
         if ($sql != "") {
             $sql .= " and ";
         }
         $sql .= $sql_filter;
     }
     # Append custom permissions
     $t .= $sql_join;
     if ($score == "") {
         $score = "r.hit_count";
     }
     # In case score hasn't been set (i.e. empty search)
     global $max_results;
     if ($t2 != "" && $sql != "") {
         $sql = " and " . $sql;
     }
     # Compile final SQL
     # Performance enhancement - set return limit to number of rows required
     if ($search_sql_double_pass_mode && $fetchrows != -1) {
         $max_results = $fetchrows;
     }
     $results_sql = $sql_prefix . "select distinct {$score} score, {$select} from resource r" . $t . "  where {$t2} {$sql} group by r.ref order by {$order_by} limit {$max_results}" . $sql_suffix;
     # Debug
     debug('$results_sql=' . $results_sql);
     # Execute query
     $result = sql_query($results_sql, false, $fetchrows);
     # Performance improvement - perform a second count-only query and pad the result array as necessary
     if ($search_sql_double_pass_mode && count($result) >= $max_results) {
         $count_sql = "select count(distinct r.ref) value from resource r" . $t . "  where {$t2} {$sql}";
         $count = sql_value($count_sql, 0);
         $result = array_pad($result, $count, 0);
     }
     debug("Search found " . count($result) . " results");
     if (count($result) > 0) {
         hook("beforereturnresults", "", array($result, $archive));
         return $result;
     }
     hook('zero_search_results');
     # (temp) - no suggestion for field-specific searching for now - TO DO: modify function below to support this
     if (strpos($search, ":") !== false) {
         return "";
     }
     # All keywords resolved OK, but there were no matches
     # Remove keywords, least used first, until we get results.
     $lsql = "";
     $omitmatch = false;
     for ($n = 0; $n < count($keywords); $n++) {
         if (substr($keywords[$n], 0, 1) == "-") {
             $omitmatch = true;
             $omit = $keywords[$n];
         }
         if ($lsql != "") {
             $lsql .= " or ";
         }
         $lsql .= "keyword='" . escape_check($keywords[$n]) . "'";
     }
     if ($omitmatch) {
         return trim_spaces(str_replace(" " . $omit . " ", " ", " " . join(" ", $keywords) . " "));
     }
     if ($lsql != "") {
         $least = sql_value("select keyword value from keyword where {$lsql} order by hit_count asc limit 1", "");
         return trim_spaces(str_replace(" " . $least . " ", " ", " " . join(" ", $keywords) . " "));
     } else {
         return array();
     }
 }