Related articles and tags

So, anyone have any ides, or examples of how they have implemented tagging?

I guess my two biggest hangups are the actual tagging/category system in the admin.  What are people using?  Multi-select lists?  Free form text fields?

And secondly, once I do have tagging implemented....what's the best way to 1) get the list of tags for the current shown article.....and then 2) search those tags across MULTIPLE sections in the CMS.  The client wants the related articles to come from multiple sections.  I'm guessing what I'd have to do is perform multiple queries, somehow storing the matching article numbers and what section they are in.  Then, to make sure it's not always showing the current related articles from the first section, take those numbers, randomize them, and then pull the top 5 to show on the page.

Any ideas?

-Kevin

Hi Kevin, 

To me it sounds like the biggest issue will be the quantity of data that can be related. If any record can be related to any other record on the site, then however you create system, you're going to have a lot of check boxes/ large drop down lists in one place.

I would go with your first suggestion, and create a section that is used for creating related records, I would personally go with check boxes, as people can struggle with using multi selection lists, especially when they contain a lot of data. 

I would create a field like this: http://www.interactivetools.com/kb/article.php?Populate-a-list-field-from-another-section-15 for each section. If you use the num value for each section as the value for your checkbox field, and use the title field for the label you might be able to get away with making only one more get Records call to retrieve all of your related records. Maybe something like this:

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
  /* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
  
  // load viewer library
  $libraryPath = 'cmsAdmin/lib/viewer_functions.php';
  $dirsToCheck = array('C:/wamp/www/test/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

  // load record from 'blog'
  list($blogs, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $blog = @$blogs[0]; // get first record
  if (!$blog) { dieWith404("Record not found!"); } // show error message if no record found

  // load records related to the blog
  list($relatedArticles, $blogMetaData) = getRecords(array(
    'tableName'   => 'related_blogs',
    'where'       => "`blog_related` LIKE '%\t{$blog['num']}\t'%",
    'loadUploads' => false,
    'allowSearch' => false,
  ));


This is just example code, so you'll have to modify it to work with your pages.

So this page is displaying a record from the blog section. The first getRecords function gets the blog the record that is going to be displayed on the page. Then the second getRecords function calls the related_records section, which contains all of the related fields for the different sections.For example a field called related_blogs for the blog section, related_news for a new section, related_contact for the contact page. So this getRecords function would grab records that have the current num value for the blog record checked in the related_blogs field.

I hope that all makes sense!

Thanks

Greg

Sorry about my rambling....but let me say this:

Let's assume they are going to have a manageable list of tags (under 20).  And I have those as checkboxes, pulling from another section in the CMS.  So for each record they can select up to 20 tags.  Ok, I have this working fine.  I'm also able to construct the tags where clause using the following code:

$where = "";
$articleCategories = array();


$articleCategories = explode("\t", trim($detailRecord['tags'], "\t"));

foreach ($articleCategories as $category) {
$where .= " tags LIKE '%\t$category\t%' OR";
}

$where = rtrim($where, "OR"); //remove the last "OR"

Ok this works.  Now I guess my question would be.....what is the best way to use that where clause.....to query across multiple tables.....getting a master list of related articles.  Then randomizing the list, and picking 5.  Then listing the title and snippet from each with a link to the actual article.

Phew.....out of breath after that one.

-Kevin

So I'm going with a multi-select list for the keywords.

Then what I'm going to do is run it through a function to grab the related articles.  This is the code I'm using for 2 of my sections.....I'll add more later:

function getRelatedArticles($detailRecord) {

$where = "";
$articleKeywords = array();

$articleKeywords = explode("\t", trim($detailRecord['keywords'], "\t"));

foreach ($articleKeywords as $keywords) {
$where .= " keywords LIKE '%\t$keywords\t%' OR";
}

$where = rtrim($where, "OR"); //remove the last "OR"
$where = "(" . $where . ")";
$where .= " AND num != '" . intval($detailRecord['num']) . "'";


list($relatedArticle, $relatedArticlesMetaData) = getRecords(array( 
'tableName' => 'ask_a_consultant', 
'allowSearch' => false, 
'where' => $where,
'debugSql' => false,
));

list($relatedArticle2, $relatedArticlesMetaData2) = getRecords(array( 
'tableName' => 'legislative_update', 
'allowSearch' => false, 
'where' => $where,
'debugSql' => false,
));

$relatedArticles = array_merge($relatedArticle, $relatedArticle2);

// Shuffle the array to randomize records
shuffle($relatedArticles);

return $relatedArticles;

}


So you can see I basically grab the list of related articles and put them each in an array.  Then I merge the arrays.  Then to randomize the output....I shuffle the arrays.

After that, I can return the array to my page where this code put the top 4 in a list:

$max_loop=4;
$count = 0;
foreach ($relatedArticles as $relatedArticle) {
if ($relatedArticle['_tableName'] == 'ask_a_consultant') {
echo "<li><a href=\"" . $relatedArticle['_link'] . "\"><b>" . $relatedArticle['question'] . "</b><br />" . textTrim($relatedArticle['answer'], 100, 'notags', '', '') . "</a></li>";

} else {

echo "<li><a href=\"" . $relatedArticle['_link'] . "\"><b>" . $relatedArticle['title'] . "</b><br />" . textTrim($relatedArticle['summary'], 135, 'notags', '', '') . "</a></li>";
}

$count++;
if($count==$max_loop) break;
}


Because each section has a different field name for it's title and article text, you can see I do a check to see which section I'm in and format it accordingly.

I'll be expanding this to include other sections....right now I'm testing with 2.

So far so good....seems to be working.  No doubt it's not optimized.....but I'll look into that later.

Also, I do a check AFTER the function call to get the related articles:

if (empty($relatedArticles)) { $recentArticles = getRecentArticles($detailRecord); }

Basically if there were no related articles returned, go back and run a similar function to get the most RECENT articles:

function getRecentArticles($detailRecord) {

list($recentArticles, $relatedArticlesMetaData) = getRecords(array( 
'tableName' => 'ask_a_consultant', 
'allowSearch' => false, 
'limit' => 5,
'where' => "num != '" . intval($detailRecord['num']) . "'",
'orderBy' => 'createdDate DESC', 
'debugSql' => false,
));

list($recentArticles2, $relatedArticlesMetaData2) = getRecords(array( 
'tableName' => 'legislative_update', 
'allowSearch' => false, 
'limit' => 5, 
'orderBy' => 'createdDate DESC', 
'debugSql' => false,
));

$recentArticles = array_merge($recentArticles, $recentArticles2);
usort($recentArticles, 'date_sort');

return $recentArticles;

}

This again is the best I can come up with to go across multiple sections.  I then take that merged array and run it through a sort function to get them in the correct order:

function date_sort($a, $b) {
    $t1 = strtotime($a['date']);
    $t2 = strtotime($b['date']);
    return $t2 - $t1;
} 


I then using a similar foreach loop to show the latest 4 most recent articles.

-Kevin

Hi Kevin,

We've just been discussing this problem in our office when you replied. The method your using is probably the simplest, and what we were going to suggest. 

If you wanted something that would be quicker to load on the page you could create a CRON plugin that preloads all of the related records into a separate section  every time a record is saved or deleted in CMS, then you'd just have to get the related keyword links from that table. If you send an e-mail to consulting@interactivetools.com we can give you an estimate of the cost of creating the plugin if you require. 

Let me know if you have any questions.

Thanks!

Greg

Hi Greg,

Wow.....good to know I did something right!

I'll run that by the client and see if they want us to submit that request.  Right now things are working pretty quickly.

Looking at my code......anything pop out that you would do differently to optimize things?

-Kevin

Hi Kevin,

In general the code looks good. You could look into setting these variables as false in your getRecords functions:

list($relatedArticle, $relatedArticlesMetaData) = getRecords(array( 
  'tableName'        => 'ask_a_consultant', 
  'allowSearch'      => false, 
  'where'            => $where,
  'debugSql'         => false,
  'loadUploads'      => false,         // optional, defaults to yes, loads upload array into upload field
  'loadCreatedBy'    => false,         // optional, defaults to yes, adds createdBy. fields for created user
  'loadListDetails'  => false,         // optional, defaults to yes, adds $details with prev/next page, etc info
  'loadPseudoFields' => false,      // optional, defaults to yes, adds additional fields for :text, :label, :values, etc
)); 


These values are set to true by default, by setting them to false your getRecords MySQL call will be smaller, and less demanding on the server. Just enable any of the options that you use the data from. 

Cheers

Greg