Collections functions
General functions
Render functions
Theme permission functions
Resource functions

search_public_collections()

Description

Performs a search for featured collections / public collections.

Parameters

ColumnTypeDefaultDescription
$search string ""
$order_by string "name"
$sort string "ASC"
$exclude_themes boolean true
$include_resources boolean false
$override_group_restrict boolean false
$fetchrows integer -1

Return

array

Location

include/collections_functions.php lines 974 to 1207

Definition

 
function search_public_collections($search=""$order_by="name"$sort="ASC"$exclude_themes=true$include_resources=false$override_group_restrict=false$fetchrows=-1)
    {
    global 
$userref,$public_collections_confine_group,$userref,$usergroup;

    
$keysql "";
    
$sql "";
    
$sql_params = []; 
    
$select_extra "";
    
debug_function_call("search_public_collections"func_get_args());
    
// Validate sort & order_by
    
$sort = (in_array($sort, array("ASC""DESC")) ? $sort "ASC");
    
$valid_order_bys = array("fullname""name""ref""count""type""created");
    
$order_by = (in_array($order_by$valid_order_bys) ? $order_by "name");

    if (
strpos($search,"collectiontitle:") !== false)
        {
        
// This includes a specific title search from the advanced search page.
        
$searchtitlelength  0;
        
$searchtitleval     "";
        
$origsearch         $search;

        
// Force quotes around any collectiontitle: search to support old behaviour
        // i.e. to allow split_keywords() to work
        // collectiontitle:*ser * collection* simpleyear:2022 
        //  - will be changed to -
        // "collectiontitle:*ser * collection*" simpleyear:2022 
        
$searchstart mb_substr($search,0,strpos($search,"collectiontitle:"));
        
$titlepos strpos($search,"collectiontitle:")+16;
        
$searchend mb_substr($search,$titlepos);
        if(
strpos($searchend,":") != false)
            {
            
// Remove any other parts of the search with xxxxx: prefix that relate to other search aspects
            
$searchtitleval=explode(":",$searchend)[0];
            
$searchtitleparts=explode(" ",$searchtitleval);
            if(
count($searchtitleparts) > 1)
                {
                
// The last string relates to the next searched field name/attribute
                
array_pop($searchtitleparts);
                }
            
// Build new string for searched value 
            
$searchtitleval implode(" ",$searchtitleparts);
            
$searchtitlelength strlen($searchtitleval);
            if(
substr($searchtitleval,-1,1) == ",")
                {
                
$searchtitleval substr($searchtitleval,0,-1);
                }
            
// Add quotes 
            
$search $searchstart ' "' "collectiontitle:" $searchtitleval '"';
            
// Append the other search strings
            
$search .= substr($origsearch,$titlepos $searchtitlelength);
            }
        else
            {
            
// nothing to remove
            
$search $searchstart ' "' "collectiontitle:" $searchend '"';
            }
        
debug("New search: " $search);
        }

    
$keywords=split_keywords($search,false,false,false,false,true);
    if (
strlen($search)==&& !is_numeric($search)) 
        {
        
# A-Z search
        
$sql "AND c.name LIKE ?";
        
$sql_params[] = "s";$sql_params[] = $search "%";
        }
    if (
strlen($search)>|| is_numeric($search))
        {
        
$keyrefs=array();
        
$keyunions = array();
        
$unionselect "SELECT kunion.collection";
        for (
$n=0;$n<count($keywords);$n++)
            {
            if(
substr($keywords[$n],0,1)=="\"" && substr($keywords[$n],-1,1)=="\"")
                {
                
$keywords[$n] = substr($keywords[$n],1,-1);
                }

            if (
substr($keywords[$n],0,16)=="collectiontitle:")
                {
                
$newsearch explode(":",$keywords[$n])[1];
                
$newsearch strpos($newsearch,'*')===false '%' trim($newsearch) . '%' str_replace('*''%'trim($newsearch));
                
$sql "AND c.name LIKE ?";
                
$sql_params[] = "s";$sql_params[] = $newsearch;
                }
            elseif (
substr($keywords[$n],0,16)=="collectionowner:")
                {
                
$keywords[$n]=substr($keywords[$n],16);
                
$keyref=$keywords[$n];
                
$sql.=" AND (u.username RLIKE ? OR u.fullname RLIKE ?)";
                
$sql_params[] = "i";$sql_params[] = $keyref;
                
$sql_params[] = "i";$sql_params[] = $keyref;
                }
            elseif (
substr($keywords[$n],0,19)=="collectionownerref:")
                {
                
$keywords[$n]=substr($keywords[$n],19);
                
$keyref=$keywords[$n];
                
$sql.=" AND (c.user=?)";
                
$sql_params[] = "i";$sql_params[] = $keyref;
                }
            elseif (
substr($keywords[$n],0,10)=="basicyear:" || substr($keywords[$n],0,11)=="basicmonth:")
                {
                
$dateparts=explode(":",$keywords[$n]);
                
$yearpart $dateparts[0] == "basicyear" $dateparts[1] :  "____";
                
$monthpart $dateparts[0] == "basicmonth" ?  $dateparts[1] : "__";
                
$sql .= " AND c.created LIKE ?";
                
$sql_params[] = "s";$sql_params[] = $yearpart "-" $monthpart "%";
                }
            else
                {
                if (
substr($keywords[$n],0,19)=="collectionkeywords:"$keywords[$n]=substr($keywords[$n],19);
                
# Support field specific matching - discard the field identifier as not appropriate for collection searches.
                
if (strpos($keywords[$n],":")!==false)
                    {
                    
$keywords[$n]=substr($keywords[$n],strpos($keywords[$n],":")+1);
                    }
                
$keyref=resolve_keyword($keywords[$n],false);
                if (
$keyref !== false)
                    {
                    
$keyrefs[]=$keyref;
                    }
                }
            }

        for(
$n=0;$n<count($keyrefs);$n++)
            {
            
$select_extra .= ", k.key" $n;
            
$unionselect .= ", BIT_OR(key" $n "_found) AS key" .$n;
            
$unionsql "SELECT collection ";
            for(
$l=0;$l<count($keyrefs);$l++)
                {
                
$unionsql .= $l == $n ",TRUE" ",FALSE";
                
$unionsql .= " AS key" $l "_found";
                }
            
$unionsql .= " FROM collection_keyword WHERE keyword=" $keyrefs[$n];
            
$keyunions[] = $unionsql;
            
$sql .= " AND key" .  $n;
            }
        if(
count($keyunions)> 0)
            {
            
$keysql .= " LEFT OUTER JOIN (" $unionselect " FROM (" implode(" UNION "$keyunions) . ") kunion GROUP BY collection) AS k ON c.ref = k.collection";
            }
        }

    
# Restrict to parent, child and sibling groups?
    
if ($public_collections_confine_group && !$override_group_restrict)
        {
        
# Form a list of all applicable groups
        
$groups=array($usergroup); # Start with user's own group
        
$usergroupparams = ["i",$usergroup];
        
$groups=array_merge($groups,ps_array("SELECT ref value FROM usergroup WHERE parent=?",$usergroupparams)); # Children
        
$groups=array_merge($groups,ps_array("SELECT parent value FROM usergroup WHERE ref=?",$usergroupparams)); # Parent
        
$groups=array_merge($groups,ps_array("SELECT ref value FROM usergroup WHERE parent<>0 AND parent=(SELECT parent FROM usergroup WHERE ref=?)",$usergroupparams)); # Siblings (same parent)

        
$sql.=" AND u.usergroup IN (" ps_param_insert(count($groups)) . ")";
        
$sql_params array_merge($sql_paramsps_param_fill($groups,"i"));
        }

    
// Add extra elements to the SELECT statement if needed
    
if($include_resources)
        {
        
$select_extra .= ", COUNT(DISTINCT cr.resource) AS count";
        }

    
// Filter by type (public/featured collections)
    
$public_type_filter_sql "c.`type` = ?";
    
$public_type_filter_sql_params = ["i",COLLECTION_TYPE_PUBLIC];


    if(
$exclude_themes)
        {
        
$featured_type_filter_sql "";
        
$featured_type_filter_sql_params =[];
        }
    else
        {
        
$featured_type_filter_sql "(c.`type` = ?)";
        
$featured_type_filter_sql_params = ["i",COLLECTION_TYPE_FEATURED];
        
$fcf_sql featured_collections_permissions_filter_sql("AND""c.ref");
        if(
is_array($fcf_sql))
            {
            
// Update with the extra condition
            
$featured_type_filter_sql "(c.`type` = ? " $fcf_sql[0] . ")";
            
$featured_type_filter_sql_params array_merge(["i",COLLECTION_TYPE_FEATURED],$fcf_sql[1]);
            }
        }

    if(
$public_type_filter_sql != "" && $featured_type_filter_sql != "")
        {
        
$type_filter_sql "(" $public_type_filter_sql " OR " $featured_type_filter_sql ")";
        
$type_filter_sql_params array_merge($public_type_filter_sql_params,$featured_type_filter_sql_params);
        }
    else
        {
        
$type_filter_sql $public_type_filter_sql $featured_type_filter_sql;
        
$type_filter_sql_params array_merge($public_type_filter_sql_params,$featured_type_filter_sql_params);
        }

    
$where_clause_osql 'col.`type` = ' COLLECTION_TYPE_PUBLIC;
    if(
$featured_type_filter_sql !== '')
        {
        
$where_clause_osql .= ' OR (col.`type` = ' COLLECTION_TYPE_FEATURED ' AND col.is_featured_collection_category = false)';
        }

    
$main_sql sprintf(
        
"SELECT *
           FROM (
                         SELECT DISTINCT c.*,
                                u.username,
                                u.fullname,
                                IF(c.`type` = %s AND COUNT(DISTINCT cc.ref)>0, true, false) AS is_featured_collection_category
                                %s
                           FROM collection AS c
                LEFT OUTER JOIN collection AS cc ON c.ref = cc.parent
                LEFT OUTER JOIN collection_resource AS cr ON c.ref = cr.collection
                LEFT OUTER JOIN user AS u ON c.user = u.ref
                          %s # keysql
                          WHERE %s # type_filter_sql
                            %s
                       GROUP BY c.ref
                       ORDER BY %s
           ) AS col
          WHERE %s"
,
        
COLLECTION_TYPE_FEATURED,
        
$select_extra,
        
$keysql,
        
$type_filter_sql,
        
$sql# extra filters
        
"{$order_by} {$sort}",
        
$where_clause_osql
    
);

    return 
ps_query($main_sqlarray_merge($type_filter_sql_params,$sql_params),''$fetchrows);
    }

This article was last updated 31st January 2023 10:35 Europe/London time based on the source file dated 6th January 2023 19:05 Europe/London time.