Get unique values

I have a database field with dates and times that looks like this

2018-10-29 01:16:28
2018-10-25 22:58:34 
2018-10-18 16:59:27

I have a database query that I would like to make only get the distinct year portion of each row, but can not seem to make it do that, so I am just querying the entire date.

I have then added a formatter to it of EnquiryDateSubmitted.getYear(EnquiryDateSubmitted)
Which then returns about 2000 entries where 2018 is repeated 300 times.

I need something like .unique added to the list, but i can not use .unique unless the returned data is an array, so I would like to do something like
EnquiryDateSubmitted.getYear(EnquiryDateSubmitted).toArray().unique
Which obviously does not work

Does anyone have any idea of how i can do this other that creating a view in MySQL with the date already stripped into just the year portion.

When you say ‘distinct year portion’ what do you mean? For example you want to get 2018 just once? If yes this is what I do when working with distinct.

  1. I create a new column called ‘common’
  2. I add a default value = 2 (in your situation you would add the year (2018, 2017, etc))
  3. Then on using the distinct I select this ‘common’ column I have created

This way it retrieves only once values that are different.

Hope this approach can help you.
Thank you!

1 Like

That is exactly what i want to do, however without using MySQL to create new columns or create a new MySQL view, hoping there is a way to do this just by pulling in the 2000 records then stripping out only the year, then displaying only the unique values, as in 2018 once and then 2017 once etc.
The only way i can think of right now is loading the stripped down years into a dmx-value variable and then loading the data from the variable which should then be an array and using the unique() formatter on that.

Maybe I could add a repeat step to the server action with the expression of {{query_enquiry_year.formatDate(“yyyy”)}} but then in the steps I would need to add a “Set Value” step possibly, just not too sure how to handle it from there?

I’d suggest creating a view, which returns the unique years and using it as a source for your query then :slight_smile:

Ok, I submit, say “Uncle”, I failed, i will create a table view in MySQL like i originally imagined i would need to do, before i spent the last 2 hours trying to be super smart, lol.

1 Like

Not in front of a machine with Wappler or Mysql on so can only to SQL for you at moment off the top of my head untested

basic return all records only showing the year part of the date
select year(EnquiryDateSubmitted) as yearonly from tablename

Return only 1 record for each year
select distinct year(EnquiryDateSubmitted) as yearonly from tablename

Return unique years with number of records fitting each year
select distinct year(EnquiryDateSubmitted) as yearonly, count(*) as mycount from tablename

Hope this helps, certainly will create the views you want, can’t see how i would do it in wappler as i dont have access.

1 Like

@Hyperbytes, only you would know that off the top of your head, lol, I was just starting my research, haha, you figured you might as well post it before I PM you for the answer anyway, clever man.

1 Like

Just tried this, first one works perfect, the second one with the count though will not return correct, it only shows a single year with the count of all rows in the entire table? what did you miss? hmmm.

Sorry, try adding group by yearonly on the end, that should do the job (i hope)

EDIT and then probably remove the distinct clause i suspect

1 Like

Redeemed as the MySQL master, thanks so much, appreciate it.

Phew, thought i was going to be demoted

1 Like

Never Brian, you are integral part in my one man band team here in South Africa.

Ok so I have still had to fight this one a little.

What I wanted to do
Have a page with 1300 records roughly shown for all Online Enquiries to this website from January 2010 to today.

Have a select menu dynamically populated with only Years shown from 2010 - 2018

Have another select menu dynamically populated with only Months from 01 - 12 however this would be dependant on select menu 1, so if 2018 were selected then it should only show from month 01 - month 10 as well as skip out all months where there were no online enquiries.

What I have been forced to rather do as I can not get that to work as expected is have a single select menu showing all Years and Months so 2018-10 and 2018-09 and 2018-08 etc. all the way back to 2010-01

Then I have had to create a new $GET variable in my main query that gets all the records and filters the query by the value of the select menu, the problem I ran into was the operator in the condition for a date field does not give me options such as “Begins With” and all those types of things, so I had to pretend I was working with a text field rather to use the Begins With operator, then go into the .php file /dmxConnect/api/folder/file.php and find all the incorrect field names and change them to the correct name.

To be honest I did not imagine this would work, but it has flawlessly.

Moral of the story is, next time i think hmm I need a simpler solution rather than just using a date range selector which is more designed around this, just stop, smack yourself in the face and use the right tool for the job instead of hacking your way through it.

Playing with grandkids at moment so only brief response but if you take the count of matched records filtering by count >0 may remove the empty months

1 Like