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):
- The posts having the keyword in the title.
- Then, if the keyword is in the content of the post.
- 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.
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” 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 
You can check it out see how it works for you.
Thanks! I will try this out later on today and let you know how it goes. I’ll track down SDK too – she doesn’t comment on my site very often.
Ok! For any problem please come back and let me know!
I will try it out as well, you are damn fast man!
Hi – I tried this and it doesn’t seem to be searching the comments. For example, on my 5 Ugly WordPress themes post I have the word retro and a lot of people mentioned retro in the comments. But if I search on that word, the post is the only search result.
In this part
$wpdb->posts as posts,
$wpdb->comments as comments
I had to change -> to -> because I got an error.
Catchable fatal error: Object of class wpdb could not be converted to string in /home/kimwood/public_html/wp-content/plugins/sem-search-reloaded.php on line 123
So, I’m not really sure what is happening. Let me know if you need any files or anything.
ok here are two things. first of all searching through comments will actually bring you back the post that has that comment. for instance for the retro word you have it on the ugly themes. let’s say it’s mentioned on another post, for instance the plugins one, then the two posts will be returned. it does not return the comment itself. i was kind of fuzzy on that one i must admit. it cannot return comments for a number of reasons. if we wanted that we would have to major rewrite the plugin.
secondly about the errors. my STUPID code highlighting thing struck again. just click on “View Source” and copy that text instead of the one in the box. i seriously have to do something about it…
please do come back if you still have problems!
Ok – got ya. Let me try to find a word that’s in the comments but not in an article … ok found one – sycophant, it’s been used in the comments a couple of times and never in an article. It found the two articles – very cool.
I use the syntax highlighter plugin for code – maybe that will work for you. http://code.google.com/p/syntaxhighlighter/
i am glad it works for you! i saw this one. the only problem is that i have to change the way i format code on all my code posts and they are a lot! anyway thanks for the suggestion.
still if you find any problems please stop by!
i tried this but am getting db errors (i am searching for ‘sdk’):
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '� OR posts.post_content REGEXP � OR comments.comment_content REGEXP �) GROUP' at line 18]
SELECT posts.*, CASE WHEN posts.post_title REGEXP 'sdk' THEN 1 ELSE 0 END AS keyword_in_title, MATCH ( posts.post_title, posts.post_content ) AGAINST ( 'sdk' ) AS mysql_score FROM wp_posts as posts, wp_comments as comments WHERE posts.post_date_gmt <= '2008-12-01 23:12:00' AND posts.post_password = '' AND posts.post_status = 'publish' AND posts.ID = comments.comment_post_ID AND ( posts.post_title REGEXP � OR posts.post_content REGEXP � OR comments.comment_content REGEXP �) GROUP BY posts.ID ORDER BY keyword_in_title DESC, mysql_score DESC, posts.post_date DESC LIMIT 0, 25
wait a minute, i fixed it…
had to change all the ‘ to ‘ in the code… (you might want to update you code above)
thanks! it appears to be searching comments now
to be honest i don’t see any problem in the code but readers be advised there might be this problem when copy/pasting. if sou you can do what SDK said or change the ‘ to “\”” (backslash and double quote). thanks for bringing this up!
I’ve found that I can run into the problem when copying and pasting code and I have to retype the single or double quotes. It’s something to keep in mind to mention whenever I write articles with code sample. I’m really glad that SDK got it to work 🙂
Hi – SDK commented over on my write-up of this plugin and said that it wasn’t searching titles. I could see that with her searches but haven’t been able to duplicate it on my site. Well, actually the problem is that I haven’t been able to come up with a term that is in the title but not in any article (as a reverse test). The comment is at http://www.kimwoodbridge.com/wordpress-plugin-search-reloaded-tweaked-by-stratos/#comment-2692
Just wanted to let you know that I disabled the Search Reloaded plugin and tried the default WP search, the chestnuts post was indeed found.
It definitely seems to be something with the Search Reloaded tweaks…
@Elana: i am looking into the problem to see how we can get around it… stay put!
any luck stratos? thx for all your help…
@Elana Unfortunately, to date, i haven’t been able to replicate your problem. I am still on it and i am trying to figure this out. I’ll let you know if i have a solution. Thanks for dropping by!
Just stumbled across your site – keep up the good work. I was just wondering if you knew another download location for search reloaded – http://www.semiologic.com/software/wp-fixes/search-reloaded/ gives me http://www.semiologic.com could not be found. Please check the name and try again.
I am desperate to implement a better search function on my site 😛
@Kash: They seem to be having some sort of a problem. Check out your mail 😉 Thanks for dropping by!
Can someone tell me where I can find search reloaded? I am in desperate need as well. It would be greatly appreciated. Thanks.
@Kevin Check your e-mail 😉
It looks like Semilogic has pulled the plugin as their site now says:
This is an exclusive plugin for Semiologic Pro users.”
Too bad, I wanted to try it out.
@Bohemian: Check your email 😉
Sadly I’m having no luck finding a download of the plug-in either.
@matazz: Check your email 😉
I have the same problem as Matazz.
@James: Check your mail too 😉
Thanks – I am seeing a problem though with pagination of the search page, but I see it’s working for you.
I’m using the original version of the plugin, without comment search.
Like Bohemian, I too went in search of the Search Reloaded plugin and found Semilogic is only offering it users of their Semilogic Pro software.
Too, bad as I think it’s just what I need. 🙁
@Miss B: Check out the update i issued and try out the plugin James mentioned.
Thanks stratosg. Another WP designer friend of mine had written up a post on her site that pointed to James’ site (sorry I didn’t recognize his logo/avatar earlier), Adam at archGFX and Yoast. It was when I was at Yoast that I saw the links to the Search Reloaded and Search Excerpt (which I had no problem downloading).
Today I just googled some more and found Kim’s site where I found a link to yours. Thanks for the update, and I’ll go back to Yoast and check out the keyword-highlighting-in-title plugin now. 😉
@Miss B: Well if you still have problems let me know…
This is a great post. I was wondering if there is still away to get the Search Reloaded script?
@Michael: Well unfortunately the author is now actually selling the plugin so i can’t help you out any more. See i have the plugin on my archives but i can’t go out sending it to people cause it’s copyright infringement. I am really sorry 🙁
Hi Stratos- Were you asked not to send it out? Wouldn’t the version that was released prior to it being sold be under GPL?
I haven’t tested this yet but am going to
It might be a good replacement for Search Reloaded.
@Kim: Well i was not asked not to send it out but the plugin was not released under the GNU GPL before. If it was i would have added a download mirror here. I don’t know i am thinking about asking the author about it…
I was just asking – I wasn’t sure if it had a GPL or not. I think it’s probably better to see if that new one works as well as Search Reloaded. I think it’s kind of uncool to offer something for free and then start charging for it. Why not make a “better” version to sell and leave the original?
@Kim: Well i totally agree. The plugin is way simple. It has a bit of a complicated query but it’s nothing much. Now charging for this kind of stuff and that much (!!) is not an option for me. But, i respect his decision, it’s his code, so i just can’t help my readers with this plugin any more unfortunately. I have Jame’s suggestion as well that i think is pretty good too…
Has anyone looked at this search : http://wordpress.org/extend/plugins/wordpress-sphinx-plugin/ I think it could be the answer to not using search reloaded?
I beleive it returns the results in order of relevance..
Hope this helps
@Kash: Thanks for adding one more solution to the readers over here. I would suggest all of those who feel hooked up to search reloaded try both the solutions… Thanks again for stopping by!
Didn’t know I was so helpful!
@Kash I believe you have to install itself Sphinx separately for the plugin to work, which can be either a pain or impossible if the server is locked down too much.
Where is the $reg_one_present variable being set, can you show the code that sets that variable?
Never mind I wrote my own plugin.