| View previous topic :: View next topic |
| Author |
Message |
koski
Joined: 22 Jun 2002
Posts: 16
|
 Posted: Wed Aug 28, 2002 12:20 pm Post subject: counting posts by category |
 |
|
sometimes is useful to see how many posts you have in each category.
So, the first step is to code the function that will count the posts:
| Code: | function count_cat_posts($cat_ID) {
global $tableposts;
$query = "SELECT * FROM $tableposts WHERE post_category = $cat_ID";
$result = mysql_query( $query );
return mysql_num_rows( $result );
} |
You can add that function to b2-include/b2template.functions.php
You can see that it only requires a single parameter, the Category ID.
Now make more easy for us.
When listing the categories was very funny that the sistem adds automatically the posts count, but not when in a dropdown.
So let's go.
As easy as adding other paramter to the function 'list_cats' (in b2-include/b2template.function.php):
original function:
| Code: | function list_cats($optionall = 1, $all = 'All', $sort_column = 'ID', $sort_order = 'asc', $file = 'blah') {
global $tablecategories,$querycount;
global $pagenow;
$file = ($file == 'blah') ? $pagenow : $file;
$sort_column = 'cat_'.$sort_column;
$query="SELECT * FROM $tablecategories WHERE cat_ID > 0 ORDER BY $sort_column $sort_order";
$result=mysql_query($query);
$querycount++;
if (intval($optionall) == 1) {
echo "\t<a href=\"$file?cat=all\">$all</a><br />\n";
}
while($row = mysql_fetch_object($result)) {
echo "\t<a href=\"$file?cat=".$row->cat_ID."\">";
echo stripslashes($row->cat_name)."</a><br />\n";
}
} |
Now the modified function:
| Code: | function list_cats($optionall = 1, $all = 'All', $sort_column = 'ID', $sort_order = 'asc', $file = 'blah', $count = 0) {
global $tablecategories,$querycount;
global $pagenow;
$file = ($file == 'blah') ? $pagenow : $file;
$sort_column = 'cat_'.$sort_column;
$query="SELECT * FROM $tablecategories WHERE cat_ID > 0 ORDER BY $sort_column $sort_order";
$result=mysql_query($query);
$querycount++;
if (intval($optionall) == 1) {
echo "\t<a href=\"$file?cat=all\">$all</a><br />\n";
}
while($row = mysql_fetch_object($result)) {
echo "\t<a href=\"$file?cat=".$row->cat_ID."\">";
echo stripslashes($row->cat_name)."</a>";
if ( $count == 1) {
echo " ".
count_cat_posts( $row->cat_ID );
}
echo "<br />\n";
}
} |
We have added a parameter called $count, that accepts values 0 or 1.
0 = not counted
1 = count posts
Easy, hope you like it ;)
Salu2
PS: you can see how it works in http://oscar.gen7es.net click in "Archivos" and you will see how it works OR going directly to the page http://oscar.gen7es.net/b2custar.php. The weblog is in spanish but anyway you can figure out how it works. |
|
| Back to top |
|
 |
GamerZ
Joined: 15 May 2002
Posts: 537
Location: Singapore
|
| Posted: Thu Aug 29, 2002 11:31 am Post subject: |
|
|
read some threads in sitepointforums, syaing that by using mysql_num_rows is quite inefficient. Instead try this
[php:1:04963d0f2a]
function count_cat_posts($cat_ID) {
global $tableposts;
$query = "SELECT COUNT(*) FROM $tableposts WHERE post_category = $cat_ID";
$result = mysql_query( $query );
return mysql_result($result,0);
}
[/php:1:04963d0f2a]
_________________
++ GamerZ.Per.Sg - Complex Simplicity |
|
| Back to top |
|
|
koski
Joined: 22 Jun 2002
Posts: 16
|
| Posted: Thu Aug 29, 2002 1:42 pm Post subject: |
|
|
GamerZ I have tried your code and it has a problem when no rows were returned.
"Warning: Unable to jump to row 0 on MySQL result index #"
So, for now, I'll keep my code. But thank you for the advise. |
|
| Back to top |
|
|
GamerZ
Joined: 15 May 2002
Posts: 537
Location: Singapore
|
| Posted: Thu Aug 29, 2002 3:13 pm Post subject: |
|
|
| koski wrote: | GamerZ I have tried your code and it has a problem when no rows were returned.
"Warning: Unable to jump to row 0 on MySQL result index #"
So, for now, I'll keep my code. But thank you for the advise. |
hmm wierd. LOL Okie
_________________
++ GamerZ.Per.Sg - Complex Simplicity |
|
| Back to top |
|
|
alex_t_king
Joined: 09 Oct 2002
Posts: 194
|
| Posted: Fri Oct 25, 2002 1:54 am Post subject: |
|
|
I guess I should search this thing before I go off and start hacking on my own - I basically wrote the same code you guys did, but I'd embedded it all in the list_cats() function. I like the way you separated the count into it's own function, so I redid my code in that manner, but left my SQL using the COUNT(*) function as gamerz noted - using this is a much more efficient method.
Here it is:
| Code: | function list_cats($optionall = 1, $all = 'All', $sort_column = 'ID', $sort_order = 'asc', $file = 'blah', $count_posts = 0) {
global $tablecategories,$querycount;
global $pagenow;
global $querystring_start, $querystring_equal, $querystring_separator;
$file = ($file == 'blah') ? $pagenow : $file;
$sort_column = 'cat_'.$sort_column;
$query="SELECT * FROM $tablecategories WHERE cat_ID > 0 ORDER BY $sort_column $sort_order";
$result=mysql_query($query);
$querycount++;
if (intval($optionall) == 1) {
echo "\t<a href=\"".$file.$querystring_start.'cat'.$querystring_equal.'all">'.$all."</a><br />\n";
}
while($row = mysql_fetch_object($result)) {
// begin hack
// added new param: $count_posts
$temp = "";
// getting the count of posts in each section
if ($count_posts == 1) {
$count = count_cat_posts($row->cat_ID);
$temp = " (".$count.")";
}
// end hack
echo "\t<a href=\"".$file.$querystring_start.'cat'.$querystring_equal.$row->cat_ID.'">';
echo stripslashes($row->cat_name)."</a>".$temp."<br />\n";
}
}
// hack
function count_cat_posts($cat_ID) {
global $tableposts;
$count = mysql_query("SELECT COUNT(*) FROM $tableposts WHERE post_category = '$cat_ID'");
$count = mysql_result($count,0);
return $count;
}
|
This shows "Category Name (15)" where 15 is the number of posts for each category. |
|
| Back to top |
|
|
yulee
Joined: 03 May 2003
Posts: 8
|
| Posted: Sun May 04, 2003 12:45 pm Post subject: |
|
|
| Thank you Alex, awesome! |
|
| Back to top |
|
|
Cyberian75
Joined: 26 Sep 2002
Posts: 1278
Location: Oregon
|
| Posted: Sun May 04, 2003 6:06 pm Post subject: |
|
|
I would use "COUNT(ID)" in the count function for efficiency. There's really no point of counting every column in each row.
_________________
Michael P.
 |
|
| Back to top |
|
|
yulee
Joined: 03 May 2003
Posts: 8
|
| Posted: Sun May 04, 2003 6:22 pm Post subject: |
|
|
Err, excuse me but I'm not a developer, so I cannot follow your comments. Do you think there is a way to get it running better?
Thank you! |
|
| Back to top |
|
|
Cyberian75
Joined: 26 Sep 2002
Posts: 1278
Location: Oregon
|
| Posted: Sun May 04, 2003 6:41 pm Post subject: |
|
|
| yulee wrote: | Err, excuse me but I'm not a developer, so I cannot follow your comments. Do you think there is a way to get it running better?
Thank you! |
It's nothing major, but it's recommended to query only the columns needed. So, Alex's second function becomes:
| Code: |
function count_cat_posts($cat_ID) {
global $tableposts;
$count = mysql_query("SELECT COUNT(ID) FROM $tableposts WHERE post_category = '$cat_ID'");
$count = mysql_result($count,0);
return $count;
}
|
Although the increase in performance is not noticeable, it should increase it by a little.
_________________
Michael P.
|
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
