help generating some statistics
Posted: Sun Nov 06, 2011 7:23 pm
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:
But I don't really know how to handle the rest of it. Can anyone help?
Thanks,
Andy
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);Thanks,
Andy