help generating some statistics

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
andy_mack
Forum Newbie
Posts: 2
Joined: Sun Nov 06, 2011 7:15 pm

help generating some statistics

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: looping help

Post 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
Last edited by califdon on Sun Nov 06, 2011 9:17 pm, edited 2 times in total.
Reason: Corrected my dumb omission of a closing parenthesis.
andy_mack
Forum Newbie
Posts: 2
Joined: Sun Nov 06, 2011 7:15 pm

Re: help generating some statistics

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: help generating some statistics

Post 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.
Post Reply