How to order by substr(Release, 0, 4) , Title

In my discography I have a release date and a CD title.
The date is in the table written like this 0000-00-00

When I then list the CD's and - ORDER BY Release, Title - it is only when it is released the exact same date it will order the CD's in Title order.

I only show the year in the list and not the date, so what I want it to do is to order all the CD's from the same year in Title order.

The year I use to write in the list is extracted like this

$year = substr($release, 0, 4);

And then I have tried something like this

SELECT * from Discography ORDER BY substr(Release, 0, 4) , Title");

And in a lot other ways like that, but I can't get it to work.

How do I do this. ?

First off, your SQL has too many closing parens and closing quotes. Fix it:

SELECT * from Discography ORDER BY substr(Release,0, 4) , Title;

MySQL substrings start counting with character 1, not character 0 as PHP substrings.

So:

SELECT * from Discography ORDER BY substr(Release,1, 4) , Title;

should work.

Better formatting would be based on built-in date formatting

SELECT * from Discography ORDER BY date_format(Release,'%Y') , Title;

With this approach, you could group by any format --by month, quarter, week, day, etc

SELECT * from Discography ORDER BY YEAR(Release), Title

If you only display the year then only select the year - and use the inbuilt YEAR() function rather than date_format.


$sql = "SELECT YEAR(Release) AS Release, Title,  Artist FROM Discography ORDER BY Release, Title";

Maybe this

"SELECT * from Discography ORDER BY YEAR(Release), DESC, Title, ASC)");

YEAR(Release), Title workes for me...

Thank you so much..