Posted by Ehsan on 04/04/06 10:43

Probably you are using mysql 3.x.x. The function SUBDATE() is in mysql
4.1.1+. You can use DATE_SUB() instead. You will require SUBDATE(NOW(),
INTERVAL 31 DAY).

I also have category in my blog. for that you will have a seperate
table for category whose ID field will be a foreign key in your blog
table. I used left join to perform the count. Following is my sql:

SELECT s.*, COUNT(b.id) as totalPost FROM section_tbl AS s LEFT JOIN
blog_tbl AS b ON (s.id = b.sec_id) WHERE s.status = 'active' GROUP BY
b.sec_id

For number of posts in a month the sql goes as follows:

SELECT CONCAT(MONTHNAME(b.post_date_time), ' ', YEAR(b.post_date_time))
as postMonYear, MONTH(b.post_date_time) as postMonth, COUNT(b.id) as
totalBlogs from blog_tbl AS b LEFT JOIN section_tbl AS s ON (b.sec_id =
s.id) WHERE s.status = 'active' AND b.status = 'active' AND b.private =
'no' GROUP BY CONCAT(MONTH(post_date_time), ' ', YEAR(post_date_time))
ORDER BY post_date_time ASC

I Print postMonYear - totalBlogs and then in anchor tag I used
postMonth to set the link. So the tag is:

<a
href='blogscript.php?month=<?=$obj->postMonth?>'><?=$obj->postMonYear?>
- <?=$obj->totalBlogs?></a>

Same thing is done for Section/Category.

Hope this helps.

Thanks and God Bless!!

Ehsan

[Back to original message]