php mysql date.utc and count the occurance

Question

Need help to get a result like below I have tried a lot but could not be successful. Count the number of date occurrence and display in an array. [[Date.UTC(2011,9,23),1],[Date.UTC(2011,9,24),2],[Date.UTC(2011,9,25),5]].

So far I am able to extract the date into an array format, below is my result. [["03-09-2011"],["06-09-2011"],["06-09-2011"]]. How to add another row to show the count of each dates and date Date.UTC?

Please help

my code

$mysql_connect = mysql_connect($db_host, $db_user, $db_pword, $db_name);
$query = "SELECT DATE_FORMAT(`timestamp`,'%d-%m-%Y') as date from mytable";
if (!$mysql_connect)  die("unable to connect to database: " . mysql_error());

@mysql_select_db($db_name) or die( "Unable to select database");

$result = mysql_query($query);
$response = array();
$stats = array();
while($row=mysql_fetch_array($result))
{ 
    $date = $row ['date'];

    $stats[] = array ($date);

} 

$Response[] = $stats;

$fp = fopen('results.json', 'w');
fwrite($fp, json_encode($Response));
fclose($fp);


//die ("Failed to execute Query: " . mysql_error());

mysql_close($mysql_connect);

Answer 1

You can get the dates already counted out of the database with a GROUP BY clause:

SELECT 
    DATE_FORMAT(`timestamp`,'%d-%m-%Y') as date, 
    COUNT(*) as frequency
FROM mytable 
GROUP BY DATE_FORMAT(`timestamp`,'%d-%m-%Y')

Conversion from a date string to timestamp (as Javascript's Date.UTC does) can either be done in PHP or MySQL. UNIX_TIMESTAMP(CAST(timestamp AS DATE)) will get you the timestamp for date straight out of the database.

In PHP http://php.net/manual/en/function.strtotime.php can be used to get a unix timestamp from the format you are already retrieving: $date_utc = strtotime($row['date']); For more control use DateTime http://php.net/manual/en/book.datetime.php (strtotime can't be configured - it just uses standard assumptions).

$date_time = DateTime::createFromFormat('d-m-Y', $row['date']);
$date_utc = $date_time->getTimestamp();