Related Records?

34 posts by 6 authors in: Forums > CMS Builder
Last Post: July 24, 2012   (RSS)

By ILLUME-MEDIA-LABS - August 9, 2010

Hi, was wondering how I can add a 'related news' section so when reading an article, there would be a block in the sidebar which will list..say 5 related articles...based on keywords within the article.
Thanks.

Re: [Illume Magazine] Related Records?

By Chris - August 9, 2010

Hi Illume Magazine,

Wow, that's a tricky problem.

The simplest solution I can imagine would be to use a category system and show recent articles in the same category. You don't necessarily have to make your categories visible to the user.

If that doesn't fit your needs, I can imagine a solution which relies upon a "keywords" field, looking for other articles which share one or more keywords.

Writing a solution which would automatically grab keywords from the article text would be pretty complicated: how would the server know that "the" isn't a great keyword to use? This would be a fairly difficult custom programming problem to solve!

Does any of this help? Please let me know if you have any questions.
All the best,
Chris

Re: [chris] Related Records?

By ILLUME-MEDIA-LABS - August 9, 2010

Hi chris. That's what I meant. We already have a keywords field. What would the query look like that compares that field with existing articles and returns 5 most recent ones that match. Thanks.

Re: [chris] Related Records?

By ILLUME-MEDIA-LABS - August 11, 2010

Thanks. When I added your code, and change the field names to match whats in my cms, I got this error:

MySQL 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 ') AND am_articles.hidden = 0 ORDER BY date desc LIMIT 5' at line 3

Re: [Illume Magazine] Related Records?

By Chris - August 11, 2010

Hi Illume Magazine,

Hmm.

Try printing out $where before the getRecords() call:

showme($where);

...and/or asking getRecords() to show you the SQL it's generating:

// find the most recent records with at least one matching keyword (but not including the current record)
$similarRecords = getRecords(array(
'tableName' => 'articles',
'where' => "(" . $where . ") AND num != " . $record['num'],
'limit' => 5,
'debugSql' => true,
));

What does that show?


P.S. I also made a slight correction to the code above. I made it blue in the previous post.
All the best,
Chris

Re: [chris] Related Records?

By ILLUME-MEDIA-LABS - August 28, 2010 - edited: August 28, 2010

Hi, Chris...

So I finally got around to working on this. This is the modified code with my column added.

// construct a where clause out of this record's keywords (e.g. "apples, oranges" -> "keywords LIKE '%apples%' OR keywords LIKE '%oranges'")
$keywords = preg_split('/,\s*/', $articlesRecord['tag_cloud_multiple_keywords']);
$where = '0';
foreach ($keywords as $keyword) {
$where .= ' OR ';
$where .= "tag_cloud_multiple_keywords LIKE '%" . mysql_escape($keyword) . "%'";
}

// find the most recent records with at least one matching keyword (but not including the current record)
$similarRecords = getRecords(array(
'tableName' => 'am_articles',
'where' => "(" . $where . ") AND num != " . $articlesRecord['num'],
'limit' => 5,
));

To pull the info..I use

<?php foreach ($similarRecords as $record): ?>
<a href="<?php echo $record['_link'] ?>"><?php echo $record['title'] ?></a><?php endforeach ?>

But the result just shows a linked word 'Array' that links back to the article. See example: http://www.illumemag.com/zine/articleDetail1.php?Gates-Applauds-Khan-Academy-s-Ingenuity-13259 (just below the pull quote)

Re: [Illume Magazine] Related Records?

By Perchpole - August 29, 2010

Hi, Illume -

Look for the code in red...

// construct a where clause out of this record's keywords (e.g. "apples, oranges" -> "keywords LIKE '%apples%' OR keywords LIKE '%oranges'")
$keywords = preg_split('/,\s*/', $articlesRecord['tag_cloud_multiple_keywords']);
$where = '0';
foreach ($keywords as $keyword) {
$where .= ' OR ';
$where .= "tag_cloud_multiple_keywords LIKE '%" . mysql_escape($keyword) . "%'";
}

// find the most recent records with at least one matching keyword (but not including the current record)
$similarRecords = getRecords(array(
'tableName' => 'am_articles',
'where' => "(" . $where . ") AND num != " . $articlesRecord['num'],
'limit' => 5,
));

...and change it to this:

list($similarRecords)

Should work OK now.

:0)

Perch

Re: [Perchpole] Related Records?

By Chris - August 30, 2010

Hey, good catch, Perchpole! Thanks! :D

Did that get things sorted out for you, Illume? Please let me know if you have any questions.
All the best,
Chris

Re: [chris] Related Records?

By ILLUME-MEDIA-LABS - August 30, 2010

Yes. this works great. Thank you!

Will this also work if there are several keywords in the field? Some may be separated by a comma, and some by a space.

Thanks again.