• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

php unique month/year from date column in database??

LEGEND ,
Dec 08, 2021 Dec 08, 2021

Copy link to clipboard

Copied

OK, so I'm sure this used to work but I cant seem to see the wood from the trees.

 

I have a typical date format in the database and I am trying to get the unique month and year from the date column and then order the information in the correct order - month/year:

 

date

2022-01-10

2022-06-15

2022-11-24

2023-11-06

2022-06-17

2024-01-17

 

This gets the unique month and year

 

SELECT DISTINCT MONTHNAME(date) as `Month` , ' ', YEAR(date) AS `Year`
FROM destination_dates_prices

 

But when I try to ORDER BY date I get an error:

 

SELECT DISTINCT MONTHNAME(date) as `Month` , ' ', YEAR(date) AS `Year`
FROM destination_dates_prices ORDER BY date DESC

 

Mysql Error:

#3065 - Expression #1 of ORDER BY clause is not in SELECT list, references column 'destination_dates_prices.date' which is not in SELECT list; this is incompatible with DISTINCT

 

I can't seem to find where to locate the 'date' in the select clause???? 

 

Os

Views

641

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

LEGEND , Dec 08, 2021 Dec 08, 2021

Well it gets a bit more complicated. After doing a Google search and finding bits and pieces of nearly working examples, but not quite what I required, this finally worked:

 

SELECT DISTINCT month(`date`), year(`date`), monthname(`date`) FROM destination_dates_prices 

ORDER BY year(`date`), month(`date`) ASC

 

I've done this numerous times, on a number of other websites, in a different way, BUT for some reason that didn't work. I dont know if its because I'm on a newer version of sql on this new mach

...

Votes

Translate

Translate
Community Expert ,
Dec 08, 2021 Dec 08, 2021

Copy link to clipboard

Copied

Wouldn't the logic be something like:

 

SELECT DESTINCT field_name
FROM table_name
ORDER BY field_name ASC|DESC;

 

Nancy O'Shea— Product User, Community Expert & Moderator
Alt-Web Design & Publishing ~ Web : Print : Graphics : Media

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 08, 2021 Dec 08, 2021

Copy link to clipboard

Copied

Well it gets a bit more complicated. After doing a Google search and finding bits and pieces of nearly working examples, but not quite what I required, this finally worked:

 

SELECT DISTINCT month(`date`), year(`date`), monthname(`date`) FROM destination_dates_prices 

ORDER BY year(`date`), month(`date`) ASC

 

I've done this numerous times, on a number of other websites, in a different way, BUT for some reason that didn't work. I dont know if its because I'm on a newer version of sql on this new machine or this database table is slightly more complex!!!

 

I must be a sucker for punishment!!!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Dec 08, 2021 Dec 08, 2021

Copy link to clipboard

Copied

LATEST

Thanks for marking that correct, I didnt like to do that myself!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines