function sphinx_search_execute($search_arguments, &$error) { if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) { return false; } if (!($table_prefix = get_table_prefix())) { return false; } if (!($forum_fid = get_forum_fid())) { return false; } /** @var mysqli $sphinx */ if (!($sphinx = sphinx_search_connect())) { $error = SEARCH_SPHINX_UNAVAILABLE; return false; } // Sphinx Search index name is the forum's webtag. if (!($sphinx_search_index = get_webtag())) { $error = SEARCH_SPHINX_UNAVAILABLE; return false; } // Validate the webtag if (!forum_check_webtag_available($sphinx_search_index)) { $error = SEARCH_SPHINX_UNAVAILABLE; return false; } // Regular Database connection. if (!($db = db::get())) { return false; } // If the user has specified a folder within their viewable scope limit them // to that folder, otherwise limit them to their available folders. $where_sql = "WHERE forum = {$forum_fid} AND fid IN ({$search_arguments['fid']}) "; // Where query needs to limit the search results to the user specified date range. $where_sql .= sphinx_search_date_range($search_arguments['date_from'], $search_arguments['date_to']); // Username based search. if (isset($search_arguments['user_uid_array']) && sizeof($search_arguments['user_uid_array']) > 0) { // Save the sort by and sort dir. search_save_arguments($search_arguments); // Combine the user UIDs into a comma-seperated list. $user_uids = implode(',', array_filter($search_arguments['user_uid_array'], 'is_numeric')); // Check if we're searching for threads or posts started by these users. if (isset($search_arguments['user_include']) && is_numeric($search_arguments['user_include'])) { if ($search_arguments['user_include'] == SEARCH_FILTER_USER_THREADS) { $where_sql .= "AND by_uid IN ({$user_uids}) AND pid = 1 "; } else { if ($search_arguments['user_include'] == SEARCH_FILTER_USER_POSTS) { $where_sql .= "AND from_uid IN ({$user_uids}) "; } } } } /// Keyword based search. if (isset($search_arguments['search_string']) && strlen(trim($search_arguments['search_string'])) > 0) { // Sphinx doesn't like -- in MATCH. Don't know if it's because it // thinks it is a MySQL-style comment or a bug. We have no choice // but to strip it out. $search_string = $sphinx->real_escape_string(str_replace('--', '', $search_arguments['search_string'])); search_save_arguments($search_arguments); $where_sql .= "AND MATCH('{$search_string}')"; } else { if (!isset($search_arguments['user_uid_array']) || sizeof($search_arguments['user_uid_array']) < 1) { $error = SEARCH_NO_MATCHES; return false; } } // If the user wants results grouped by thread (TID) then do so. if (isset($search_arguments['group_by_thread']) && $search_arguments['group_by_thread'] == SEARCH_GROUP_THREADS) { $group_sql = "GROUP BY tid"; } else { $group_sql = ""; } // Get the correct sort dir $sort_dir = $search_arguments['sort_dir'] == SEARCH_SORT_DESC ? 'DESC' : 'ASC'; // Construct the order by clause. switch ($search_arguments['sort_by']) { case SEARCH_SORT_NUM_REPLIES: $order_sql = "ORDER BY length {$sort_dir}"; break; case SEARCH_SORT_FOLDER_NAME: $order_sql = "ORDER BY fid {$sort_dir}"; break; case SEARCH_SORT_AUTHOR_NAME: $order_sql = "ORDER BY from_uid {$sort_dir}"; break; default: $order_sql = "ORDER BY created {$sort_dir}"; break; } // Prepend _DELTA to the end of the index name. $sphinx_search_index_delta = sprintf('%s_DELTA', $sphinx_search_index); // Build query including main and delta indexes. $sql = "SELECT *, WEIGHT() FROM {$sphinx_search_index}, {$sphinx_search_index_delta} "; $sql .= "{$where_sql} {$group_sql} {$order_sql} LIMIT 1000"; // Execute the query if (!($result = $sphinx->query($sql))) { return false; } // Check if we have any results if ($result->num_rows == 0) { // No results from search. $error = SEARCH_NO_MATCHES; return false; } while (($search_result = $result->fetch_assoc()) !== null) { if (isset($search_result['weight()'])) { $weight = $search_result['weight()']; } else { if (isset($search_result['weight'])) { $weight = $search_result['weight']; } else { $weight = 0; } } $sql = "INSERT INTO SEARCH_RESULTS (UID, FORUM, TID, PID, RELEVANCE) "; $sql .= "SELECT '{$_SESSION['UID']}' AS UID, '{$forum_fid}' AS FORUM, THREAD.TID, POST.PID, "; $sql .= "{$weight} AS RELEVANCE FROM `{$table_prefix}POST` "; $sql .= "POST INNER JOIN `{$table_prefix}THREAD` THREAD ON (THREAD.TID = POST.TID) "; $sql .= "WHERE THREAD.TID = '{$search_result['tid']}' AND POST.PID = '{$search_result['pid']}' "; $sql .= "AND THREAD.LENGTH > 0 AND THREAD.DELETED = 'N' AND (THREAD.APPROVED IS NOT NULL "; $sql .= "OR THREAD.BY_UID = '{$_SESSION['UID']}') "; if (!$db->query($sql)) { return false; } } return true; }
function search_mysql_execute($search_arguments, &$error) { if (!isset($_SESSION['UID']) || !is_numeric($_SESSION['UID'])) { return false; } if (!($table_prefix = get_table_prefix())) { return false; } if (!($forum_fid = get_forum_fid())) { return false; } // Database connection. if (!($db = db::get())) { return false; } // If the user has specified a folder within their viewable scope limit them // to that folder, otherwise limit them to their available folders. $where_sql = "WHERE THREAD.FID IN ({$search_arguments['fid']}) "; // Can't search for deleted threads nor threads with no posts $where_sql .= "AND THREAD.DELETED = 'N' AND THREAD.LENGTH > 0 AND (THREAD.APPROVED IS NOT NULL OR THREAD.BY_UID = '{$_SESSION['UID']}') "; // Where query needs to limit the search results to the user specified date range. $where_sql .= search_date_range($search_arguments['date_from'], $search_arguments['date_to'], SEARCH_DATE_RANGE_SQL); // No select, joins, from or having clauses by default. $select_sql = null; $join_sql = null; $from_sql = null; $having_sql = null; // Username based search. if (isset($search_arguments['user_uid_array']) && sizeof($search_arguments['user_uid_array']) > 0) { // Base query slightly different if you're not searching by keywords if (isset($search_arguments['group_by_thread']) && $search_arguments['group_by_thread'] == SEARCH_GROUP_THREADS) { $select_sql = "INSERT INTO SEARCH_RESULTS (UID, FORUM, TID, PID, RELEVANCE) SELECT SQL_NO_CACHE "; $select_sql .= "SQL_BUFFER_RESULT {$_SESSION['UID']}, {$forum_fid}, POST.TID, MIN(POST.PID), "; $select_sql .= "1.0 AS RELEVANCE "; } else { $select_sql = "INSERT INTO SEARCH_RESULTS (UID, FORUM, TID, PID, RELEVANCE) SELECT SQL_NO_CACHE "; $select_sql .= "SQL_BUFFER_RESULT {$_SESSION['UID']}, {$forum_fid}, POST.TID, POST.PID, "; $select_sql .= "1.0 AS RELEVANCE "; } // Save the sort by and sort dir. search_save_arguments($search_arguments); // FROM query uses POST table if we're not using keyword searches. $from_sql = "FROM `{$table_prefix}POST` POST "; // Join to the THREAD table for the TID $join_sql = "INNER JOIN `{$table_prefix}THREAD` THREAD ON (THREAD.TID = POST.TID) "; // Combine the user UIDs into a comma-separated list. $user_uids = implode(',', array_filter($search_arguments['user_uid_array'], 'is_numeric')); // Check if we're searching for threads or posts started by these users. if (isset($search_arguments['user_include']) && is_numeric($search_arguments['user_include'])) { if ($search_arguments['user_include'] == SEARCH_FILTER_USER_THREADS) { $where_sql .= "AND THREAD.BY_UID IN ({$user_uids}) AND POST.PID = 1 "; } else { if ($search_arguments['user_include'] == SEARCH_FILTER_USER_POSTS) { $where_sql .= "AND POST.FROM_UID IN ({$user_uids}) "; } } } } /// Keyword based search. if (isset($search_arguments['search_string']) && strlen(trim($search_arguments['search_string'])) > 0) { $search_string = $db->escape($search_arguments['search_string']); $from_sql = "FROM `{$table_prefix}POST_CONTENT` POST_CONTENT "; $join_sql = "INNER JOIN `{$table_prefix}THREAD` THREAD ON (THREAD.TID = POST_CONTENT.TID) "; $join_sql .= "INNER JOIN `{$table_prefix}POST` POST ON (POST.TID = POST_CONTENT.TID AND POST.PID = POST_CONTENT.PID) "; $having_sql = "HAVING RELEVANCE > 0.2 "; search_save_arguments($search_arguments); if (isset($search_arguments['group_by_thread']) && $search_arguments['group_by_thread'] == SEARCH_GROUP_THREADS) { $select_sql = "INSERT INTO SEARCH_RESULTS (UID, FORUM, TID, PID, RELEVANCE) "; $select_sql .= "SELECT SQL_NO_CACHE SQL_BUFFER_RESULT {$_SESSION['UID']}, {$forum_fid}, "; $select_sql .= "POST.TID, MIN(POST.PID), MATCH(POST_CONTENT.CONTENT, THREAD.TITLE) "; $select_sql .= "AGAINST('{$search_string}' IN BOOLEAN MODE) AS RELEVANCE "; } else { $select_sql = "INSERT INTO SEARCH_RESULTS (UID, FORUM, TID, PID, RELEVANCE) "; $select_sql .= "SELECT SQL_NO_CACHE SQL_BUFFER_RESULT {$_SESSION['UID']}, {$forum_fid}, "; $select_sql .= "POST.TID, POST.PID, MATCH(POST_CONTENT.CONTENT, THREAD.TITLE) "; $select_sql .= "AGAINST('{$search_string}' IN BOOLEAN MODE) AS RELEVANCE "; } $where_sql .= "AND MATCH(POST_CONTENT.CONTENT) AGAINST('{$search_string}' IN BOOLEAN MODE) "; } else { if (isset($search_arguments['search_tag']) && strlen(trim($search_arguments['search_tag'])) > 0) { $search_tag = $db->escape($search_arguments['search_tag']); $from_sql = "FROM `{$table_prefix}POST` POST "; $join_sql = "INNER JOIN `{$table_prefix}THREAD` THREAD ON (THREAD.TID = POST.TID) "; $join_sql .= "INNER JOIN `{$table_prefix}POST_TAG` POST_TAG ON (POST_TAG.TID = POST.TID AND POST_TAG.PID = POST.PID) "; $join_sql .= "INNER JOIN `{$table_prefix}TAG` TAG ON (TAG.TID = POST_TAG.TAG) "; search_save_arguments($search_arguments); if (isset($search_arguments['group_by_thread']) && $search_arguments['group_by_thread'] == SEARCH_GROUP_THREADS) { $select_sql = "INSERT INTO SEARCH_RESULTS (UID, FORUM, TID, PID, RELEVANCE) "; $select_sql .= "SELECT SQL_NO_CACHE SQL_BUFFER_RESULT {$_SESSION['UID']}, {$forum_fid}, "; $select_sql .= "POST.TID, MIN(POST.PID), 1.0 AS RELEVANCE "; } else { $select_sql = "INSERT INTO SEARCH_RESULTS (UID, FORUM, TID, PID, RELEVANCE) "; $select_sql .= "SELECT SQL_NO_CACHE SQL_BUFFER_RESULT {$_SESSION['UID']}, {$forum_fid}, "; $select_sql .= "POST.TID, POST.PID, 1.0 AS RELEVANCE "; } $where_sql .= "AND TAG.TAG = '{$search_tag}' "; } else { if (!isset($search_arguments['user_uid_array']) || sizeof($search_arguments['user_uid_array']) < 1) { $error = SEARCH_NO_MATCHES; return false; } } } // If the user wants results grouped by thread (TID) then do so. if (isset($search_arguments['group_by_thread']) && $search_arguments['group_by_thread'] == SEARCH_GROUP_THREADS) { $group_sql = "GROUP BY THREAD.TID "; } else { $group_sql = ""; } // Get the correct sort dir $sort_dir = $search_arguments['sort_dir'] == SEARCH_SORT_DESC ? 'DESC' : 'ASC'; // Construct the order by clause. switch ($search_arguments['sort_by']) { case SEARCH_SORT_RELEVANCE: $order_sql = "ORDER BY RELEVANCE {$sort_dir} "; break; case SEARCH_SORT_NUM_REPLIES: $order_sql = "ORDER BY THREAD.LENGTH {$sort_dir} "; break; case SEARCH_SORT_FOLDER_NAME: $order_sql = "ORDER BY THREAD.FID {$sort_dir} "; break; case SEARCH_SORT_AUTHOR_NAME: $order_sql = "ORDER BY POST.FROM_UID {$sort_dir} "; break; default: $order_sql = "ORDER BY POST.CREATED {$sort_dir} "; break; } // Set a limit of 1000 results. $limit_sql = "LIMIT 0, 1000 "; // Build the final query. $sql = "{$select_sql} {$from_sql} {$join_sql} {$where_sql} "; $sql .= "{$group_sql} {$having_sql} {$order_sql} {$limit_sql}"; // Execute the query if (!$db->query($sql)) { return false; } // Check the number of results if ($db->affected_rows > 0) { return true; } // No results from search. $error = SEARCH_NO_MATCHES; return false; }