Retrieving Data from MySQL and Displaying in Main Content Area

Answers

• Kasper Scholar of the Bits Copenhagen Vanilla Staff

  February 2013 Answer ✓

  @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;
  
  }
  

  Kasper Kronborg Isager (kasperisager) | Freelance Developer @Vanilla | Hit me up: Google Mail or Vanilla Mail | Find me on GitHub

  5
• manish29 New

  February 2013

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

  0
• Kasper Scholar of the Bits Copenhagen Vanilla Staff

  February 2013

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

  Kasper Kronborg Isager (kasperisager) | Freelance Developer @Vanilla | Hit me up: Google Mail or Vanilla Mail | Find me on GitHub

  1
• manish29 New

  February 2013

  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?

  0
• x00 MVP

  February 2013

  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.

  grep is your friend.

  0
• manish29 New

  February 2013

  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.

  0
• x00 MVP

  February 2013 edited February 2013

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

  grep is your friend.

  0
• x00 MVP

  February 2013 Answer ✓

  note he has already put a way you can leave Base_

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

  either way.

  grep is your friend.

  5
• manish29 New

  February 2013

  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?

  0
• x00 MVP

  February 2013 Answer ✓

  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');
  

  grep is your friend.

  5
• manish29 New

  February 2013

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

  2