Query for distinct years from various dates

I’m working on a page (in PHP) that lists various events on past dates. The events are stored in an sql table, each entry having a date in yyyy-mm-dd format. I have the page contents working good.
I want to also add a page index with a list of links to IDs on the page based on years. So for example:-

<ul>
<li><a href="#event2015">Events in 2015</a></li>
<li><a href="#event2014">Events in 2014</a></li>
<li><a href="#event2013">Events in 2013</a></li>
</ul>

Each year may have a number of events, but to build the index list I need to query the db to create an array of distinct years from the Date column of the table. But what is the correct query to get that?
This is what I have tried, but it does not work:-

$sql = mysqli_query($db, "SELECT DISTINCT DATEPART(yyyy,Date) FROM Events ORDER BY Date DESC");
$years = mysqli_fetch_array($sql);

DATEPART is not a mysql function

SELECT YEAR(`date`) FROM Events ORDER BY YEAR(`date`) DESC

I have to say, it was a new one for me, or maybe it’s an old one. It’s something I found when Googling for answers:-
http://www.w3schools.com/sql/func_datepart.asp
Anyway, it doesn’t work.
I’ll try your suggestion when I get a minute.
Thanks

I guess w3schools doesn’t make it obviously clear, but “SQL Server” is a MicroSoft database.

That could be me not reading the page properly in desperation for answers. i just see SQL and think it’s MySql.
Anyway, I tried the above from r937 but it made a vary long, possibly infinite (I had to close it down), empty list.
Though it may not be the query that’s wrong, it could be my while loop to create the list, maybe try a foreach instead.

that page quite clearly says SQL Server DATEPART function

SQL Server is microsoft’s database, very different from MySQL

when googling, make sure you add “mysql” to the search parameters

that would’ve taken you here – http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

[quote=“SamA74, post:5, topic:205058, full:true”]Anyway, I tried the above from r937 but it made a vary long, possibly infinite (I had to close it down), empty list.
[/quote]
aaargh, i made a silly error

try this with GROUP BY instead of ORDER BY –

SELECT YEAR(`date`) FROM Events GROUP BY YEAR(`date`) DESC

Thanks, but I still haven’t got the query working, though that may be the php I’m using to echo the results.
Though for now I do have the index working using a more familiar (to me) type of query

SELECT Date FROM Events ORDER BY Date DESC

and some php to strip the dates down to unique years using substr to remove the rest of the date, then array_unique to remove duplicate years. It works, but is less elegant than I hoped for. I wanted a query that would just return the array I wanted without the need for any further processing.

SELECT YEAR(`date`) AS theyear
  FROM Events 
GROUP 
    BY YEAR(`date`) DESC

then use “theyear” in your php code

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.