[Resolved] Statistics per month/day problem (possible error in date-query?)

NetRoam

Hey all!

I've created a little statistics module for the company website which also works perfectly fine ... every hit is stored in the database with the datetime format in the MySQL-database - just so I can see exactly when every visitor came to the site.

Anyway - I've listed the hits so it's split up in months - which is fine as well ... it does say:

October ... 300 visitors
November ... 12 visitors
...

But as I want to show each month more in details so it'll show hits for each day - the bug comes in play. I'm sure it's my SQL-statement that causes the problem, but if you would be so kind to see if you can help me, it would mean a lot to me.

The thing is that when I click October for example, it suddenly shows ALL entries - and replacing the actual month with the current month (which is November), so it says this:

november, 2003 ... 17 visitors
november, 2003 ... 5 visitors
november, 2003 ... 8 visitors
november, 2003 ... 5 visitors

But it shouldn't take the first 2 entries and it should say October instead of November

The link I use to enter the stats per month is this: statistics.php?stats=per_month&month=$month
($month gives "November" or "October" for example)

The SQL-statement I use is this:

SELECT DATE_FORMAT(`dato`, '%Y-%m-%d') AS `dag`, COUNT(*) AS `hits` FROM `simplecount` 
GROUP BY DATE_FORMAT(`dato`, '%Y-%m-%d') ORDER BY `dato` DESC

I hope it's possible to help me with this. Thanx in advance!

/// NetRoam

jayant

where have u given the month in the query? , u havent specified which month you want.

NetRoam

You're right - I left that out, but that's also a little thing I can't quite figure out how to pull off ... do you have an idea how the "where" clause should be made?

jayant

rtfn

NetRoam

Well, that doesn't help me all that much because I do know how the where clause is usually used for ... but in this case it's a bit more advanced, since I need to check the datetime in my database - for example these:

2003-11-03 12:56:11
2003-10-29 11:14:23
2003-10-28 04:30:54

And select all the entries that has for example the 10 or 11 ... I changed the actual link which goes to the stats so it would be something like this:

statistics.php?stats=per_month&month=10 (10 is a variable called $month)

So how do I pick up the $month variable on the new page and add it in the SQL-statement I wrote before so it would filter out ANY other entries from other months than the one stated in the link?

jayant

use little logic

WHERE DATE_FORMAT(dato,'%c') = 10

will give results for october

got the idea?

NetRoam

Well - sorry for being a bit of a pain, but I'm still pretty new at this!

Anyway - it did work out fine when I put 10 directly in the SQL-statement but for some reason I can't quite send the variable I want to send from the previous page so I can add the variable in the SQL-statement instead of the actual number

NetRoam

Never mind - I found out myself!!