Query Related Articles From DB
For Blogs and Such
Posted by Charlie Recksieck
on 2022-12-01
If you want to see an example, just look at the lower right on this very page (or scroll to the very bottom if viewing on mobile phone or tablet).
Basic Searching
You might think that it takes a very sophisticated algorithm or multi-faceted query to pull off a quality search of tables of articles. In reality, you really do get great results by a text search.
For instance, in this very article you're reading right now, we are talking mainly about searching a database, PHP code (this site is PHP-driven), and related posts. Obviously, we would want what you see on those suggested links at the bottom of our menu in "Related Articles" to be along those lines. If we're seeing suggestions for our articles about GPS or supply-chain issues or advertising, then our engine for suggestions hasn't done its job.
Again, simply searching text fields in our content database can really do the job. If we did a basic query of our article body and article description fields for the word "search", that's really going to do most of the work for us. We don't need to have our code be too impossible.
Define The Most Important Word
For us, we found that a real key was for each article in our blog to have one key word as the crux of what the article was about.
Let's say an article was about search engines. We don't want to be overly specific by making our key phrase be "search engine" because it would require a less likely sequence of text to be found, whereas if our key phrase was just "engine" or "search", then we'd get more possible results - which is a good thing.
Look at a few records of this blog's content table here. The first field shown is our "one key phrase" field.
When we search for that "one key phrase" appearing in our body, description, title and tag fields then it organically is going to show up where it matters.
Pro tip: If a word can have a few forms like "advertising" vs. "advertise", then you should make your one key phrase be something like "advertis" which is inclusive enough for find all forms of the word advertising, advertisements, advertise, etc.
The Query
Here's our query looking at several fields:
As you can see, we total up the appearances of our key word/phrase in several fields: the article body, title, subtitle, description and tags. If that article appears something like 8 times in there, we've got some overlap. It's that easy.
Now, let's say our key word is a little unusual or arcane and unlikely to give a lot of results. If the key word is "juggling" then we probably won't find much. In that case, we then want a next-level of search to at least come up with something. Here, we are at least then going to give some results from the same blog category. So, if the article is about PHP code, then our "related articles" links won't be about social media.
That's pretty much it. Once we have our PHP recordset of results, we have them sorted by "score" (a query-created variable of how many times the keyword appears) and can list the title and link. Voila.
Other Voices
If you'd like to read a little further about this, this StackOverflow good Samaritan has written some helpful code on this.
WordPress - And there are some good commands and tools in WordPress to help you pull this off: Read here
Happy To Help
If you want a quick free consult or advice about this, we're always happy to help so don't be afraid to reach out to us.
Otherwise, happy searching.