select all years between two dates

[fife]

Im trying to create a repeated list of all the years forms have been filled in, in my database.  The database has been running for 4 years and the row creation_date is unixtime.

 

I want a list that looks as follows

 

2010

2011

2012

2013

 

to create this list I understand Im after creation_date between the highest and lowest value of Year in unixtime.  So I wrote the following query

 

 mysql_select_db($database_dbconnet, $dbconnet); $query_rs_frmyear = sprintf("SELECT creation_date FROM customer_details WHEREcreation BETWEEEN (SELECT MIN(DATE_FORMAT(FROM_UNIXTIME(creation_date), 'Y')) AS lowyear FROM customer_details)AND (SELECT MAX(DATE_FORMAT(FROM_UNIXTIME(creation_date), 'Y')) AS highyear FROM customer_details)"); $rs_frmyear = mysql_query($query_rs_frmyear, $dbconnet) or die(mysql_error());     //echo the yearsdo {echo $row_rs_frmyear['creation_date'];   } while($row_rs_frmyear = mysql_fetch_assoc($rs_frmyear));

 

 

Which fell flat on its face!!! Any ideas anyone?

Edited February 26, 2013 by fife

[Jessica]

SELECT DISTINCT(DATE_FORMAT(FROM_UNIXTIME(creation_date), 'Y')) AS year FROM customer_details ORDER BY creation_date

[fife]

Hi Jessica. Unfortunately that doesn't work and echos nothing when I run the loop.

 

Awesome how you think in such simple terms Jessica thank you for your help. It still amazes me how complicated I tend to try and make things for myself.

[kicken]

There's no need to find the highest and lowest values, just leave the condition out of the where clause and it will grab everything:

edit:added from unixtime

SELECT 
   YEAR(FROM_UNIXTIME(creation_date)) as year
FROM customer_details 

You should also be using a while loop, not a do/while loop. You need to fetch the row first, then process it. A do/while loop works the other way and tries to process it then fetch it. Edited February 26, 2013 by kicken

[fife]

Brill thanks all. It now works using

 

SELECT DISTINCT YEAR(FROM_UNIXTIME(creation_date)) as year FROM customer_details ORDER BY creation_date ASC