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 972 to 1150

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 "";
    
// 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");

    
# Keywords searching?
    
$keywords=split_keywords($search);
    if (
strlen($search)==&& !is_numeric($search)) 
        {
        
# A-Z search
        
$sql "AND c.name LIKE ?";
        
$sql_params[] = "s";$sql_params[] = $search "%";
        }
    elseif (
substr($search,0,16)=="collectiontitle:")
        {
        
# A-Z specific title search
        
$newsearch implode(' 'array_diff($keywords, ['collectiontitle']));
        
$newsearch strpos($newsearch,'*')===false '%' trim($newsearch) . '%' str_replace('*''%'trim($newsearch));
        
$sql "AND c.name LIKE ?";
        
$sql_params[] = "s";$sql_params[] = $newsearch;
        }
    else 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,15)!="collectiontitle")
                {
                if (
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;
                    }
                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" $keyrefs[$n];
            
$unionselect .= ", BIT_OR(key" $keyrefs[$n] . "_found) AS key" $keyrefs[$n];
            
$unionsql "SELECT collection ";
            for(
$l=0;$l<count($keyrefs);$l++)
                {
                
$unionsql .= $keyrefs[$l] == $keyrefs[$n] ? ",TRUE" ",FALSE";
                
$unionsql .= " AS key" $keyrefs[$l] . "_found";
                }
            
$unionsql .= " FROM collection_keyword WHERE keyword=" $keyrefs[$n];
            
$keyunions[] = $unionsql;
            
$sql .= " AND key" .  $keyrefs[$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 3rd October 2022 12:35 Europe/London time based on the source file dated 22nd September 2022 11:00 Europe/London time.