I have a database with a table where all the records are timestamped using UNIX time. I need to extract that data into a useable table on a webpage that breaks up into links that correspond to each month since the data began being entered (in 2005). I originaly thought I'd figured it out... but lo and behold when the next December rolled around (ie, last month), and it didn't show up, I realized my mistake.
Initially, my code was as follows :
$lst = "SELECT data_timestamp, MONTH(FROM_UNIXTIME(data_timestamp)) as data_m FROM data GROUP BY prp_m ORDER BY data_timestamp ASC";
$lst_result = @mysql_query($lst, $connect) or die(mysql_error());
This worked great for most of the year, returning a link in the table for each month of the year. However... once this December rolled around, it didn't show. I realized that it was listing the months as I specified... but by MONTH... so only 12 records at a time will show! For example, this is what I'm getting :
December, 2005
January, 2006
February, 2006
March, 2006
April, 2006
May, 2006
June, 2006
July, 2006
August, 2006
September, 2006
October, 2006
November, 2006
This last December (Dec 2006) doesn't show up in the webpage list, and now this month (January 2007) isn't showing up either.
So I tried this next :
$lst = "SELECT data_timestamp, YEARWEEK(FROM_UNIXTIME(data_timestamp)) as prp_m FROM data GROUP BY prp_m ORDER BY data_timestamp ASC";
$lst_result = @mysql_query($lst, $connect) or die(mysql_error());
Now, with this new query, I'm getting this as my results in the webpage list :
...
October, 2006
October, 2006
October, 2006
October, 2006
October, 2006
November, 2006
November, 2006
November, 2006
November, 2006
December, 2006
December, 2006
December, 2006
December, 2006
December, 2006
January, 2007
This time at least December 2006 and January 2007 are showing up, but obviously this is not the results I want... I need it to display in a single listing for each month. I understand why I'm getting this result (YEARWEEK).
So, is there a way to get what I need with a different UNIX_TIMESTAMP SQL statement than I'm using? I've hunted through the MySQL manual and haven't found anything that I can see would work, and a search through the forums didn't provide any indication either.
