WSC - Top 10 Records with Comments

Hi Perch,

Here is how you could display this data on the page, below is some example code using a test blog section I've created.

If you're pulling record from a section like this:

  // load records from 'blog'
  list($blogs, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'loadUploads' => true,
    'allowSearch' => false,
  ));

Then you could display a list of records and the how many comments each one has like this:

<ul>
  <?php foreach($blogs as $blog): ?>
    <li><?php echo $blog['title']; ?> (<?php echo mysql_count('_wsc_comments', "`recordNum` = '{$blog['num']}' AND `tableOrTag` = 'blog'"); ?>)</li>
  <?php endforeach; ?>
</ul>

So the mysql_count function is one that's built into CMS Builder, and is used to count records. The first variable is the table name, and the second is a where statement that you want to use to filter the records.

Cheers,

Greg

Hi, Greg -

Thanks for this. It's almost perfect. The only thing missing is the order. I need to pull out the top 10 most commented blog records and then show them in descending order.

How can I do that?

Thanks,

Perch

Hey Antar,

Unfortunately, adding this extra element increases the complexity of the code significantly. 

You need to write a custom MySQL command that retrieves the number of comments for each blog, and then orders on it. This is done by using a second SELECT statement inside of your current mysql statement.

CMS Builder has a function built into it that allows you to carry out a custom mysql select query and get the results returned in an array:

 showme(mysql_select_query("SELECT num, title, (SELECT COUNT(*) FROM ".$TABLE_PREFIX."_wsc_comments WHERE `tableOrTag` = 'blogs' AND `recordNum` = b.num) AS `countTotal` FROM ".$TABLE_PREFIX."blog as b ORDER BY `countTotal` DESC"));


The MySQL command I created above gets the num, title and total number of comments from the blog section, and orders them by the number of comments they have, you'll have to adapt the code above to work the pages you've created.

Cheers,

Greg

Hi Perch,

That's correct, once you've got a few hundred comments, this will have an impact on your load times.

What I do for this kind of meta data is write a cron plugin that runs every minute, and calculates and stores this meta information against each record itself. This will reduce the load on the server significantly. 

Cheers,

Greg