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