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: [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

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.