How to create an array of years ordered by year using SQL & PHP ?

Posted on 2008-11-13
Last Modified: 2012-05-05

I'm using the following php/sql query to calculate the number of years between my earliest and most recent database records:
$query_years = mysql_query("SELECT YEAR(MAX(date)) - YEAR(MIN(date)) FROM locations");

This returns a single value and what i require instead is an array of all the YEARS in year order so that any year where there isn't a specific record for that year is omitted...
e.g. if my database had 2 records dated 1983 and 2008 - then it should just return these 2 years in chronological order, rather than all 25 years between the two dates.

 - please can you show me how this is done?

Many thanks
Question by:Daniish
    LVL 12

    Accepted Solution

    I'm not familiar with MySQL but if I assume that the YEAR function extracts the year part of the date then the following should work (based on Oracle and SQLServer sql)

    Select distinct YEAR(date) from locations
    order by date

    The distinct qualifier removes duplicates from the result set.

    An alternative is
    Select y from
    (Select YEAR(date) as yfrom locations order by date) inner
    group by y

    In this example the inner query potentially returns returns duplicate years and these are removed by the outer group by clause

    Hope this helps
    LVL 17

    Expert Comment


    $query_years = mysql_query("SELECT YEAR(MAX(date)) - YEAR(MIN(date)) FROM locations WHERE `date` BETWEEN YEAR(MAX(date)) AND YEAR(MIN(date)) ORDER BY `date`;");

    Open in new window

    LVL 1

    Expert Comment

    I'm not sure but try one of this:

    $query_years = mysql_query("SELECT YEAR(MIN(date)),YEAR(MAX(date)) FROM locations ");


    $query_years = mysql_query("SELECT DISTINCT YEAR(date) FROM locations ");

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now