This post will be a conclusion on the requests for plugins and tweaks requested over at Kim’s blog. Both her and a reader of her’s, SDK, requested on a small tweak on the plugin called “Search Reloaded”. The plugin does a very good job on serching posts for you. It actually enhances the search feature alot by sorting not by date but by relevance. To be exact it sorts with these criteria (and in this order):

  1. The posts having the keyword in the title.
  2. Then, if the keyword is in the content of the post.
  3. Finally by date.

It’s actually a pretty good approach, surely much better than the plain “sort by date” that WordPress has by default. The tweak that was requested was this. The plugin should search in the comments of a post too. That’s rather reasonable. Now, i can’t modify the plugin and let you have it from here, since the license is non-GPL. But i can post the tweak and you can do it yourself. So here it is…

First of all, you can download the plugin (if you haven’t done already) at their site here. The version i am working on is 2.11 so please make sure you have that or the line numbers may differ. After you have it downloaded, open “sem-search-reloaded.php” with your favorite text editor. What we are going to do here is alter the select query a little so we can include selecting posts in comments too, other than only posts. Please look for te following code after line 111.

$search_query = "
	SELECT
		posts.*,
		CASE
			WHEN posts.post_title REGEXP '$reg_one_present'
				THEN 1
				ELSE 0
			END AS keyword_in_title,
		MATCH ( posts.post_title, posts.post_content )
			AGAINST ( '" . addslashes($query_string) . "' ) AS mysql_score
	FROM
		$wpdb->posts as posts
	WHERE
		posts.post_date_gmt <= '" . $now . "'"
		. ( ( defined('sem_home_page_id') && sem_home_page_id )
			? "
		AND posts.ID <> " . intval(sem_home_page_id)
			: ""
			)
		. "
		AND posts.post_password = ''
		AND posts.post_status = 'publish'
		AND ( posts.post_title REGEXP '$reg_one_present' OR posts.post_content REGEXP '$reg_one_present' )
	GROUP BY
		posts.ID
	ORDER BY
		keyword_in_title DESC, mysql_score DESC, posts.post_date DESC
	LIMIT " . intval($offset) . ", ". intval($posts_per_page);

Now, we need to tinker with three places on this code to get the job done. The first placeis after the “FROM” clause. The second one is within the “AND” series and the final one is in the final “AND”. But let’s take a detailed look in each one of them.

Please focus on this line:

$wpdb->posts as posts

You need to change this to:

$wpdb->posts as posts,
$wpdb->comments as comments

Now, the second thing we need to change is here:

AND posts.post_status = 'publish'

What we need to do is add another AND clause. Please change it to this:

AND posts.post_status = 'publish'
AND posts.ID = comments.comment_post_ID

Finally, on the exact next line find this:

AND ( posts.post_title REGEXP '$reg_one_present' OR posts.post_content REGEXP '$reg_one_present' )

Change it to this:

AND ( posts.post_title REGEXP '$reg_one_present' OR posts.post_content REGEXP '$reg_one_present' OR comments.comment_content REGEXP '$reg_one_present')

For your convenience here is the whole edited code:

$search_query = "
	SELECT
		posts.*,
		CASE
			WHEN posts.post_title REGEXP '$reg_one_present'
				THEN 1
				ELSE 0
			END AS keyword_in_title,
		MATCH ( posts.post_title, posts.post_content )
			AGAINST ( '" . addslashes($query_string) . "' ) AS mysql_score
	FROM
		$wpdb->posts as posts,
		$wpdb->comments as comments
	WHERE
		posts.post_date_gmt <= '" . $now . "'"
		. ( ( defined('sem_home_page_id') && sem_home_page_id )
			? "
		AND posts.ID <> " . intval(sem_home_page_id)
			: ""
			)
		. "
		AND posts.post_password = ''
		AND posts.post_status = 'publish'
		AND posts.ID = comments.comment_post_ID
		AND ( posts.post_title REGEXP '$reg_one_present' OR posts.post_content REGEXP '$reg_one_present' OR comments.comment_content REGEXP '$reg_one_present')
	GROUP BY
		posts.ID
	ORDER BY
		keyword_in_title DESC, mysql_score DESC, posts.post_date DESC
	LIMIT " . intval($offset) . ", ". intval($posts_per_page);

You are done! Please make a backup of the original plugin before you upload and lose something. Then, save, upload and make a search!

Hopefully, this will work out of the box. If it doesn’t then come back here with the problems you encounter and i’ll do my best to solve them. But please, be very cautious when changing stuff around and always keep a backup so in case anything goes wrong you can go back to the good old working plugin.

Update

I have been getting a lot of requests because the plugin is not available for download now. A commenter below, James, took a better look into the matter and came up with a combo o plugins that will do just that. Here is his suggestion:

After playing around with all of them for a bit,
the cleanest way is to have “search reloaded” &
“search excerpt”[1] both on. Search Unleashed
didn’t work everywhere as well as it should have,
which is a pity.

I did need to put in keyword-highlighting-in-title
manually as described on [2]
[1] http://scott.yang.id.au/code/search-excerpt/
[2] http://yoast.com/wordpress-search/

You can check it out see how it works for you.