function children_id_to_array(&$child_array, $table, $parent)
{
global $db;
if (!is_numeric($parent)) {
die;
}
// retrieve all children of $parent
$sql = 'SELECT category__auto_id FROM ' . $table . ' WHERE category_parent="' . $parent . '" and category__auto_id <> 0;';
$result = $db->get_results($sql);
if ($result) {
foreach ($result as $row) {
$child_array[] = $row->category__auto_id;
// call this function again to display this child's children
children_id_to_array($child_array, $table, $row->category__auto_id);
}
}
}
function children_id_to_array($array, $table, $parent, $level)
{
global $array;
// retrieve all children of $parent
$sql = 'SELECT category__auto_id FROM ' . $table . ' WHERE category_parent="' . $parent . '" and category__auto_id <> 0;';
//echo $sql;
$result = mysql_query($sql);
//if(count($array) > 50){print_r($array);die();};
// display each child
while ($row = mysql_fetch_array($result)) {
// indent and display the title of this child
//echo str_repeat(' ',$level).$row['category__auto_id']."\n";
$array[] = $row['category__auto_id'];
// call this function again to display this
// child's children
children_id_to_array($array, $table, $row['category__auto_id'], $level + 1);
}
}
}
}
if ($action == "move_below") {
$id = utf8_substr(sanitize($_REQUEST['id_to_move'], 3), 9, 100);
$move_id = utf8_substr(sanitize($_REQUEST['movebelow_id'], 3), 6, 100);
if (!is_numeric($id)) {
die;
}
if (!is_numeric($move_id)) {
die;
}
if ($id == $move_id) {
header("Location: admin_categories.php");
}
$array = "";
children_id_to_array($array, table_categories, $id);
if (is_array($array)) {
if (!in_array($move_id, $array)) {
$sql = "Select * from " . table_categories . " where category__auto_id=" . $move_id . ";";
$results = $db->get_row($sql);
$move_sort = $results->category_order;
$sql = "update " . table_categories . " set category_parent = " . $results->category_parent . ", category_order = " . ($move_sort + 1) . " where category__auto_id=" . $id . ";";
$db->query($sql);
rebuild_the_tree();
header("Location: admin_categories.php");
} else {
die('You cannot move a category into it\'s own subcategory. Click <a href = "admin_categories.php">here</a> to reload.');
}
} else {
$sql = "Select * from " . table_categories . " where category__auto_id=" . $move_id . ";";
$results = $db->get_row($sql);
function group_shared($requestID, $catId, $flag = 0)
{
global $db, $main_smarty, $the_template, $page_size, $cached_links;
if (!is_numeric($requestID)) {
die;
}
$link = new Link();
$group_shared_display = "";
if ($catId) {
$child_cats = '';
// do we also search the subcategories?
if (Independent_Subcategories == true) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $catId);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$child_cat_sql .= ' OR `link_category` = ' . $child_cat_id . ' ';
if (Multiple_Categories) {
$child_cat_sql .= ' OR ac_cat_id = ' . $child_cat_id . ' ';
}
}
}
}
if (Multiple_Categories) {
$child_cat_sql .= " OR ac_cat_id = {$catId} ";
}
$from_where .= " AND (link_category={$catId} " . $child_cat_sql . ")";
}
$offset = (get_current_page() - 1) * $page_size;
if ($flag == 1) {
$sql = "SELECT SQL_CALC_FOUND_ROWS b.* FROM " . table_group_shared . " a\r\n\t\t\t\t LEFT JOIN " . table_links . " b ON link_id=share_link_id\r\n\t\t\t\t WHERE share_group_id = {$requestID} AND !ISNULL(link_id) {$from_where} \r\n\t\t\t\t GROUP BY link_id\r\n\t\t\t\t ORDER BY link_published_date DESC, link_date DESC ";
} else {
$sql = "SELECT SQL_CALC_FOUND_ROWS b.* FROM " . table_group_shared . " a\r\n\t\t\t\t LEFT JOIN " . table_links . " b ON link_id=share_link_id\r\n\t\t\t\t WHERE share_group_id = {$requestID} AND !ISNULL(link_id) {$from_where} \r\n\t\t\t\t GROUP BY link_id\r\n\t\t\t\t ORDER BY link_published_date DESC, link_date DESC LIMIT {$offset}, {$page_size}";
}
// Search on additional categories
if ($catId && Multiple_Categories) {
$sql = str_replace("WHERE", " LEFT JOIN " . table_additional_categories . " ON ac_link_id=link_id WHERE", $sql);
}
$links = $db->get_results($sql);
$rows = $db->get_var("SELECT FOUND_ROWS()");
if ($flag == 1) {
return $rows;
}
if ($links) {
foreach ($links as $dblink) {
$link->id = $dblink->link_id;
$cached_links[$dblink->link_id] = $dblink;
$link->read();
$group_shared_display .= $link->print_summary('summary', true);
}
}
$main_smarty->assign('group_shared_display', $group_shared_display);
//for auto scrolling
if (Auto_scroll == 2 || Auto_scroll == 3) {
$main_smarty->assign("scrollpageSize", $page_size);
$main_smarty->assign('total_row', $rows);
if ($catId) {
$main_smarty->assign('catID', $catId);
}
$main_smarty->assign('total_row', $rows);
} else {
$main_smarty->assign('group_story_pagination', do_pages($rows, $page_size, 'group_story', true));
}
}
function show()
{
// CDPDF old = global $db, $dblang, $URLMethod, $tags_words_limit, $tags_min_pts, $tags_max_pts;
global $db, $dblang, $URLMethod, $tags_words_limit, $tags_min_pts, $tags_max_pts, $thecat;
// if we didnt set a word limit, use the default set in the config.php
if ($this->word_limit == NULL) {
$this->word_limit = $tags_words_limit;
}
// if we didnt set the minimum font points, use the default set in the config.php
if ($this->min_points == NULL) {
$this->min_points = $tags_min_pts;
}
// if we didnt set the maximum font points, use the default set in the config.php
if ($this->max_points == NULL) {
$this->max_points = $tags_max_pts;
}
// see if we clicked on a link to filter to a specific time range
if (($from = check_integer('range')) >= 0 && $from < count($this->range_values) && $this->range_values[$from] > 0) {
$from_time = time() - $this->range_values[$from];
$from_where = "FROM " . table_tags . ", " . table_links . " WHERE tag_lang='{$dblang}' and tag_date > FROM_UNIXTIME({$from_time}) and link_id = tag_link_id and ";
$time_query = "&from={$from_time}";
$this->smarty_variable->assign('time_query', $time_query);
} else {
$from_where = "FROM " . table_tags . ", " . table_links . " WHERE tag_lang='{$dblang}' and link_id = tag_link_id and ";
$cache_possible = 1;
}
if ($this->filterTo == 'all') {
$from_where .= " (link_status='published' OR link_status='new') ";
$cache_possible++;
}
if ($this->filterTo == 'new') {
$from_where .= " link_status='new' ";
}
if ($this->filterTo == 'published') {
$from_where .= " link_status='published' ";
}
if (is_numeric($this->filterCategory) && $this->filterCategory > 0) {
$catId = $this->filterCategory;
$child_cats = '';
// do we also search the subcategories?
if (Independent_Subcategories == true) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $catId);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$child_cat_sql .= ' OR `link_category` = ' . $child_cat_id . ' ';
if (Multiple_Categories) {
$child_cat_sql .= ' OR ac_cat_id = ' . $child_cat_id . ' ';
}
}
}
}
if (Multiple_Categories) {
$child_cat_sql .= " OR ac_cat_id = {$catId} ";
}
$from_where .= " AND (link_category={$catId} " . $child_cat_sql . ")";
$cache_possible = 0;
}
//CDPDF
if (isset($_REQUEST['category'])) {
$catId = $db->get_var("SELECT category_id from " . table_categories . " where category_safe_name = '" . $db->escape($_REQUEST['category']) . "';");
$category_name = $db->get_var("SELECT category_name from " . table_categories . " where category_safe_name = '" . $db->escape($_REQUEST['category']) . "';");
$this->smarty_variable->assign('category_name', $category_name);
//$catId = get_category_id($this->category);
if (isset($catId)) {
$child_cats = '';
// do we also search the subcategories?
if (!Independent_Subcategories) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $catId);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$child_cat_sql .= ' OR `link_category` = ' . $child_cat_id . ' ';
if (Multiple_Categories) {
$child_cat_sql .= ' OR ac_cat_id = ' . $child_cat_id . ' ';
}
}
}
}
if (Multiple_Categories) {
$child_cat_sql .= " OR ac_cat_id = {$catId} ";
}
$cache_possible = 0;
$from_where .= " AND (link_category={$catId} " . $child_cat_sql . ")";
// Search on additional categories
if (Multiple_Categories) {
$from_where = str_replace("WHERE", " LEFT JOIN " . table_additional_categories . " ON ac_link_id=link_id WHERE", $from_where);
}
}
}
//CDPDF
$from_where .= " GROUP BY tag_words";
/*CDPDF : we calculate the coefficient with the following queries
$max = max($db->get_var("select count(*) as words $from_where order by words desc limit 1"), 2);
$coef = ($this->max_points - $this->min_points)/($max-1);
CDPDF */
if ($cache_possible == 2) {
$sql = "select * FROM " . table_tag_cache . " limit {$this->word_limit}";
$res = $db->get_results($sql);
} else {
$sql = "select tag_words, count(DISTINCT link_id) as count {$from_where} order by count desc limit {$this->word_limit}";
//echo $sql;
$res = $db->get_results($sql);
}
if ($res) {
foreach ($res as $item) {
//echo $item->tag_words;
$words[$item->tag_words] = $item->count;
$tagcount[] = $item->count;
}
//CDPDF modification
$max = max($tagcount);
// DB 12/10/08
if ($max != 1) {
$coef = ($this->max_points - $this->min_points) / ($max - 1);
} else {
$coef = 0;
}
/////
//cdpdf mofiification
ksort($words);
$tag_number = array();
$tag_name = array();
$tag_count = array();
$tag_size = array();
$tag_url = array();
$tagnumber = 0;
foreach (array_keys($words) as $theword) {
$tag_number[$tagnumber] = $tagnumber;
$tag_name[$tagnumber] = $theword;
$tag_count[$tagnumber] = $words[$theword];
$tag_size[$tagnumber] = $tags_min_pts + ($tag_count[$tagnumber] - 1) * $coef;
if (isset($time_query)) {
$tag_url[$tagnumber] = getmyurl('tag2', urlencode($tag_name[$tagnumber]), $from_time);
} else {
$tag_url[$tagnumber] = getmyurl('tag', urlencode($tag_name[$tagnumber]));
}
$tagnumber = $tagnumber + 1;
}
}
// Set the smarty variables
if (isset($words)) {
$this->smarty_variable->assign('words', $words);
}
if (isset($tag_number)) {
$this->smarty_variable->assign('tag_number', $tag_number);
} else {
$this->smarty_variable->assign('tag_number', 0);
}
if (isset($tag_name)) {
$this->smarty_variable->assign('tag_name', $tag_name);
}
if (isset($tag_count)) {
$this->smarty_variable->assign('tag_count', $tag_count);
}
if (isset($tag_size)) {
$this->smarty_variable->assign('tag_size', $tag_size);
}
if (isset($tag_url)) {
$tag_url = str_replace(" ", "+", $tag_url);
// Steef 2k7-07 tag search fix
$this->smarty_variable->assign('tag_url', $tag_url);
}
$this->smarty_variable->assign('tags_words_limit', $this->word_limit);
$this->smarty_variable->assign('tags_min_pts', $this->min_points);
$this->smarty_variable->assign('tags_max_pts', $this->max_points);
$this->smarty_variable->assign('tags_largest_tag', $max);
$this->smarty_variable->assign('tags_coef', $coef);
}
function doSearch()
{
global $db, $current_user, $main_smarty;
$search_clause = $this->get_search_clause();
// set smarty variables
if (isset($this->searchTerm)) {
$main_smarty->assign('search', $this->searchTerm);
$main_smarty->assign('searchtext', htmlspecialchars($this->searchTerm));
} else {
$main_smarty->assign('searchtext', '');
}
$from_where = "FROM " . $this->searchTable . " WHERE ";
if ($this->filterToStatus == 'all') {
$from_where .= " link_status!='discard' ";
}
if ($this->filterToStatus == 'queued') {
$from_where .= " link_status='queued' ";
}
if ($this->filterToStatus == 'discard') {
$from_where .= " link_status='discard' ";
}
if ($this->filterToStatus == 'published') {
$from_where .= " link_status='published' ";
}
if ($this->filterToStatus == 'popular') {
$from_where .= " link_status='published' ";
}
if ($this->url != '') {
if ($this->filterToStatus != '') {
$from_where .= ' AND ';
}
$from_where .= " link_url='{$this->url}' ";
}
if ($this->filterToTimeFrame == 'today') {
$tsdt = date('Ymd000000', strtotime("now"));
$fsdt = date('Ymd235959', strtotime("now"));
$from_where .= " AND (link_published_date >= {$tsdt} AND link_published_date <= {$fsdt}) ";
}
if ($this->filterToTimeFrame == 'yesterday') {
$tsdt = date('Ymd000000', strtotime("-1 day"));
$fsdt = date('Ymd235959', strtotime("-1 day"));
$from_where .= " AND (link_published_date >= {$tsdt} AND link_published_date <= {$fsdt}) ";
}
if ($this->filterToTimeFrame == 'week') {
$wknum = date('w', strtotime("now"));
if ($wknum > 0) {
$tsdt = date('Ymd000000', strtotime("-{$wknum} day"));
$fsdt = date('Ymd235959', strtotime("now"));
} else {
$tsdt = date('Ymd000000', strtotime("now"));
$fsdt = date('Ymd235959', strtotime("now"));
}
$from_where .= " AND (link_published_date >= {$tsdt} AND link_published_date <= {$fsdt}) ";
}
if ($this->filterToTimeFrame == 'month') {
$tsdt = date('Ym01000000', strtotime("now"));
$fsdt = date('Ym' . date('t', strtotime("now")) . '235959', strtotime("now"));
$from_where .= " AND (link_published_date >= {$tsdt} AND link_published_date <= {$fsdt}) ";
}
if ($this->filterToTimeFrame == 'year') {
$tsdt = date('Y0101000000', strtotime("now"));
$fsdt = date('Y1231235959', strtotime("now"));
$from_where .= " AND (link_published_date >= {$tsdt} AND link_published_date <= {$fsdt}) ";
}
/////sorojit: for user selected category display
if ($_COOKIE['mnm_user']) {
$user_login = $db->escape(sanitize($_COOKIE['mnm_user'], 3));
$sqlGeticategory = $db->get_var("SELECT user_categories from " . table_users . " where user_login = '******';");
$from_where .= " AND link_category in ({$sqlGeticategory})";
}
//should we filter to just this category?
if (isset($this->category)) {
//$catId = $db->get_var("SELECT category_id from " . table_categories . " where category_name = '" . $this->category . "';");
$catId = get_category_id($this->category);
if (isset($catId)) {
$child_cats = '';
// do we also search the subcategories?
if ($this->search_subcats == true) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $catId);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$child_cat_sql .= ' OR `link_category` = ' . $child_cat_id . ' ';
}
}
}
$from_where .= " AND (link_category={$catId} " . $child_cat_sql . ")";
}
}
if (isset($this->orderBy)) {
if (strpos($this->orderBy, "ORDER BY") != 1) {
$this->orderBy = "ORDER BY " . $this->orderBy;
}
}
// always check groups (to hide private groups)
$from_where = str_replace("WHERE", " LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id = " . table_groups . ".group_id WHERE", $from_where);
$groups = $db->get_results("SELECT * FROM " . table_group_member . " WHERE member_user_id = {$current_user->user_id} and member_status = 'active'");
if ($groups) {
$group_ids = array();
foreach ($groups as $group) {
$group_ids[] = $group->member_group_id;
}
$group_list = join(",", $group_ids);
$from_where .= " AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list})) ";
} else {
$group_list = '';
$from_where .= " AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
}
if ($this->searchTerm == "") {
// like when on the index or upcoming pages.
$this->sql = "SELECT link_id {$from_where} {$search_clause} {$this->orderBy} LIMIT {$this->offset},{$this->pagesize}";
} else {
$this->sql = "SELECT link_id, link_date, link_published_date {$from_where} {$search_clause} ";
}
############################################################################################################# START CUSTOM CODE advsearch 1.0
if ($this->adv && $this->searchTerm != "") {
$from_where = table_links;
$search_clause = 'WHERE ';
$search_params = array();
$search_AND_params = array();
$query = "SELECT " . table_links . ".link_id AS link_id, " . table_links . ".link_date AS link_date, " . table_links . ".link_published_date AS link_published_date FROM ";
// always check groups (to hide private groups)
$from_where .= " LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id = " . table_groups . ".group_id ";
if ($group_list) {
$search_AND_params[] = " (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list})) ";
} else {
$search_AND_params[] = " (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
}
//check if it is a literal search
$buffKeyword = $this->searchTerm;
$keywords = array();
if (substr($this->searchTerm, 1, 1) == '"' && substr($this->searchTerm, strlen($this->searchTerm) - 1, 1) == '"') {
$literal = true;
$addparam = ' COLLATE latin1_general_cs ';
$this->searchTerm = str_replace('\\"', '', $this->searchTerm);
$keywords[] = $this->searchTerm;
} else {
$keywords = explode(' ', $this->searchTerm);
}
$bufferOrig = $this->searchTerm;
//search category
if ($this->s_cat != 0) {
$search_AND_params[] = " " . table_links . ".link_category = " . $this->s_cat . " ";
}
//search tags
if ($this->s_tags != 0) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " " . table_links . ".link_tags {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
$this->searchTerm = $bufferOrig;
}
//search links
if ($this->s_story != 0) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
if ($this->s_story == 1) {
$search_params[] = " " . table_links . ".link_title {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_story == 2) {
$search_params[] = " " . table_links . ".link_content {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_story == 3) {
$search_params[] = " " . table_links . ".link_title {$addparam} LIKE '%" . $this->searchTerm . "%' ";
$search_params[] = " " . table_links . ".link_content {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
}
$this->searchTerm = $bufferOrig;
}
//search author
if ($this->s_user != 0) {
$from_where .= " INNER JOIN " . table_users . " ON " . table_links . ".link_author = " . table_users . ".user_id ";
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " " . table_users . ".user_login {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
$this->searchTerm = $bufferOrig;
}
//search group
if ($this->s_group != 0) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
if ($this->s_group == 1) {
$search_params[] = " " . table_groups . ".group_name {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_group == 2) {
$search_params[] = " " . table_groups . ".group_description {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_group == 3) {
$search_params[] = " " . table_groups . ".group_name {$addparam} LIKE '%" . $this->searchTerm . "%' ";
$search_params[] = " " . table_groups . ".group_description {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
}
$this->searchTerm = $bufferOrig;
}
//search comments
if ($this->s_comments != 0) {
$from_where .= " LEFT JOIN " . table_comments . " ON " . table_links . ".link_id = " . table_comments . ".comment_link_id ";
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " " . table_comments . ".comment_content {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
$this->searchTerm = $bufferOrig;
}
$search_clause = ' WHERE (' . implode(' OR ', $search_params) . ' ) ';
if (sizeof($search_AND_params) > 0) {
$search_clause .= ' AND (' . implode(' AND ', $search_AND_params) . ' ) ';
}
$this->sql = $query . ' ' . $from_where . ' ' . $search_clause . " AND " . table_links . ".link_status <> 'discard' ";
$this->searchTerm = $buffKeyword;
}
// echo $this->sql;
############################################################################################################# END CUSTOM CODE advsearch 1.0
// if this query changes be sure to make sure to update link_summary
// just look for $linksum_count near the top
$this->countsql = "SELECT count(*) {$from_where} {$search_clause}";
return;
}
case 'saved':
$title = $main_smarty->get_config_vars("PLIGG_Visual_RSS_Saved");
$user->username = $login;
$order_field = 'saved_id';
$link_date = "date";
$from_where = "FROM " . table_links . "\r\n\t\t\t\t\t\tLEFT JOIN " . table_saved_links . " ON saved_link_id=link_id\r\n\t\t\t\t\t\tLEFT JOIN " . table_users . " ON link_author=user_id \r\n\t\t\t\t\t\tWHERE saved_user_id={$user->id} AND (link_status='published' OR link_status='queued') ";
break;
}
$cat = isset($_GET['category']) && is_numeric($_GET['category']) ? $_GET['category'] : 0;
if ($cat > 0) {
$child_cats = '';
// do we also search the subcategories?
if (!Independent_Subcategories) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $cat);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$child_cat_sql .= ' OR `link_category` = ' . $child_cat_id . ' ';
if (Multiple_Categories) {
$child_cat_sql .= ' OR ac_cat_id = ' . $child_cat_id . ' ';
}
}
}
}
if (Multiple_Categories) {
$from_where = str_replace("WHERE", " LEFT JOIN " . table_additional_categories . " ON ac_link_id=link_id WHERE", $from_where);
$child_cat_sql .= " OR ac_cat_id = {$cat} ";
}
$from_where .= " AND (link_category={$cat} " . $child_cat_sql . ")";
function gen_query_forCatId($catId)
{
if ($catId) {
$child_cats = '';
// do we also search the subcategories?
if (Independent_Subcategories == true) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $catId);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$child_cat_sql .= ' OR `link_category` = ' . $child_cat_id . ' ';
if (Multiple_Categories) {
$child_cat_sql .= ' OR ac_cat_id = ' . $child_cat_id . ' ';
}
}
}
}
if (Multiple_Categories) {
$child_cat_sql .= " OR ac_cat_id = {$catId} ";
}
$from_where = " AND (link_category={$catId} " . $child_cat_sql . ")";
}
return $from_where;
}
function doSearch()
{
global $db, $current_user, $main_smarty;
$search_clause = $this->get_search_clause();
// set smarty variables
if (isset($this->searchTerm)) {
$main_smarty->assign('search', $this->searchTerm);
$main_smarty->assign('searchtext', htmlspecialchars($this->searchTerm));
} else {
$main_smarty->assign('searchtext', '');
}
$from_where = "FROM " . $this->searchTable . " WHERE ";
if ($this->filterToStatus == 'all') {
$from_where .= " link_status IN ('published','queued') ";
}
if ($this->filterToStatus == 'queued') {
$from_where .= " link_status='queued' ";
}
if ($this->filterToStatus == 'discard') {
$from_where .= " link_status='discard' ";
}
if ($this->filterToStatus == 'published') {
$from_where .= " link_status='published' ";
}
if ($this->filterToStatus == 'popular') {
$from_where .= " link_status='published' ";
}
if ($this->url != '') {
if ($this->filterToStatus != '') {
$from_where .= ' AND ';
}
$from_where .= " link_url LIKE '%{$this->url}%' ";
}
if ($this->filterToTimeFrame == 'today') {
$from_where .= " AND link_date > DATE_SUB(NOW(),INTERVAL 1 DAY) ";
} elseif ($this->filterToTimeFrame == 'yesterday') {
$from_where .= " AND link_date BETWEEN DATE_SUB(NOW(),INTERVAL 2 DAY) AND DATE_SUB(NOW(),INTERVAL 1 DAY) ";
} elseif ($this->filterToTimeFrame == 'week') {
$from_where .= " AND link_date > DATE_SUB(NOW(),INTERVAL 7 DAY) ";
} elseif ($this->filterToTimeFrame == 'month') {
$from_where .= " AND link_date > DATE_SUB(NOW(),INTERVAL 1 MONTH) ";
} elseif ($this->filterToTimeFrame == 'year') {
$from_where .= " AND link_date > DATE_SUB(NOW(),INTERVAL 1 YEAR) ";
}
/////sorojit: for user selected category display
if ($_COOKIE['mnm_user']) {
$user_login = $db->escape(sanitize($_COOKIE['mnm_user'], 3));
$sqlGeticategory = $db->get_var("SELECT user_categories from " . table_users . " where user_login = '******';");
if ($sqlGeticategory) {
$from_where .= " AND link_category NOT IN ({$sqlGeticategory})";
if (Multiple_Categories) {
$from_where .= " AND ac_cat_id NOT IN ({$sqlGeticategory})";
}
}
}
//should we filter to just this category?
if (isset($this->category)) {
//$catId = $db->get_var("SELECT category_id from " . table_categories . " where category_name = '" . $this->category . "';");
// $catId = get_category_id($this->category);
$catId = $this->category;
if ($catId) {
$child_cats = '';
// do we also search the subcategories?
if (!Independent_Subcategories) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $catId);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$child_cat_sql .= ' OR `link_category` = ' . $child_cat_id . ' ';
if (Multiple_Categories) {
$child_cat_sql .= ' OR ac_cat_id = ' . $child_cat_id . ' ';
}
}
}
}
if (Multiple_Categories) {
$child_cat_sql .= " OR ac_cat_id = {$catId} ";
}
$from_where .= " AND (link_category={$catId} " . $child_cat_sql . ")";
}
}
if (isset($this->orderBy)) {
if (strpos($this->orderBy, "ORDER BY") != 1) {
$this->orderBy = " ORDER BY " . $this->orderBy;
}
}
// always check groups (to hide private groups)
$from_where = str_replace("WHERE", " LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id = " . table_groups . ".group_id WHERE", $from_where);
if (Voting_Method == 2) {
$from_where = str_replace("WHERE", " LEFT JOIN " . table_votes . " ON vote_type='links' AND vote_link_id=link_id AND vote_value>0 WHERE", $from_where);
}
// Search on additional categories
if (Multiple_Categories) {
$from_where = str_replace("WHERE", " LEFT JOIN " . table_additional_categories . " ON ac_link_id=link_id WHERE", $from_where);
}
$groups = $db->get_results("SELECT * FROM " . table_group_member . " WHERE member_user_id = {$current_user->user_id} and member_status = 'active'");
if ($groups) {
$group_ids = array();
foreach ($groups as $group) {
$group_ids[] = $group->member_group_id;
}
$group_list = join(",", $group_ids);
$from_where .= " AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list})) ";
} else {
$group_list = '';
$from_where .= " AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
}
if ($this->searchTerm == "" && $this->url == "") {
// like when on the index or upcoming pages.
$this->sql = "SELECT link_id {$from_where} {$search_clause} GROUP BY link_id {$this->orderBy} LIMIT {$this->offset},{$this->pagesize}";
} else {
$this->sql = "SELECT link_id, link_date, link_published_date {$from_where} {$search_clause}";
}
###### START Advanced Search ######
if ($this->adv) {
$from_where = table_links;
$search_clause = 'WHERE ';
$search_params = array();
$search_AND_params = array();
$query = "SELECT " . table_links . ".link_id AS link_id, " . table_links . ".link_date AS link_date, " . table_links . ".link_published_date AS link_published_date FROM ";
// always check groups (to hide private groups)
$from_where .= " LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id = " . table_groups . ".group_id ";
if ($group_list) {
$search_AND_params[] = " (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list})) ";
} else {
$search_AND_params[] = " (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
}
//check if it is a literal search
$buffKeyword = $this->searchTerm;
$keywords = array();
if (substr($this->searchTerm, 1, 1) == '"' && substr($this->searchTerm, strlen($this->searchTerm) - 1, 1) == '"') {
$literal = true;
$addparam = ' COLLATE latin1_general_cs ';
$this->searchTerm = str_replace('\\"', '', $this->searchTerm);
$keywords[] = $this->searchTerm;
} else {
$keywords = explode(' ', $this->searchTerm);
}
$bufferOrig = $this->searchTerm;
//search category
if ($this->s_cat != 0) {
if (Multiple_Categories) {
$mult_sql = " OR ac_cat_id = '" . $db->escape($this->s_cat) . "'";
}
$search_AND_params[] = "( " . table_links . ".link_category = '" . $db->escape($this->s_cat) . "' {$mult_sql})";
}
//search tags
if ($this->s_tags != 0 && $this->searchTerm) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " " . table_links . ".link_tags {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
$this->searchTerm = $bufferOrig;
}
//search links
if ($this->s_story != 0 && $this->searchTerm) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
if ($this->s_story == 1) {
$search_params[] = " " . table_links . ".link_title {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_story == 2) {
$search_params[] = " " . table_links . ".link_content {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_story == 3) {
$search_params[] .= " " . table_links . ".link_title {$addparam} LIKE '%" . $this->searchTerm . "%' ";
$search_params[] .= " " . table_links . ".link_content {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
}
$this->searchTerm = $bufferOrig;
}
//search author
if ($this->s_user != 0 && $this->searchTerm) {
$from_where .= " INNER JOIN " . table_users . " ON " . table_links . ".link_author = " . table_users . ".user_id ";
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " " . table_users . ".user_login {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
$this->searchTerm = $bufferOrig;
}
//search group
if ($this->s_group != 0 && $this->searchTerm) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
if ($this->s_group == 1) {
$search_params[] = " " . table_groups . ".group_name {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_group == 2) {
$search_params[] = " " . table_groups . ".group_description {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_group == 3) {
$search_params[] = " " . table_groups . ".group_name {$addparam} LIKE '%" . $this->searchTerm . "%' ";
$search_params[] = " " . table_groups . ".group_description {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
}
$this->searchTerm = $bufferOrig;
}
//search comments
if ($this->s_comments != 0 && $this->searchTerm) {
$from_where .= " LEFT JOIN " . table_comments . " ON " . table_links . ".link_id = " . table_comments . ".comment_link_id ";
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " (" . table_comments . ".comment_content {$addparam} LIKE '%" . $this->searchTerm . "%' AND comment_status='published')";
}
$this->searchTerm = $bufferOrig;
}
//search by date
if ($this->s_date) {
$this->s_date = date('Y-m-d', strtotime($this->s_date));
# $from_where .= " WHERE DATE(link_date)='{$this->s_date}' ";
$search_AND_params[] = " DATE(" . table_links . ".link_date)='{$this->s_date}' ";
# $this->searchTerm = $bufferOrig;
}
if (Voting_Method == 2) {
$from_where .= " LEFT JOIN " . table_votes . " ON vote_type='links' AND vote_link_id=link_id AND vote_value>0";
}
// Search on additional categories
if (Multiple_Categories) {
$from_where .= " LEFT JOIN " . table_additional_categories . " ON ac_link_id=link_id";
}
if ($this->status != '' && $this->status != 'all') {
$search_params[] = " " . table_links . ".link_status = '{$this->status}' ";
}
if (sizeof($search_params)) {
$search_clause = '(' . implode(' OR ', $search_params) . ' ) ';
} else {
$search_clause = '1';
}
if (sizeof($search_AND_params) > 0) {
$search_clause .= ' AND (' . implode(' AND ', $search_AND_params) . ' ) ';
}
$this->sql = $query . ' ' . $from_where . ' WHERE ' . $search_clause . " AND " . table_links . ".link_status IN ('published','queued') ";
$this->searchTerm = $buffKeyword;
}
#echo $this->sql."<br><br>";
###### END Advanced Search ######
// if this query changes be sure to make sure to update link_summary
// just look for $linksum_count near the top
$this->countsql = "SELECT count(DISTINCT link_id) {$from_where} {$search_clause} ";
return;
}
function doSearch($limit)
{
global $db, $current_user, $main_smarty;
$search_clause = $this->get_search_clause();
// set smarty variables
if (isset($this->searchTerm)) {
$main_smarty->assign('search', $this->searchTerm);
$main_smarty->assign('searchtext', htmlspecialchars($this->searchTerm));
} else {
$main_smarty->assign('searchtext', '');
}
$from_where = "FROM " . $this->searchTable . " WHERE ";
if ($this->filterToStatus == 'all') {
$from_where .= " link_status IN ('published','new') ";
}
if ($this->filterToStatus == 'new') {
$from_where .= " link_status='new' ";
}
if ($this->filterToStatus == 'discard') {
$from_where .= " link_status='discard' ";
}
if ($this->filterToStatus == 'published') {
$from_where .= " link_status='published' ";
}
if ($this->filterToStatus == 'popular') {
$from_where .= " link_status='published' ";
}
if ($this->url != '') {
if ($this->filterToStatus != '') {
$from_where .= ' AND ';
}
$from_where .= " link_url LIKE '%{$this->url}%' ";
}
// Sort filters for published and new pages
if ($this->filterToStatus == 'published') {
if ($this->filterToTimeFrame == 'today') {
$from_where .= " AND link_published_date > DATE_SUB(NOW(),INTERVAL 1 DAY) ";
} elseif ($this->filterToTimeFrame == 'yesterday') {
$from_where .= " AND link_published_date BETWEEN DATE_SUB(NOW(),INTERVAL 2 DAY) AND DATE_SUB(NOW(),INTERVAL 1 DAY) ";
} elseif ($this->filterToTimeFrame == 'week') {
$from_where .= " AND link_published_date > DATE_SUB(NOW(),INTERVAL 7 DAY) ";
} elseif ($this->filterToTimeFrame == 'month') {
$from_where .= " AND link_published_date > DATE_SUB(NOW(),INTERVAL 1 MONTH) ";
} elseif ($this->filterToTimeFrame == 'year') {
$from_where .= " AND link_published_date > DATE_SUB(NOW(),INTERVAL 1 YEAR) ";
} else {
if ($this->filterToTimeFrame == 'upvoted') {
$this->searchTerm = "upvoted";
} else {
if ($this->filterToTimeFrame == 'downvoted') {
$this->searchTerm = "downvoted";
} else {
if ($this->filterToTimeFrame == 'commented') {
$this->searchTerm = "commented";
}
}
}
}
} else {
if ($this->filterToTimeFrame == 'today') {
$from_where .= " AND link_date > DATE_SUB(NOW(),INTERVAL 1 DAY) ";
} elseif ($this->filterToTimeFrame == 'yesterday') {
$from_where .= " AND link_date BETWEEN DATE_SUB(NOW(),INTERVAL 2 DAY) AND DATE_SUB(NOW(),INTERVAL 1 DAY) ";
} elseif ($this->filterToTimeFrame == 'week') {
$from_where .= " AND link_date > DATE_SUB(NOW(),INTERVAL 7 DAY) ";
} elseif ($this->filterToTimeFrame == 'month') {
$from_where .= " AND link_date > DATE_SUB(NOW(),INTERVAL 1 MONTH) ";
} elseif ($this->filterToTimeFrame == 'year') {
$from_where .= " AND link_date > DATE_SUB(NOW(),INTERVAL 1 YEAR) ";
} else {
if ($this->filterToTimeFrame == 'upvoted') {
$this->searchTerm = "upvoted";
} else {
if ($this->filterToTimeFrame == 'downvoted') {
$this->searchTerm = "downvoted";
} else {
if ($this->filterToTimeFrame == 'commented') {
$this->searchTerm = "commented";
}
}
}
}
}
/////sorojit: for user selected category display
if ($_COOKIE['mnm_user']) {
$user_login = $db->escape(sanitize($_COOKIE['mnm_user'], 3));
$sqlGeticategory = $db->get_var("SELECT user_categories from " . table_users . " where user_login = '******';");
if ($sqlGeticategory) {
$from_where .= " AND link_category NOT IN ({$sqlGeticategory})";
if (Multiple_Categories) {
$from_where .= " AND ac_cat_id NOT IN ({$sqlGeticategory})";
}
}
}
//should we filter to just this category?
if (isset($this->category)) {
//$catId = $db->get_var("SELECT category_id from " . table_categories . " where category_name = '" . $this->category . "';");
// $catId = get_category_id($this->category);
$catId = $this->category;
if ($catId) {
$child_cats = '';
// do we also search the subcategories?
if (Independent_Subcategories == true) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $catId);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$child_cat_sql .= ' OR `link_category` = ' . $child_cat_id . ' ';
if (Multiple_Categories) {
$child_cat_sql .= ' OR ac_cat_id = ' . $child_cat_id . ' ';
}
}
}
}
if (Multiple_Categories) {
$child_cat_sql .= " OR ac_cat_id = {$catId} ";
}
$from_where .= " AND (link_category={$catId} " . $child_cat_sql . ")";
}
}
if (isset($this->orderBy)) {
if (strpos($this->orderBy, "ORDER BY") != 1) {
$this->orderBy = " ORDER BY " . $this->orderBy;
}
}
// always check groups (to hide private groups)
$from_where = str_replace("WHERE", " LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id = " . table_groups . ".group_id WHERE", $from_where);
if (Voting_Method == 2) {
$from_where = str_replace("WHERE", " LEFT JOIN " . table_votes . " ON vote_type='links' AND vote_link_id=link_id AND vote_value>0 WHERE", $from_where);
}
// Search on additional categories
if (Multiple_Categories) {
$from_where = str_replace("WHERE", " LEFT JOIN " . table_additional_categories . " ON ac_link_id=link_id WHERE", $from_where);
}
$groups = $db->get_results("SELECT * FROM " . table_group_member . " WHERE member_user_id = {$current_user->user_id} and member_status = 'active'");
if ($groups) {
$group_ids = array();
foreach ($groups as $group) {
$group_ids[] = $group->member_group_id;
}
$group_list = join(",", $group_ids);
$from_where .= " AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list})) ";
} else {
$group_list = '';
$from_where .= " AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
}
if (intval($limit) <= 0) {
$limit = $this->pagesize;
}
if ($this->searchTerm == "" && $this->url == "") {
// like when on the index or new pages.
$this->sql = "SELECT link_id, link_votes, link_karma, link_comments {$from_where} {$search_clause} GROUP BY link_id {$this->orderBy} LIMIT {$this->offset}, {$limit}";
####### This is the first attempt to fix the bugs in the below 3 sorts methods. We will tidy and optimize the code later when we determine that
####### it is bugs free ~ redwinefireplace #######
####### We are constructing the entire query at once, taking into consideration:
####### Fixed the sort by Upvoted, Downvoted and Commented.
####### 1- Now the pagination is accurate and no more blank pages.
####### 2- Each of the above sort reflects the result of the page from where they were requested (I.e. from index page, returns the results of
####### links with 'published' status. and from New page, with status = 'new.
####### 3- Private groups links no longer show in the result unless the logged in user is a member in those groups. It is filtered in the
####### if ($group_list) { statement that creates a list of groups ids in which the user is member. ~ redwinefireplace
} else {
if ($this->searchTerm == 'upvoted') {
$usrclause = "";
$group = "GROUP BY link_id";
if ($catId) {
$from_where = "FROM " . table_links . " LEFT JOIN " . table_categories . " ON " . table_links . ".link_category =" . table_categories . ".category_id LEFT JOIN " . table_votes . " ON (" . table_links . ".link_id =" . table_votes . ".vote_link_id AND " . table_votes . ".vote_value > 0) LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id =" . table_groups . ".group_id LEFT JOIN " . table_additional_categories . " ON " . table_additional_categories . ".ac_link_id=" . table_links . ".link_id WHERE " . table_links . ".link_status='" . $this->filterToStatus . "' AND " . table_links . ".link_votes >0 AND (" . table_links . ".link_category=" . $catId . " OR " . table_additional_categories . ".ac_cat_id =" . $catId . ") AND " . table_votes . ".vote_type='links' AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
if ($group_list) {
$from_where = str_replace("ISNULL(" . table_groups . ".group_privacy))", "ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list}))", $from_where);
}
$this->sql = "SELECT DISTINCT * {$from_where} {$group} ORDER BY link_votes DESC LIMIT {$this->offset}, {$limit}";
} else {
$from_where = "FROM " . table_links . " LEFT JOIN " . table_votes . " ON (" . table_links . ".link_id =" . table_votes . ".vote_link_id AND " . table_votes . ".vote_value > 0) LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id =" . table_groups . ".group_id LEFT JOIN " . table_additional_categories . " ON " . table_additional_categories . ".ac_link_id=" . table_links . ".link_id WHERE " . table_links . ".link_status='" . $this->filterToStatus . "' AND " . table_links . ".link_votes >0 AND " . table_votes . ".vote_type='links' AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
if ($group_list) {
$from_where = str_replace("ISNULL(" . table_groups . ".group_privacy))", "ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list}))", $from_where);
}
$this->sql = "SELECT DISTINCT * {$from_where} {$group} ORDER BY link_votes DESC LIMIT {$this->offset}, {$limit}";
}
} else {
if ($this->searchTerm == 'downvoted') {
$usrclause = "";
$group = "GROUP BY link_id";
if ($catId) {
$from_where = "FROM " . table_links . " LEFT JOIN " . table_categories . " ON " . table_links . ".link_category =" . table_categories . ".category_id LEFT JOIN " . table_votes . " ON (" . table_links . ".link_id =" . table_votes . ".vote_link_id AND " . table_votes . ".vote_value < 0) LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id =" . table_groups . ".group_id LEFT JOIN " . table_additional_categories . " ON " . table_additional_categories . ".ac_link_id=" . table_links . ".link_id WHERE " . table_links . ".link_status='" . $this->filterToStatus . "' AND (" . table_links . ".link_category=" . $catId . " OR " . table_additional_categories . ".ac_cat_id =" . $catId . ") AND " . table_votes . ".vote_type='links' AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
if ($group_list) {
$from_where = str_replace("ISNULL(" . table_groups . ".group_privacy))", "ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list}))", $from_where);
}
$this->sql = "SELECT DISTINCT * {$from_where} {$group} ORDER BY link_votes ASC LIMIT {$this->offset}, {$limit}";
} else {
$from_where = "FROM " . table_links . " LEFT JOIN " . table_votes . " ON (" . table_links . ".link_id =" . table_votes . ".vote_link_id AND " . table_votes . ".vote_value < 0) LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id =" . table_groups . ".group_id LEFT JOIN " . table_additional_categories . " ON " . table_additional_categories . ".ac_link_id=" . table_links . ".link_id WHERE " . table_links . ".link_status='" . $this->filterToStatus . "' AND " . table_votes . ".vote_type='links' AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
if ($group_list) {
$from_where = str_replace("ISNULL(" . table_groups . ".group_privacy))", "ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list}))", $from_where);
}
$this->sql = "SELECT DISTINCT * {$from_where} {$group} ORDER BY link_votes ASC LIMIT {$this->offset}, {$limit}";
}
} else {
if ($this->searchTerm == "commented") {
$usrclause = "";
$group = "GROUP BY link_id";
if ($catId) {
$from_where = "FROM " . table_links . " LEFT JOIN " . table_categories . " ON " . table_links . ".link_category =" . table_categories . ".category_id LEFT JOIN " . table_comments . " ON " . table_links . ".link_id =" . table_comments . ".comment_link_id LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id =" . table_groups . ".group_id LEFT JOIN " . table_additional_categories . " ON " . table_additional_categories . ".ac_link_id=" . table_links . ".link_id WHERE " . table_links . ".link_status='" . $this->filterToStatus . "' AND (" . table_links . ".link_category=" . $catId . " OR " . table_additional_categories . ".ac_cat_id =" . $catId . ") AND " . table_comments . ".comment_status='published' AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
if ($group_list) {
$from_where = str_replace("ISNULL(" . table_groups . ".group_privacy))", "ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list}))", $from_where);
}
$this->sql = "SELECT DISTINCT * {$from_where} {$group} ORDER BY link_comments DESC LIMIT {$this->offset}, {$limit}";
} else {
$from_where = "FROM " . table_links . " LEFT JOIN " . table_comments . " ON " . table_links . ".link_id =" . table_comments . ".comment_link_id LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id =" . table_groups . ".group_id LEFT JOIN " . table_additional_categories . " ON " . table_additional_categories . ".ac_link_id=" . table_links . ".link_id WHERE " . table_links . ".link_status='" . $this->filterToStatus . "' AND " . table_comments . ".comment_status='published' AND (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
if ($group_list) {
$from_where = str_replace("ISNULL(" . table_groups . ".group_privacy))", "ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list}))", $from_where);
}
$this->sql = "SELECT DISTINCT * {$from_where} {$group} ORDER BY link_comments DESC LIMIT {$this->offset}, {$limit}";
}
} else {
$this->sql = "SELECT link_id, link_date, link_published_date, link_votes, link_karma, link_comments {$from_where} {$search_clause} {$this->orderBy}";
}
}
}
}
###### START Advanced Search ######
if ($this->adv) {
$from_where = table_links;
$search_clause = 'WHERE ';
$search_params = array();
$search_AND_params = array();
$query = "SELECT " . table_links . ".link_id AS link_id, " . table_links . ".link_date AS link_date, " . table_links . ".link_published_date AS link_published_date FROM ";
// always check groups (to hide private groups)
$from_where .= " LEFT JOIN " . table_groups . " ON " . table_links . ".link_group_id = " . table_groups . ".group_id ";
if ($group_list) {
$search_AND_params[] = " (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy) OR " . table_groups . ".group_id IN({$group_list})) ";
} else {
$search_AND_params[] = " (" . table_groups . ".group_privacy!='private' OR ISNULL(" . table_groups . ".group_privacy))";
}
//check if it is a literal search
$buffKeyword = $this->searchTerm;
$keywords = array();
if (substr($this->searchTerm, 1, 1) == '"' && substr($this->searchTerm, strlen($this->searchTerm) - 1, 1) == '"') {
$literal = true;
$addparam = ' COLLATE utf8_general_ci ';
$this->searchTerm = str_replace('\\"', '', $this->searchTerm);
$keywords[] = $this->searchTerm;
} else {
$keywords = explode(' ', $this->searchTerm);
}
$bufferOrig = $this->searchTerm;
//search category
if ($this->s_cat != 0) {
$catId = $this->s_cat;
if ($catId) {
$child_cats = '';
// do we also search the subcategories?
if (Independent_Subcategories == true) {
$child_array = '';
// get a list of all children and put them in $child_array.
children_id_to_array($child_array, table_categories, $catId);
if ($child_array != '') {
// build the sql
foreach ($child_array as $child_cat_id) {
$mult_sql .= " OR `link_category` = " . $child_cat_id . " ";
if (Multiple_Categories) {
$mult_sql .= " OR ac_cat_id = " . $child_cat_id . " ";
}
}
}
}
if (Multiple_Categories) {
$mult_sql .= " OR ac_cat_id = '" . $db->escape($this->s_cat) . "'";
}
$search_AND_params[] = "( " . table_links . ".link_category = '" . $db->escape($this->s_cat) . "' {$mult_sql})";
}
}
//search tags
if ($this->s_tags != 0 && $this->searchTerm) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " " . table_links . ".link_tags {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
$this->searchTerm = $bufferOrig;
}
//search links
if ($this->s_story != 0 && $this->searchTerm) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
if ($this->s_story == 1) {
$search_params[] = " " . table_links . ".link_title {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_story == 2) {
$search_params[] = " " . table_links . ".link_content {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_story == 3) {
$search_params[] = " " . table_links . ".link_title {$addparam} LIKE '%" . $this->searchTerm . "%' ";
$search_params[] = " " . table_links . ".link_content {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
}
$this->searchTerm = $bufferOrig;
}
//search author
if ($this->s_user != 0 && $this->searchTerm) {
$from_where .= " INNER JOIN " . table_users . " ON " . table_links . ".link_author = " . table_users . ".user_id ";
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " " . table_users . ".user_login {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
$this->searchTerm = $bufferOrig;
}
//search group
if ($this->s_group != 0 && $this->searchTerm) {
foreach ($keywords as $key) {
$this->searchTerm = $key;
if ($this->s_group == 1) {
$search_params[] = " " . table_groups . ".group_name {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_group == 2) {
$search_params[] = " " . table_groups . ".group_description {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
if ($this->s_group == 3) {
$search_params[] = " " . table_groups . ".group_name {$addparam} LIKE '%" . $this->searchTerm . "%' ";
$search_params[] = " " . table_groups . ".group_description {$addparam} LIKE '%" . $this->searchTerm . "%' ";
}
}
$this->searchTerm = $bufferOrig;
}
//search comments
if ($this->s_comments != 0 && $this->searchTerm) {
$from_where .= " LEFT JOIN " . table_comments . " ON " . table_links . ".link_id = " . table_comments . ".comment_link_id ";
foreach ($keywords as $key) {
$this->searchTerm = $key;
$search_params[] = " (" . table_comments . ".comment_content {$addparam} LIKE '%" . $this->searchTerm . "%' AND comment_status='published')";
}
$this->searchTerm = $bufferOrig;
}
//search by date
if ($this->s_date) {
$this->s_date = date('Y-m-d', strtotime($this->s_date));
# $from_where .= " WHERE DATE(link_date)='{$this->s_date}' ";
$search_AND_params[] = " DATE(" . table_links . ".link_date)='{$this->s_date}' ";
# $this->searchTerm = $bufferOrig;
}
if (Voting_Method == 2) {
$from_where .= " LEFT JOIN " . table_votes . " ON vote_type='links' AND vote_link_id=link_id AND vote_value>0";
}
// Search on additional categories
if (Multiple_Categories) {
$from_where .= " LEFT JOIN " . table_additional_categories . " ON ac_link_id=link_id";
}
if ($this->status != '' && $this->status != 'all') {
$search_params[] = " " . table_links . ".link_status = '{$this->status}' ";
}
if (sizeof($search_params)) {
$search_clause = '(' . implode(' OR ', $search_params) . ' ) ';
} else {
$search_clause = '1';
}
if (sizeof($search_AND_params) > 0) {
$search_clause .= ' AND (' . implode(' AND ', $search_AND_params) . ' ) ';
}
$this->sql = $query . ' ' . $from_where . ' WHERE ' . $search_clause . " AND " . table_links . ".link_status IN ('published','new')";
$this->searchTerm = $buffKeyword;
}
#echo $this->sql."<br><br>";
###### END Advanced Search ######
// if this query changes be sure to make sure to update link_summary
// just look for $linksum_count near the top
$this->countsql = "SELECT count(DISTINCT link_id) {$from_where} {$search_clause} ";
return;
}
