Show number of posts in a category

[Availor]

Hello,

I know it's possible to do as I've seen it on different WB websites. I would like to post my news categories in the side menu, and show how many posts each category has.

For example.

Cars (4)
Boats (2)
templates (5)

Each category is the news category, and each post is the post inside the news category.

This way it's possible to create a more blog-like website.

I hope someone has or can make this code snippet I bet it will be useful for others as well.

Availor.

[doc]

Hello,

check out the snippet anynews from the addons repository. Use this as starting point and modify the SQL stuff to what ever you need.

Christian

[Availor]

I already have and use this snippet, but it only lets me show a certain category from the news, but not show the amount of posts in each category. I don't know how to alter the code to show the number of posts in each cat.

[marathoner]

You can accomplish this by either doing the count in the query or return all rows and write a PHP loop to do the counts.

Here's how to count in MySQL
http://dev.mysql.com/doc/refman/5.0/en/counting-rows.html

[Availor]

Thanks for the link, but my coding skills are somewhat lacking...

As I've mentioned before I have seen websites made by WebsiteBaker do this so I was hoping maybe the person who made it would see my post in the forum and paste the code here.
I don't know how to use this MySQL code.

The more I work with it, the more I realise WB can be made into a portal system with just several snippets added.
I've already managed to create small portals with different news categories being shown in different sections using the anews module. Showing the number of posts in each category can make it as a blog system together with being a cms.

Anyway, if anyone knows and is willing to post the code snippet I'll be happy to input it into my templates. I've actually wanted to create a blogging template like this http://www.revolutiontheme.com/

It's pretty simple for WB and no need to pay 79$ for each template (it's for wordpress).

http://www.revolutiontheme.com/realestate/ - is a wordpress template.
http://www.websitebakerden.com/demo/portal/pages/business1.php and this is a preview of a template I'm creating for WB. I want it to look similar, but not exactly the same.

Anyhow you get my point about the code snippet.

Cheers 

[pcwacht]

Some rough code to help you get started - mind you, this was written more then one year ago for tyhr MEDIABLOG not for the news module but by changing the databasetables and fields it will get you the same stuff for the news module


Paste the code in a code section..... and change the pageid to match your news pageid!

Code:

// Change page_id to match your situation
$page='37';

$database = new database();
// get page link for news
$query = "SELECT * FROM ".TABLE_PREFIX."pages WHERE `page_id`= '$page' ";
$resultpage = $database->query($query);
$page_record=$resultpage->fetchRow();
$groups_page_link = $wb->page_link($page_record['link']);

// get group data
$query = "SELECT count(i.group_id) AS total, i.group_id, g.title FROM ".TABLE_PREFIX."mod_mediablog_items AS i, ".TABLE_PREFIX."mod_mediablog_groups AS g WHERE i.page_id = '$page' AND i.active = '1'  AND i.group_id = g.group_id  GROUP BY i.group_id ORDER BY g.title";
$result = $database->query($query);

// If there is output
if($result && $result->numRows() > 0) {
  // Loop through groups
  while($group = $result->fetchRow()) {
    // Build the output
    echo '<a href = "'.$groups_page_link.'?g='.$group['group_id'].'">'.$group['title'].'</a> &nbsp; (<b>'.$group['total'].'</b>)<br/>';
  }
} else {
    echo 'No Categories Found';
}

Next is an archive list, does something simular but shows posts per month
Again ... Paste the code in a code section..... and change the pageid to match your news pageid!

Code:

// Change page_id to match your situation
$page='37';

// Fetch link to this page
$link=$wb->link;

$months = Array (1=>"Januari",
                 2=>"Februari",
                 3=>"Maart",
                 4=>"April",
                 5=>"Mei",
                 6=>"June",
                 7=>"July",
                 8=>"August",
                 9=>"September",
                 10=>"October",
                 11=>"November",
                 12=>"December");

if ($_GET['year']<>0 AND $_GET['month']<>0) {
  $year = $_GET['year'];
  $month = $_GET['month'];
  echo 'Year '.$year.' Month '.$month.'<hr>';
  $database = new database();
  // Get the posts
  $query = "SELECT link,title,content_short FROM ".TABLE_PREFIX."mod_mediablog_items WHERE page_id= '$page' AND active = '1' AND FROM_UNIXTIME( itemed_when, '%m' ) = $month AND FROM_UNIXTIME( itemed_when, '%Y' ) = $year ORDER BY itemed_when DESC" ;

  $result = $database->query($query);

  // If there is output
  if($result->numRows() > 0) {
    // Loop through items and build output
    while($item = $result->fetchRow()) {
      echo '<a href = "'.WB_URL.'/pages'.$item['link'].PAGE_EXTENSION.'">'.$item['title'].'</a><br>'.$item['content_short'].'<br/><br />';
    }
  }

  } else {
  $database = new database();
  // Get the posts
  $query = "SELECT YEAR(FROM_UNIXTIME( itemed_when)) AS `year`, MONTH(FROM_UNIXTIME( itemed_when)) AS `month`, count(item_id) as posts FROM ".TABLE_PREFIX."mod_mediablog_items WHERE page_id= '$page' AND active = '1' GROUP BY YEAR(FROM_UNIXTIME( itemed_when)), MONTH(FROM_UNIXTIME( itemed_when)) ORDER BY itemed_when DESC" ;

  $result = $database->query($query);

  // If there is output
  if($result->numRows() > 0) {
    // Loop through items and build output
    while($item = $result->fetchRow()) {
      echo '<a href = "'.$link.'?year='.$item['year'].'&month='.$item['month'].'">'.$item['year'].' - '.$months[$item['month']].' <strong>('.$item['posts'].')</strong></a><br>';
    }
  }
}

as allways,

have fun!
John

[Availor]

Thanks a lot! It works fine . Unfortunately the mediablog module is buggy and it doesn't seem to work with anews module. Maybe I can put it to use for a blog.

Thank you for the code snippet

[pcwacht]

This code is to help you get started

as I wrote above :

but by changing the databasetables and fields it will get you the same stuff for the news module

Just change the databasetable name to news and change the used fileds to reflect the news table

It'll work

John

[Availor]

Well, I will have to work on it, since as I've mentioned before, I don't know how to do that.
My knowledge in MySQl and php are very basic. I'm a designer not a programmer.

My assumption is;

Code:

SELECT count(i.group_id) AS total, i.group_id, g.title FROM ".TABLE_PREFIX."mod_mediablog_items AS i, ".TABLE_PREFIX."mod_mediablog_groups AS g WHERE i.page_id = '$page' AND i.active = '1'  AND i.group_id = g.group_id  GROUP BY i.group_id ORDER BY g.title";

The line "mod_mediablog_items" is the database id, so I have to find The news database id and just paste the news database name - I'll have to install another WB testing site so I don't mess up my database.

[pcwacht]

advice

Use XAMPP (www.apachefriends.d e) if you run windows
Then you can get a local install, install wb there and start playing with codes etc

The mediablog module (wich was originaly written by me) is just a copy of the news module with some additions.
Therefore I know it'll work


Good luck,
If you have questions just pm me or post them here.
I won't look every 5 minutes, but am here regurly, allmost daily.

Have fun!
John

[Availor]

Thanks a lot, I have wamp installed which is similar to xamp I guess...

[melissa]

Thanks a lot John (pcwacht) - I had the same question and your answer was enough to get me over the line.

For the standard News Module (paste into code module):

Code:

// The following code generates a list of the article groups and the number of articles.

$page='6'; //modify to your own page id

$database = new database();
// get page link for news
$query = "SELECT * FROM ".TABLE_PREFIX."pages WHERE `page_id`= '$page' ";
$resultpage = $database->query($query);
$page_record=$resultpage->fetchRow();
$groups_page_link = $wb->page_link($page_record['link']);

// get group data
$query = "SELECT count(i.group_id) AS total, i.group_id, g.title FROM ".TABLE_PREFIX."mod_news_posts AS i, ".TABLE_PREFIX."mod_news_groups AS g WHERE i.page_id = '$page' AND i.active = '1'  AND i.group_id = g.group_id  GROUP BY i.group_id ORDER BY g.title";
$result = $database->query($query);

echo '<hr />Categories:<br />';

// If there is output
if($result && $result->numRows() > 0) {
  // Loop through groups
  while($group = $result->fetchRow()) {
    // Build the output
    echo '<a href = "'.$groups_page_link.'?g='.$group['group_id'].'">'.$group['title'].'</a>&nbsp; ('.$group['total'].')<br/>';
  }
} else {
    echo 'No categories found.';
}
echo '<br /><br />';

[cnwb]

More advise

Give Vertrigo a try.
I seem to like the over all ease of use
with vertrigo over XAMPP or wamp.
Been using it now for over a year.

Very easy to use!!

JP