...

View Full Version : No Clue - Show record numbers per month.



dommy
05-25-2007, 06:44 PM
Hi,

I've been looking for help on this for ages with no success.. I would have thought it would be an easy thing... but it's totally baffled me.

I've built my own blog, and it submits data to a mdb. I've included the date the post was created as one of the table fields.

Now what I want to do is create a side menu that does this...

Jan 07 - 6 Posts
Feb 07 - 2 Posts
March 07 - 0 Posts
etc.

Each one being a link to the posts in that particular month.
Please could someone advise me on steps to...
A. Display the months since 2007 but only up to the present month.
B. Next to each month, search the records in table "blog" for the month("blogdate") and write out the total number i.e.
Jan 07 - 7 Posts
..and repeat for each month.
C. Make each month a link pulling the titles from the blogs that month.

I'm really lost and confused, so please help if you can.
Regards

Here's an example of what I require..
http://blog.case.edu/jms18/archives

mehere
05-25-2007, 08:15 PM
to display the months from beginning of year to current, try something like this:

curYear = year(now())
for i = 1 to month(now())
strSQL = "SELECT count(*) as totalPosts FROM blog WHERE month(blog_date) = '" & i & "' AND year(blog_date) = '" & curYear & "'"
set rs = conn.execute(strSQL)
if rs.EOF and rs.BOF then
response.Write(monthname(i) & " " & curYear & " - 0 posts")
else
response.Write(monthname(i) & " " & curYear & " - ")
response.Write("<a href=""display_page.asp?m=" & i & "&amp;y=" & curYear"">" & rs("totalPosts") & "post(s)</a>")
end if
rs.Close
next
the link would go to another page where you would pull the records from the database that match the month & year and display them.

dommy
05-25-2007, 08:24 PM
Thanks for the speedy reply Mehere...!

I totally understand your reply and will give it a shot now...and will post back soon, so do check back :)

P.S. Just because I found it, I thought I'd post another example..

http://www.outsightinteractive.com/blog/archive/200702
see the right hand side.

Cheers

ghell
05-25-2007, 11:51 PM
You can do this mostly in the SQL. Note that it will skip months with 0 posts.

SELECT post_date, count(post_id) FROM posts GROUP BY MONTH(post_date)

Something like that would do. It would be a lot more efficient but you would need to add code to insert the 0 post months (if you ever have 0 post months). You could also extend this to only count the last 12 months or whatever ir you wanted.

I have not tried this on ms access databases, but even if the syntax is wrong for access, you should be able to do it somehow (eg datepart if month doesn't work). The key is the GROUP BY and the COUNT.

EDIT: You can also add another group by on year and should be able to order it by month and year. To add the 0s just look for gaps when looping through. For example if you have

2007, 05, 2
2007, 06, 14
<-- When you are at the start of year 2007 month 8, you can notice that the last one you did was year 2007 month 06 and print out the missing months.
2007, 08, 3

There may be an easier way of adding the missing months (if you want them that is)

Again I'd like to point out that this single SQL query is a LOT more efficient than looping through and performing multiple queries. You should always do as much as possible in SQL if you can.

dommy
05-26-2007, 12:02 AM
Hey ghell,

Many thanks for the reply...
I will not be able to test either now.. but I will post my findings/success tomorrow. Many many thanks for your time!

:thumbsup:

EDIT: I get the following problem:
You tried to execute a query that does not include the specified expression 'post_date' as part of an aggregate function.

I changed my fields in my db to match what u used in the sql expression so I don't get what's wrong.?

ghell
05-26-2007, 01:16 AM
Ok, you can only return fields that are actually in the group by. I changed my SQL half way through and forgot to change that part.

Try just doing
SELECT *, COUNT(*) FROM ...
if you are not sure.

This should come out something like this (again, not tested)

SELECT *, COUNT(*) FROM posts GROUP BY MONTH(post_date), YEAR(post_date) ORDER BY MONTH(post_date), YEAR(post_date)

You might have to switch the year and month around.

In most cases if you can't pull out something because it is not part of the group, you can just wrap it in an aggregate function like min or first (if you know it is always the same for example, getting the minimum of 1,1,1,1,1,1 is always going to be 1) but here you should be able to pull the year, month and count out because count is aggregate and month and year are in the group by. However, I haven't tested so I may be making a mistake somewhere.

EDIT: Don't forget to change the table and field names to match your table.

mehere
05-26-2007, 05:23 AM
any item in your select that is not an aggregate function must be included in your group by.

SELECT MONTH(post_date), YEAR(post_date), COUNT(*) FROM posts GROUP BY MONTH(post_date), YEAR(post_date)
ORDER BY MONTH(post_date), YEAR(post_date)

dommy
05-26-2007, 06:58 PM
That works perfectly Mehere! You both have helped me very much - I'm starting to understand the count procedure - very useful addition! MUCH better than working back and forth through a database.


Set objRS = objConn.Execute ("SELECT MONTH(BlogDate), YEAR(BlogDate), COUNT(*) FROM blog GROUP BY MONTH(BlogDate), YEAR(BlogDate) ORDER BY MONTH(BlogDate), YEAR(BlogDate)")

One last favour if you will, as I'm still trying to get used to writing out the response.write codes in relation to the sql statements...

What do I then call to get my months and the totals?
i.e. write out the..

Jan - 5 Post(s)
Feb - 3 Post(s)

I know that at the moment the month is a number so this will need to be changed to match a month i.e. 1=jan etc

CurrDate = Now()
CurrMonthID = Month(CurrDate)
CurrMonthName = MonthName(CurrMonthID)
This gives the current month.. now to edit to show each month?

Sorry if this seems obvious, but with an example, I know how to perform this in the future.

Many thanks again guys!

UPDATE:

Just to show I'm trying :) - this as you know doesn't change the month number to a word yet i.e. Jan.


Set objRS = objConn.Execute ("SELECT MONTH(BlogDate), YEAR(BlogDate), COUNT(*) AS TotalPosts FROM blog GROUP BY MONTH(BlogDate), YEAR(BlogDate) ORDER BY MONTH(BlogDate), YEAR(BlogDate)")
BlogMonth = objRS("MONTH(BlogDate)")
BlogYear = objRS("YEAR(BlogDate)")
BlogCount = objRS("TotalPosts")
if objRS.EOF and objRS.BOF then
response.Write(BlogMonth & " " & BlogYear & ": " & "0 Posts")
else
response.Write(BlogMonth & " " & BlogYear & ": " & BlogCount & "" & ": ")
response.Write "<a href=""blog.asp?m=" & BlogMonth & "&amp;y=" & BlogYear &">" & BlogCount & " Post(s)</a>"
end if
next

I've added in AS TotalPosts (is this correct? - It's not a field in my database, does it need to be?)

But this doesn't work... I think that's because in the sql we're already ordering each month/year, and so I don't need to loop the response.write code for each month?? If so I'm not sure how I'm meant to get my results onto the page.
P.S. When I run this it doesn't like the NEXT.

Thanks again.

mehere
05-27-2007, 02:32 PM
give this a shot. this should list all months, including those with 0 between the beginning of the current year and now

curYear = year(now())
pastYear = curYear-1
curMonth = month(now())

if curMonth <> "1" then
for i = 2 to curMonth
curSelect = curSelect & " UNION SELECT " & i
next
end if

strSQL = "SELECT month(dateadd(month,months.m,'" & pastYear & "1201')) as month, count(blogdate) as totCount " & _
"FROM (SELECT m = 1 " & curSelect & ") months " & _
"LEFT OUTER JOIN blogs b ON b.blogdate >= dateadd(month,months.m-1,'" & pastYear & "1201'") " & _
"AND b.blogdate < dateadd(month,months.m,'" & pastYear & "1201') " & _
"GROUP BY dateadd(month,months.m,'" & pastYear & "1201')"
Set objRS = objConn.Execute (strSQL)

do while not objRS.EOF
if objRS("totCount") = 0 then
response.Write(monthname(objRS("month")) & " " & curYear &": " & objRS("totCount") & " Post(s)<br />")
else
response.Write("<a href=""blog.asp?m=" & objRS("month") & "&amp;y=" & curYear & ">"
response.Write(monthname(objRS("month")) & " " & curYear &": " & objRS("totCount") & " Post(s)</a><br />"
end if
objRS.MoveNext
loop
i'm not sure which database you are using but this works on SQL Server, not sure it will work in Access.

dommy
05-28-2007, 01:50 AM
Hi Mehere,

When I run the code, it complains, Item cannot be found in the collection corresponding to the requested name or ordinal.
the line it refers to is..

curSelect = curSelect & " UNION SELECT " & i

Also there seems to be something wrong with this line when looking at the code in dreamweaver - it's greying out the last part...


"AND b.BlogDate < dateadd(month,months.m,'" & pastYear & "1201')" & _

Also, Can you just clarify what dateadd is?

P.S. I am indeed using Access.
Again down on my knees thanking you!! :)

ghell
05-28-2007, 12:06 PM
union is the set operator for 2 query results. You probably don't want it as it is very rarely useful. When you union they have to be 2 queries, and both queries have to have the same result format (for example an int then a varchar(20) then a datetime).

It looks like you are using this badly anyway from "union select" & i. Normally it would be something like "select id, name from sometable UNION select age, address from someothertable" I couldn't thnk of a non-trivial example due to union being pretty useless for most things (I suppose you could use it if you were creating 1 result set out of multiple in a stored procedure loop or something). Anyway, you can see that it just goes between 2 queries with the same result signature. It joins the 2 results together vertically, theoretically removing duplicates (but that depends on the DBMS)

In this case it seems to just be used to get a result set like this:
col 1
-----
2
3
4
5
(assuming 5 is the current month)

which seems like a very weird way to do it to me.


dateadd adds to a date and returns the new date. For example dateadd 4 days to the 2007-05-08 (8th may 2007) and you get 2007-05-12

dommy
05-28-2007, 04:05 PM
Ok.. Lol two experts are disagreeing - argh this doesn't help :( Well, I knew this was a hard thing to achieve being a newbie. Anyways, ghell, using the sql code above, how would you go about doing this then?

Thanks

mehere
05-28-2007, 05:42 PM
I just tried testing it in Access and it doesn't seem to work. I agree with ghell it is a wierd way of doing it, but it did indeed return months where there were no records, so it was the easiest way to do it.

and regarding your error regarding this line:
"AND b.BlogDate < dateadd(month,months.m,'" & pastYear & "1201')" & _
has to do with the line before it having 1 too many quotes.
"LEFT OUTER JOIN blogs b ON b.blogdate >= dateadd(month,months.m-1,'" & pastYear & "1201') " & _

Anyway, since you're using Access, we need to go another direction:
First: Create a table called months, with a column called month and insert rows with the months 1 through 12
Second: Change your query & Code to this:


curYear = year(now())
curMonth = Month(now())

strSQL = "TRANSFORM First(Total) AS totCount " & _
"SELECT Format([Month],""00"") AS thisMonth " & _
"FROM (" & _
"SELECT A.[Year], A.[Month], SUM(A.Total) as Total " & _
"FROM (" & _
"SELECT Year(blogdate) as [Year], Month(blogdate) as [Month], COUNT(*) as [Total] " & _
"FROM blogs GROUP BY Year(blogdate), Month(blogdate) " & _
"UNION " & _
"SELECT " & _
"(SELECT max(year(blogdate)) FROM blogs WHERE year(blogdate)=" & curYear & ") as [Year]," & _
"[Month], Null FROM Months " & _
") A " & _
"GROUP BY A.[Year], A.[Month]) " & _
"GROUP BY Format(Month,""00"") PIVOT [Year];"
Set objRS = objConn.Execute (strSQL)

If objRS.EOF And objRS.BOF Then
Response.Write("NO RECORDS")
Else
Do while not objRS.EOF
thisMonth = objRS(0)
thisTotal = objRS(1)
if CInt(objRS(0)) <= CInt(curMonth) Then
If isNull(objRS(1)) Then
response.Write(left(monthname(objRS(0)),3) & " " & curYear &": 0 Post(s)<br />")
Else
response.Write("<a href=""blog.asp?m=" & thisMonth & "&amp;y=" & curYear & """>" & left(monthname(thisMonth),3) & " " & curYear &": " & thisTotal & " Post(s)</a><br />")
End If
End If
objRS.MoveNext
Loop
End If
let's see how this works for you.

dommy
05-28-2007, 11:19 PM
Hi Mehere,

I don't think I can use UNION... It can't find the second table, the Months table... it's there 100% - here's how I setup that table...

Link to image (http://www.cgfolio.co.uk/test/db.jpg)

D.

mehere
05-28-2007, 11:36 PM
i've put it into an access database to test it out and placed it on an ASP page to make sure the code was correct. it all works from this end.

try running this directly in your access database and see what happens:

TRANSFORM First(Total) AS totCount
SELECT Format([Month],"00") AS thisMonth
FROM
(
SELECT A.[Year], A.[Month], SUM(A.Total) as Total
FROM
(
SELECT Year(blogdate) as [Year], Month(blogdate) as [Month], COUNT(*) as [Total]
FROM blogs
GROUP BY Year(blogdate), Month(blogdate)
UNION
SELECT
(
SELECT max(year(blogdate)) FROM blogs WHERE year(blogdate)=2007) as [Year],
[Month],
Null
FROM Months
) A
GROUP BY A.[Year], A.[Month]
)
GROUP BY Format(Month,"00") PIVOT [Year];
just noticed your table name is blog not blogs ... did you make that change?

try testing it from the files i included in the zip file (asp page/access database)

dommy
05-29-2007, 03:43 AM
Hey mehere,

Ok, not sure why but urs worked fine.. :( - I'll double check what's what in my db. As for the table name - I did remember to change all blogs to blog. :)

Ok, so this is working great and I don't know how much you;ve helped me - now I can add this great feature to my blog :) P.S. I'll add compliment notes for sure!!

One thing if you will... how is the code finding the number of posts in each month? How is that done. - Just curious as I am trying to learn as I go :) Is it the COUNT(*) as [Total] grouped by month?

Again, so much gratitude for your help! I'm very impressed and will Nominate both you guys!

mehere
05-29-2007, 06:00 AM
the way it's counting is just as you mentioned. glad you got it working.

dommy
05-29-2007, 03:47 PM
Just sent you a message. :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum