• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

related topics/articles...etc

Hi experts,
When viewing an article in most of the websites, we can see a list of "related" topics to the one we read. My question is: how can I make an effective "real" related topics list as they do? What do I need? How do I search the DB?

For you to know, I have made a field in the articles table called "key_words". The admin should add key words with / between each one. Later, I explode that field and do a seach using LIKE method. But the result was horrible.

Best Regards,
0
Shopies
Asked:
Shopies
  • 3
  • 2
1 Solution
 
Ray PaseurCommented:
" the result was horrible. " could mean a lot of things.  One of the things might indicate that the admin did not add keywords very accurately or (more likely) added too many keywords.  The concept of keyword-tagging is the right way to go, so you're moving in the best direction.  

Here is what I would consider.  Instead of a field called key_words, create a table called keywords.  Each row in the table has ONE keyword and an article number with its publication date.  To find the related articles, you would select from the keywords table, choosing the articles that appeared the most frequently (or that had the most recent publication date).  

HTH, ~Ray
0
 
ShopiesAuthor Commented:
Assuming that two words occured in two different articles, Both articles will appear twice as they match the same words.!! Unless you add one word for each article and this is not practical. And example to my point would be adding two articles, say art1 and art2 and both articles have the key words "plane" and "moon" then those articles will occure twice since they have both key words in the key_words table.
Is it possible to bring an article just once even if there were multi matches?
0
 
Ray PaseurCommented:
"Assuming that two words occured in two different articles, Both articles will appear twice as they match the same words.!!"

No, we would not suggest doing that.

"Is it possible to bring an article just once even if there were multi matches?"

Yes, of course.  You can use MySQL to do a lot of things and in this case it might be a good idea to GROUP the results or to SELECT DISTINCT.  Probably the GROUP would offer better performance, but with a reasonable LIMIT you might not see much difference.  It is a good idea to EXPLAIN every query if performance is an issue.  

The logic might be something like this... Select all articles that have both "plane" and "moon" and are very recent - assign these articles first priority.  Then select all articles that have both "plane" and "moon" but are older - assign these articles second priority.  Then select all articles that have "plane" or "moon" and assign these articles third priority.  Then select DISTINCT ordered by priority.  You'll probably get a fairly good results set.

Best, ~Ray

0
 
ShopiesAuthor Commented:
Great explaination now it's more clear to me. Am I asking too much to request a MySQL example for that to learn from it? I just want to see how to looks then I'll fet it to my needs..
 
Regards,
0
 
Ray PaseurCommented:
I don't have time this month to write and test the code (although it would be useful to have in my library), but I'll try to give a better explanation.  I've been thinking about this a bit, that the temporary table might be a useful thing to keep performance good here, but it might also be good to do this with an associative array where each article_id gets a score.

The UNTESTED code below would assign a score of 3, 2, or 1 to each article_id.  A 3 would mean a recent article with both words.  A 2 would mean a recent article with one of the words.  A 1 would mean an article that is not too old, with either of the words.  Not exactly what I envisioned earlier, but probably serviceable.

Hope that helps, ~Ray


<?php // RAY_temp_shopies.php
error_reporting(E_ALL);
 
// ARTICLE KEYWORDS WOULD COME FROM EXTERNAL INPUT
$kw1 = 'moon';
$kw2 = 'plane';
 
// THE DATES FOR RECENT ARTICLES
$now  = date('c');
$then = date('c', strtotime('One Week Ago'));
$old  = date('c', strtotime('One Year Ago'));
 
// A LIMIT ON THE QUERY RESULTS
$lim = "100";
 
// ARRAY FOR AGGREGATING THE SCORES FROM QUERY RESULTS
$arts = array();
// A CLEVER MySQL PROGRAMMER MIGHT FIND A WAY TO COMBINE THESE QUERIES?
 
// A QUERY TO FIND THE RECENT ARTICLES WITH ONE WORD
$sql  = "SELECT article_id, article_word FROM article_keywords WHERE ";
$sql .= "(article_date BETWEEN \"$now\" AND \"$then\") ";
$sql .= "AND (article_word = \"$kw1\") ";
$sql .= "ORDER BY article_date DESC LIMIT $lim";
$res  = mysql_query($sql) or die( mysql_error() );
 
// GO THROUGH THESE RESULTS INCREMENTING A COUNTER FOR EACH ARTICLE ID
while ($row = mysql_fetch_assoc($res))
{
   extract($row);
   if (empty($arts["$article_id"])) $arts["$article_id"] = 0;
   
// ADD ONE TO SCORE FOR RECENT ARTICLE   
   $arts["$article_id"]++;
}
 
// A QUERY TO FIND THE RECENT ARTICLES WITH OTHER WORD
$sql  = "SELECT article_id, article_word FROM article_keywords WHERE ";
$sql .= "(article_date BETWEEN \"$now\" AND \"$then\") ";
$sql .= "AND (article_word = \"$kw2\") ";
$sql .= "ORDER BY article_date DESC LIMIT $lim";
$res  = mysql_query($sql) or die( mysql_error() );
 
// GO THROUGH THESE RESULTS INCREMENTING A COUNTER FOR THE ARTICLE ID
while ($row = mysql_fetch_assoc($res))
{
   extract($row);
   if (empty($arts["$article_id"])) $arts["$article_id"] = 0;
   
// ADD ONE TO SCORE FOR RECENT ARTICLE   
   $arts["$article_id"]++;
}
 
// A QUERY TO FIND ALL ARTICLES WITH EITHER WORD
$sql  = "SELECT article_id, article_word FROM article_keywords WHERE ";
$sql .= "(article_date > \"$old\") ";
$sql .= "AND (article_word = \"$kw1\" OR article_word = \"$kw2\") ";
$sql .= "ORDER BY article_date DESC LIMIT $lim";
$res  = mysql_query($sql) or die( mysql_error() );
 
// GO THROUGH THESE RESULTS INCREMENTING A COUNTER FOR THE ARTICLE ID
while ($row = mysql_fetch_assoc($res))
{
   extract($row);
   if (empty($arts["$article_id"])) $arts["$article_id"] = 0;
 
// ADD ONE TO SCORE FOR A MATCH ON EITHER WORD      
   $arts["$article_id"]++;
}
 
// SORT THE RESULTS
arsort($arts);
 
// SHOW THE RESULTS
foreach ($arts as $article_id => $article_score)
{
   echo "<br/>$article_id $article_score \n";
}

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now