Comments

Comments for Related Entries Revisited

Excerpt: A better way to create a list of related blog entries using MySQL fulltext indexes and the MTSQL plugin. Includes a complete technical overview of how the solution works and performance considerations. Read the whole article…

Trackback from Random Neural Misfirings
May 1, 2003 12:21 PM

Natural keywords and categories

Excerpt: How to extract keywords in blogs without even trying

Trackback from codepoetry
May 2, 2003 7:01 AM

A Better Related Entries

Excerpt: There was once a plugin for getting Related Entries, and it worked well. If you used categories in a good way (I do) then life is great. However, there are times when a really related entry is in another category...

Trackback from About
May 2, 2003 12:40 PM

Related Entries

Excerpt: Adam Kalsey has come up with a great idea using one of Brad Choate's plugins. I love watching genius at work, and then implementing their ideas.

Jesper
May 3, 2003 5:00 AM

Woah, very nice. The html shows up in the SQL query in this entry though. Might want to fix that.

Trackback from inluminent/weblog
May 3, 2003 12:32 PM

Related Entries Redux and Implementation

Excerpt: Thanks to all the comments on my most recent Related Entries post, I've installed Adam Kalsey's Related Entries code. The

Adam Kalsey
May 3, 2003 4:29 PM

I don't see any HTML in the SQL. There's some HTML inside the container tag that formats the list of related entries, but not in the query.

Jesper
May 4, 2003 1:50 PM

No, I meant this query: [span class="caps"]ALTER TABLE[/span] mt_entry [span class="caps"]ADD FULLTEXT [/span]( entry_keywords, entry_title, entry_excerpt ) (made the tags brackets instead so they wouldn't get stripped)

Adam Kalsey
May 4, 2003 2:16 PM

Ahh. There it is. That was caused by a strange interaction between Textile and an MT macro. It's fixed now.

Trackback from nf0's Life
May 5, 2003 9:55 PM

Nightly Link Dump

Excerpt: MTshell (a command line frontend to MovableType) Noel Jackson.com Related Entries Revisited :: Kalsey Consulting Group Waag Connect > E-book can last six months on two AA cells - News & Technology - CNETAsia Blackmask Online: Wet Paperback Before Scan...

Joe Grossberg
May 7, 2003 11:19 AM

I think MT's future is not as CGI scripts for blogging, but as a full-fledged tag-based language. With its access to Perl, I think it'll be like a ColdFusion that doesn't suck. Maybe I'm insane, but I think it's an avenue worth pursuing: http://www.joegrossberg.com/archives/000632.html

Trackback from Tribblescape
May 7, 2003 11:37 AM

Related entries for Movable Type

Excerpt: Adam Kalsey has a new method for automatically generating related entries links between articles in a Movable Type weblog. It requires a little setup work, but it look like a promising system that isn't dependent on keeping a taxonomy of...

Sherzod Ruzmetov
May 22, 2003 10:29 AM

This is a brilliant idea! Having said that, I have a feeling this method is not going to work if the Movable Type is not using MySQL object driver. I believe there is still some room for improvement untill there is a plugin that can do this independant of the driver being used. P.S. It can be done through MT::Object class :-)

Adam Kalsey
May 22, 2003 11:03 AM

Since the whole idea relies upon MySQL's fulltext indexing support, making a plugin that supported non-MySQL backends would be somewhat pointless. If they don't have MySQL to begin with, then I'd need to build a whole indexing system.

Trackback from Al-Muhajabah's Movable Type Tips
May 24, 2003 5:41 PM

simulating a semantic search of your blog

Excerpt: Using the SQL plugin, you can simulate a semantic search of your blog to find entries related to a given entry. A clever idea from Adam Kalsey. Just follow his instructions and you're good to go. Note that you will...

Trackback from The Long Letter
June 6, 2003 10:36 AM

Related entries

Excerpt: I'm experimenting with Adam Kalsey's Related Entried Revisited MovableType modification.

KO
June 19, 2003 11:17 PM

I'm currently using your MT related entries plugin to grab related thumbnails for my photos using the following: ">-thumb.jpg" alt="" title="" border="0" width="120" height="90" class="littlepic"> The MTEntryMore tag is where the picture location is stored, so I was trying to figure out how to change your sql code to the above. Does this query grab the whole entry? Can I just use MTEntryMore in there also? Ideally I would prefer using the MTrelated tag its a lot simpler!

KO
June 22, 2003 5:48 AM

Could all the sql be incorporated into a plugin so that it can be used just like your older plugin? i.e just specifying a tag, and not a chunk of code.

Jason Mevius
July 8, 2003 4:14 PM

Thanks, this plugin works great. I added a [h4] tag and a [ul] tag at the start of the [MTSqlEntries] query and a [/ul] tag after the query (as I assume you implied). It works beautifully whenever I have related entries. Sometimes, however, I don't. Is there a way to combine this so that it leaves off the [h4], [ul], and [/ul] tags whenever there are no related entries?

Adam Kalsey
July 8, 2003 4:18 PM

You could use the IfEmpty plugin to check to see if the MTSQLEntries returns any data and only display your formatting if it does.

Jason Mevius
July 9, 2003 11:10 AM

Hmm. Okay, I've been playing with the formatting for the IfNotEmpty tag, but I keep getting tripped up. Is there some additional guidelines or examples of nesting the expression?

Adam Kalsey
July 9, 2003 11:38 AM

You probably want to do something like... <MTIfNotEmpty expr="[MTSQLEntries query="SELECT entry_id, MATCH (entry_keywords, entry_title, entry_excerpt) AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') AS score FROM mt_entry WHERE MATCH (entry_keywords, entry_title, entry_excerpt) AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') AND entry_id != '[MTEntryID]' AND entry_blog_id = [MTBlogID] ORDER BY score DESC LIMIT 0 , 4"]1[/MTSQLEntries]"> ... your related entries template ... </MTIfNotEmpty>

Jason Mevius
July 9, 2003 1:36 PM

That still doesn't seem to be working. I verified I had the latest version of the SQLEntries and the IfEmpty plugins installed. Anything else I should check?

Trackback from Tech-Weblog by Christoph C. Cemper
July 14, 2003 4:07 PM

New related entries

Excerpt: Related Entries Revisited by Kalsey Consulting Group explains how to add a fulltext related entry feature to MT! Great stuff Adam!...

Alexander Payne
July 22, 2003 12:11 PM

Is there any reason why Related Entries would only find entries that share a common word in their titles? I've added this to my MT installation, following the above directions to the letter, and this seems to be the case; only the 'entry_title' appears to be getting indexed. Thoughts?

Adam Kalsey
July 22, 2003 1:54 PM

Probably because you don't have excerpts or keywords. The only things compared using my code is titles, excerpts, and keywords. The autogenerated excerpts don't count; those aren't actually stored in the database.

Alexander Payne
July 22, 2003 6:11 PM

Hmm, that's a good reason. Would doing a full text search on the entire entry bodies yield odd results? If not, how would that be done? And, to sympathize with Jason Mevius above, the IfEmpty statement doesn't work.

Adam Kalsey
July 23, 2003 7:43 AM

That depends on your entries, really. By using the whole entry body, you're pretty much guaranteeing that there will always be a match, even if it's just on words that really aren't part of the meaning of the entry (like "that" and "some"). If your entries aren't typically very similar, your related entries would show entries that simply had a bunch of similar words. The reason that I use title, keywords, and excerpt is because those fields usually have a very high concentration of words pertaining to the general subject of the entry. You could avoid some of the not-really-related problem by adding the fulltext match score as one of the criteria. Just change a portion of the SQL code to read: AND entry_blog_id = [MTBlogID] AND score > 5 ORDER BY score DESC LIMIT 0 , 4 You'll want to adjust the score > 5 part to suit your needs. Play with it a bit by using different numbers until you like the results. A higher number is more sensitive and will require a closer match. A lower number is less sensitive and will show more entries. Using Brad's MTNull and MTRegex plugins, I've come up with a method for not showing the related block if there were no related entries. Make sure your related block prints on all one line in the HTML source and add a comment to the end of it. Then use regex to look for the beginning of the block followed immediately by the comment. If that's found, there were no related entries so replace the block with nothing. <MTAddRegex name="norelated">s|<h2>Related:</h2><!--end related-->||</MTAddRegex> <MTNull regex="norelated"> <h2>Related:</h2><MTSQLEntries query="SELECT entry_id, MATCH (entry_keywords, entry_title, entry_excerpt) AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') AS score FROM mt_entry WHERE MATCH (entry_keywords, entry_title, entry_excerpt) AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') AND entry_id != '[MTEntryID]' AND entry_blog_id = [MTBlogID] ORDER BY score DESC LIMIT 0 , 4"><li><a href="<MTEntryLink>"><MTEntryTitle></a></li></MTSQLEntries><!--end related--> </MTNull>

Alexander Payne
July 23, 2003 6:17 PM

I've done the modifications to my mySQL tables to allow full-text searches on the entry bodies themselves (stored in entry_text), and I've got the query changed to grab entries from them. That all works fine, but no matter where I stick the 'AND score > 5', I get the error: "Build error in template 'Individual Entry Archive': Error in tag: You did not specify a query." It seems that this isn't proper syntax, or something (as you can probably tell, databases aren't my thing). Without specifying reasonable scores I get some pretty unrelated related entries, to say the least. Any thoughts as to why MTSQLEntries would be barfing on this?

Trackback from Alexander Payne
July 23, 2003 6:41 PM

Glitches, Relations, Additions Oh MY

Excerpt: There was a bit of downtime for my sites individual entry archive today, but I hope it didn't affect any of you too drastically. Moveable Type corrupted itself but not, fortunately, my database. A "reinstallation" (that is, uploading a fresh...

Adam Kalsey
July 23, 2003 9:20 PM

MT is seeing that > sign and thinking you're trying to end the tag. So you could use &gt; intead and it should work.

Alexander Payne
July 23, 2003 10:46 PM

Well, with that modification it now processes the whole query, but errors with: Error in tag: Error in query: Unknown column 'score' in 'where clause'. Nuts. Thanks for your patience with this, Adam. I totally owe you a coffee or something if we ever meet at a conference ;-)

Adam Kalsey
July 23, 2003 11:06 PM

I forgot that th published version of the code was different than my version. You'll need to alter the SQL statement. It should start with "SELECT entry_id, MATCH (entry_keywords, entry_title, entry_excerpt) as score"

Alexander Payne
July 24, 2003 9:21 AM

Not quite... "Error in query: You have an error in your SQL syntax near 'AS score AGAINST (' Glitches, Relations, Additions Oh My') AS score FROM mt_entr' at line 1"

jim
September 10, 2003 12:35 AM

try adding this to my blog and got this error when I did a rebuilt. Can't get DBI::st=HASH(0x872c3d8)->{NAME_hash}: unrecognised attribute at extlib/bradchoate/sql.pm line 132. i've followed the instructions you've stated. please advise.

Adam Kalsey
September 10, 2003 3:15 PM

According to Brad, you are probably using an old version of DBI. Have your host update your DBI drivers.

Philip Dhingra
October 1, 2003 11:46 PM

This plug-in is extremely brilliant. My 800+ posts gathering dust in the archives have no come alive thanks to this tool.

Trackback from Jaykul :: Huddled Masses
October 2, 2003 12:35 PM

Another Bayes Idea

Excerpt: What if each time I posted something I parsed the RSS feeds in my daily whirl, and apply Bayesian algorithms to generate links to similar recent entries on other people's sites...

KO
October 5, 2003 2:18 PM

How much effect does this have on rebuilding times? For a lot of entries, is this in effect searching each entry against all the others over and over?

Adam Kalsey
October 6, 2003 9:18 AM

Essentially yes, this does cause each entry to search all the others, but it's not much of a performance hit for several reasons. First, we're searching against a MySQL fulltext index. This is very fast. Its nothing like using "like" matching in SQL. Fulltext indexing is designed to quickly do precisely what we're doing here. We're also limiting the results returned to four. So MySQL does the lookup in the index, sorts the results and spits back the first four. Then it quits. Movable Type doesn't have to parse through hundreds of entries; it on;y get the four it needs. The vast majority of the time you are only rebuilding a single entry. When you post a new entry or someone comments, only that entry gets rebuilt. The only time you rebuild all the entries is when you force a full rebuild from the MT publishing interface.

craig1972
November 14, 2003 12:55 AM

hi, brilliant plugin. now, is there a way to show the whole "related entries" section ONLY if there is at least one match?

H
December 4, 2003 12:01 AM

Excellent stuff. It took all of ten minutes to set up. This is a very close match to what I was looking for. The quality of the semantic search still depends rather heavily, it seems, on the application of keywords, but that's fine with me. I'm curious if you can explain, perhaps I've just overlooked it in the discussion above, what the zero in this phrase means: DESC LIMIT 0 , 4 I understand the limit portion; that's easy. Changing the zero to other numbers does seem to impact greatly the nature of the results. If the query stops when it has found LIMIT, does that not preclude better matches deeper within the database?

Adam Kalsey
December 4, 2003 9:01 AM

LIMIT determines how many rows will be returned by the statement. All rows are still examined, LIMIT only affects the output. So LIMIT 0, 4 says return 4 rows skipping the first 0 rows (ie start at row 1). LIMIT 2, 4 means return 4 rows starting with the third one. You can change the number of related entries by changing the limit. LIMIT 6 would return the first six related entries. You can read more about LIMIT inthe MySQL documentation at http://www.mysql.com/doc/en/SELECT.html

Trackback from mashby.com
December 7, 2003 10:21 AM

Sprucing Up The Place

Excerpt: After yesterday's entry regarding Plugin and how to tweak MT, I couldn't help but do a little tweaking myself. That coupled with the comments from my review at The Weblog Review, I thought of a few things that I could do to kind of spruce up the place....

Patrick Madden
December 11, 2003 9:22 AM

Thank you for this method. I've just set it up, it works fine, but, like Alexander Payne, I haven't been entering excerpts or keywords, and thus I get only entries with common words in their titles. I've read through your explanation in the comments here, and it seems logical, but I am wondering a couple of things: Your query looks at Titles, Keywords, and Excerpts, but I would expect something about MTEntryExcerpt in the AGAINST ('[MTEntryKeywords encode_php='q'] [MTEntryTitle encode_php='q']') section of the code. Is that missing? or does it check Excerpts some other way? I don't read SQL code at all, but this seems like it's checking only titles and keywords, not excerpts (though it wants to with the entry_excerpt). And if I were daring and wanted to check within MTEntryBody (using the score > # adjustment), would I just add [MTEntryBody encode_php='q'] inside both AGAINST parenthesis? Would I also need to add a entry_body thing inside the MATCH parenthesis? My solution may be to just start using excerpts and keywords (and have the blog's other authors do it too), but I thought I'd give this a try first (as a way of including the old posts). Thanks a lot.

Trackback from Sundown
January 12, 2004 6:03 AM

Related Entries with MTSQLEntries

Excerpt: Results are pretty good and will continue to get better if I can remember to be diligent about keywords.

Junjan
October 19, 2004 10:52 AM

Hi there, I have a problem with your plugin. I have installed it as described and it works, but it always shows up only one result and it is the very same entry. Any idea what can be the problem?

TJ
October 20, 2004 1:45 AM

Hi, congratulations to this wonderful "plugin". It's a great help! One thing we couldn't figure out - how can we alter the statement to search for realted entries in all blogs? We used "...AND entry_blog_id in (8,9,10)..." instead of the existing entry_blog_id statement but it still only produces matches from one weblog? What's the problem? All teh best, Torsten

Amol Hatwar
November 17, 2004 1:19 PM

I was wanting to add a related entry list to my blog as well. I knew things could be done this way, but if the pages were made static, older posts wouldn't be able to link to newer ones. Or you'd have to regenerate all static files at every post (too much over-head). It seems to me that Simon's way is right. I wonder why he'd get MySQL choke-ups but. The function mysql_pconnect() was reported to be buggy.

Lauren Noelle
December 7, 2004 8:47 PM

I have the same problem as Junjan: "it always shows up only one result and it is the very same entry." Well, that happened on some entries, in at least one, there are none (when there should be more, I checked ones that should have lots).

Trackback from eclecticism
January 8, 2005 2:22 PM

Progress: Related Entries

Excerpt: The keyword index will work, but I've got a lot of work to do on my keywords before I can bring it live. In the meantime, I've re-implemented Adam Kalsey's 'related entries' code, listing five similar entries in the sidebar of each individual entry page.

Trackback from Mehh? Mehh!
August 2, 2005 1:16 PM

Useful site for MT users

Excerpt: An extremely useful site I came across while installing pluggins for movable type is Adam Kelseys blog which contains such beauties as the related entries plugin which I'm attempting to implement right now (had it working am just trying to...

Subbu Allamaraju
October 17, 2005 8:45 PM

Thanks for this idea. I tried a few alternatives, and your solution is the most elegant for displaying related entries.

Scott Johnson
May 17, 2006 12:07 PM

I realize that I'm about two years behind the curve on this one, but I just implemented this great little hack on my blog today. Very nice!

Sammy
May 23, 2006 8:12 AM

Is this plugin compatible with WordPress?

Scott Johnson
June 29, 2006 12:44 AM

Now that I have been using this query in my MT individual archives for a bit over a month, I have found one shortcoming. See this page for an example: http://full-speed.org/archives/2006/06/13/inaudible_ring_tones.php (Adam, feel free to delete that link once you've checked it out.) Basically, when the query doesn't find related entries, nothing is shown. I would really like to fix that. Whether it be a simple message that says that there were no related entries found or (preferably in my case) displaying some less relevant links. I have dealt with MySQL quite a bit, but this FULLTEXT stuff is new to me. And MTSQL is something that I have only used in a copy & paste manner. Any ideas?

Scott Johnson
June 29, 2006 12:54 AM

Sorry for so many postings here, but I have been hacking up your query a bit and have a result that at least gives me _something_. It's far from perfect at this point, but I now have _some_ links displaying, however irrelevant. What I did was replace MTEntryKeywords (a field that was returning an empty string for my blog postings) with MTEntryExcerpt in two places in the query. Like I said, it's not perfect, but at least I have some links now. Back to hacking that query... :)

amir
July 22, 2006 1:50 AM

hi there is that possible to we find the re;ated entries of one post, from other blogs entries? HOW??!!! How we should change the code? please mail me the method .. thanks

This discussion has been closed.

Follow me on Twitter

Best Of

  • Rounded corners in CSS There lots of ways to create rounded corners with CSS, but they always require lots of complex HTML and CSS. This is simpler.
  • The mouse and me Not only is the mouse very destructive, but it's evaded all attempts to capture or kill it so far.
  • Embrace the medium The Web is different than print, television, or any other medium. To be successful, designers must embrace those differences.
  • Let it go Netscape 4 is six years old.
  • Newly Digital Newly Digital is an experimental writing project. I've asked 11 people to write about their early experiences with computing technology and post their essays on their weblogs. So go read, enjoy, and then contribute. This collection is open to you. Write up your own story, and then let the world know about it.
  • More of the best »

Recently Read

Get More

Subscribe | Archives

Recently

Product Manager Career Ladder (Aug 19)
What are the steps along the product management career path?
Building the Customer-Informed Product (Aug 15)
Strong products aren't composed of a list of features dictated by customers. They are guided by strong visions, and the execution of that vision is the primary focus of product development.
Assumptions and project planning (Feb 18)
When your assumptions change, it's reasonable that your project plans and needs change as well. But too many managers are afraid to go back and re-work a plan that they've already agreed to.
Feature voting is harmful to your product (Feb 7)
There's a lot of problems with using feature voting to drive your product.
Encouraging 1:1s from other managers in your organization (Jan 4)
If you’re managing other managers, encourage them to hold their own 1:1s. It’s such an important tool for managing and leading that everyone needs to be holding them.
One on One Meetings - a collection of posts about 1:1s (Jan 2)
A collection of all my writing on 1:1s
Are 1:1s confidential? (Jan 2)
Is the discussion that occurs in a 1:1 confidential, even if no agreed in the meeting to keep it so?
Skip-level 1:1s are your hidden superpower (Jan 1)
Holding 1:1s with peers and with people far below you on the reporting chain will open your eyes up to what’s really going on in your business.

Subscribe to this site's feed.

Contact

Adam Kalsey

Mobile: 916.600.2497

Email: adam AT kalsey.com

Twitter, etc: akalsey

Resume

PGP Key

©1999-2019 Adam Kalsey.