function sphinx_search_date_range($from, $to)
{
list($from_timestamp, $to_timestamp) = search_date_range($from, $to, SEARCH_DATE_RANGE_ARRAY);
$range = '';
if (isset($from_timestamp) && is_numeric($from_timestamp)) {
$range = "AND created >= {$from_timestamp} ";
}
if (isset($to_timestamp) && is_numeric($to_timestamp)) {
$range .= "AND created <= {$to_timestamp} ";
}
return $range;
}
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;
}
