WSC - Top 10 Records with Comments

6 posts by 2 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: April 1, 2015   (RSS)

By Perchpole - March 31, 2015

Hi, All -

I'd like to generate a top 10 list of records ordered by the number of comments they have received. For example...

Record Five (12)
Record Seven (11)
Record Ten (6)
Record One (4)
etc...

The number of comments is shown in the brackets.

I'm sure I could do this with query but would like to use getRecords calls if possible.

Do-able?

:0/

Perch

By Perchpole - April 1, 2015

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

By gregThomas - April 1, 2015

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

Greg Thomas







PHP Programmer - interactivetools.com

By Perchpole - April 1, 2015

Greg -

Brilliant. It works perfectly.

I've always believed that this kind of operation is quite heavy in terms of the load it puts on page rendering times. Is that right?

Thanks again,

Perch

By gregThomas - April 1, 2015

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

Greg Thomas







PHP Programmer - interactivetools.com