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: [Illume Magazine] Related Records?

By Chris - August 10, 2010 - edited: August 11, 2010

H Illume Magazine,

How about this?

// 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*/', $record['keywords']);
$where = '0';
foreach ($keywords as $keyword) {
$where .= ' OR ';
$where .= "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' => 'articles',
'where' => "(" . $where . ") AND num != " . $record['num'],
'limit' => 5,
));

You'll likely need to change the name of the record variable and table name above (in red) to match your own project.

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

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