Jump to content

select all years between two dates


fife
Go to solution Solved by kicken,

Recommended Posts

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 by fife
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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 by kicken
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.