Page 1 of 1

help generating some statistics

Posted: Sun Nov 06, 2011 7:23 pm
by andy_mack
I am sure this is a newbie question, but I'm needing help generating some statistics from my website. I'm new to PHP and have implemented some PHP in my website, and now I'm trying to generate some meaningful statistics about page visits.

I have a MySQL table called 'statistics' where I track information about page loads. This table has the following fields:
id - autonumber
ipaddress
pagetitle
time - filled on MySQL side with CURRENT_TIMESTAMP which uses YYYY-MM-DD HH:MM:SS format

I want to generate a simple report which shows the count of records by date. So I need to strip out the HH:MM:SS part of the 'time' field. Then I need to count the number of records that correspond to each date. Then display that information sorted by date descending.

so basically I'm getting the data like this:

Code: Select all

mysql_connect($hostname,$dbuser,$dbpassword);
@mysql_select_db($dbname) or die( "Unable to select database");

// setup the main query
$query = "SELECT id, time FROM statistics ORDER BY time DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
But I don't really know how to handle the rest of it. Can anyone help?

Thanks,
Andy

Re: looping help

Posted: Sun Nov 06, 2011 8:28 pm
by califdon
Welcome to PHP and the DevNet forum.

Several tips that may be of help for you:

The DateTime data type in MySQL is very helpful for this purpose, since the value that is stored is actually the number of seconds since an arbitrary starting point in history (midnight of Dec. 31, 1959, if I remember correctly), so you can easily format it and do calculations with it. There is no need to "strip" it, just format it the way you want with the MySQL DATE() function or the PHP format() function.

Try this, after your database connection code:

Code: Select all

$query = "SELECT DATE(time) AS dt, COUNT(id) AS nr FROM `datetimes` GROUP BY dt ORDER BY dt";
$result=mysql_query($query);
while($row=mysql_fetch_assoc($result)) {
    extract($row);
    echo "$dt &nbsp; $nr<br />";
}
The DATE(time) extracts the date part from your "time" column and assigns that an alias field name of "dt", then the COUNT(id) counts the number of ids in each group and assigns that an alias field name of "nr", then the GROUP BY dt specifies that you want to group by the alias field "dt".

You don't need to know the number of rows returned because the while( condition will keep looping until there are no more rows to fetch (the fetch command returns a false). The extract($row) inside the loop associates each column with a variable named the same as the column or alias name.

Check out these references:
http://www.tizag.com/mysqlTutorial/mysql-date.php
http://billauer.co.il/blog/2009/03/mysq ... unix-time/
http://www.w3schools.com/php/php_date.asp

Re: help generating some statistics

Posted: Sun Nov 06, 2011 9:01 pm
by andy_mack
thanks so much califdon,

with some small little tweaks it's working perfectly - just needed to close the parenthesis in one place, put my table name in, and sort DESC on date.

Excellent.

Thanks again,
Andy

Re: help generating some statistics

Posted: Sun Nov 06, 2011 9:15 pm
by califdon
Oops, where did I get that other table name from?? Anyway, I'm glad you corrected those items and got it working. Now of course, there's a whole lot more to do, but that gets you started and you can come back here when you need additional help. And don't neglect to read through those references. It's much more powerful than I can explain in a few words here in the forum.