Retrieving Data from MySQL and Displaying in Main Content Area

manish29

I need help in adding some content to my home page. I've set categories as the home page for my blog. Now I want to manually edit the template and show 10 popular discussions (base on views) above the list of categories. I'll be really glad if someone can guide me on how to achieve this? What query to write and how to get the data to display?

Kasper

@peregrine wrote a plugin that does something like this. This is the snippet you'd be interested in:

public function MostCommented($limit="5") { 

    // change '-7 days' if you desire a different time frame  e.g.  '-30 days'
    $ago = Gdn_Format::ToDateTime(strtotime('-7 days'));
    $MostPopularCModel = new Gdn_Model('Discussion');
    $PopCommentData = $MostPopularCModel->SQL
            ->Select('DiscussionID, Name,CountComments,DateLastComment ')
            ->From('Discussion')
            ->OrderBy('CountComments', 'desc')
            ->Where('DateLastComment >',$ago)
            ->Limit($limit)
            ->Get()
            ->ResultArray();

    return  $PopCommentData;

}

x00

note he has already put a way you can leave Base_

and instead and set $ShowOnController = array('categoriescontroller');

either way.

x00

it doesn't look again discussion and discussions

you can set the module order

$Configuration['Modules']['Vanilla']['Content'] = array('MessageModule', 'Notices', 'NewConversationModule', 'NewDiscussionModule', 'CategoryModeratorsModule', 'MostPopularModule', 'Content', 'Ads');

Kasper

@peregrine wrote a plugin that does something like this. This is the snippet you'd be interested in:

public function MostCommented($limit="5") { 

    // change '-7 days' if you desire a different time frame  e.g.  '-30 days'
    $ago = Gdn_Format::ToDateTime(strtotime('-7 days'));
    $MostPopularCModel = new Gdn_Model('Discussion');
    $PopCommentData = $MostPopularCModel->SQL
            ->Select('DiscussionID, Name,CountComments,DateLastComment ')
            ->From('Discussion')
            ->OrderBy('CountComments', 'desc')
            ->Where('DateLastComment >',$ago)
            ->Limit($limit)
            ->Get()
            ->ResultArray();

    return  $PopCommentData;

}

manish29

@kasperisager this is very helpful. Where should I place this function and how will it display the list? Please help...

Kasper

I'd suggest copying @peregrine's MostPopular plugin and hacking it to suit your needs.

manish29

Ok, I'll try that. Currently if I place the contents of this function in a view file, and try to do Num Rows as $NumRows = $PopCommentData->NumRows();

I get the error: Fatal error: Call to a member function NumRows() on a non-object

Why does that happen?

x00

you are probably should checkout how the discusssion model does it, as the example above only gives a small amount of info and doesn’t join the users.

you would need to make use of a controller hook to do the query, such as CategoriesController_Render_Before and use to pass the data to a view hook

this uses some view hook to insert a view with this in

  <ul class="DataList Discussions">
     <?php include($this->FetchViewLocation('discussions', 'discussions')); ?>
  </ul>

would give you a standard discussion list, note it will make use of ->DiscussionData which is the result of you query, therefore if the controller is already using it for some other resultset, you need to ensure the two resultsets don't get mixed up.

I think that is about all the help I can reasonably give you.

manish29

Thank you so much. I took the MostPopular plugin and that is helpful.

In that plugin, my main confusion now is how to display the data retrieved only on the categories page, which is my home page now, and I want to display it above the list of categories.

Currently the plugin is set to display the data in Panel. I can change AssetTarget to 'Content' which makes the data shift to main area but it will still be on all pages and below the discussions.

x00

note the bit that says Base_ change it to CategoriesController_ in default.php

x00

note he has already put a way you can leave Base_

and instead and set $ShowOnController = array('categoriescontroller');

either way.

manish29

Thank you @x00

I'm going to make all changes per my requirement and post my success here.

And why $ShowOnController array has discussioncontroller twice?

One last question, how to make it show above the categories instead of below?

x00

it doesn't look again discussion and discussions

you can set the module order

$Configuration['Modules']['Vanilla']['Content'] = array('MessageModule', 'Notices', 'NewConversationModule', 'NewDiscussionModule', 'CategoryModeratorsModule', 'MostPopularModule', 'Content', 'Ads');

manish29

Friends, I've successfully implemented this on my website. Thanks for so much support!