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; }