How to search by month?

[Carlos_SM]

Hello!

I have a field date which contains the date form like: d-m-Y.
What I want to do is to search by month like january, march and so on through a dropdown list that contains the name of the months. The problem is that I only want this dropdown to appear in the list, I dont want this dropdown to be a part of the form. When people select their birthdays, it?s important that only the date field is filled, and in the list the dropdown with the name of the months are shown to perform the filter.

Any help?

Thanks in advance!

 

[Carlos_SM]

Friendly Bump!

 

[cheesegrits]

You'll probably have to do this with something like a calc element, which extracts the month from the date field, with something like ...

return date('F', strtotime('{yourtable___yourdate}'));

... set to "Calc on save only" Yes.

You can hide that on the form, but show it and filter for it on the list.

-- hugh

 

[Carlos_SM]

It worked but.. any month that I choose from my date field the filter returns january.
If I set a new record with august, the filter appear january and if select january, the new record is filtered, but new record the date is from august!
How can the element recognize the data in the list?
And moreover, how can I translate the months in the dropdown list? (my calc field is a dropdown and I want to translate the name months appearing in the dropdownlist)

Thanks!

 

[cheesegrits]

I'll have to set up a test case to look at the first part of that question. First thing to make sure of is that your calc is set so "Calc on save only" is YES, which means that we only calculate the value once when you submit the form, and we don't re-calc it "on the fly" every time it is rendered in List view. If you have existing rows that need to be set, you'd have to manually handle that, either by editing and saving each one, or doing it with an SQL query through phpMyAdmin.

For the second part, yes, date() is not locale aware, so you'll need to use strftime() instead, and make sure the locale is set. Not sure what language you use, but for Italian, it would be:

$old_locale = setlocale(LC_TIME, 'it_IT');
$month_name = strftime("%B", strtotime('{yourtable___yourdate}')); 
setlocale(LC_TIME, $ol_locale);
return $month_name;

Replace it_IT with your language code. Note that changing the calc code will NOT change any existing data (something we're going to fix "one of these days"), so you'll need to edit and save existing rows one time, or work out the SQL query to run in phpMyAdmin. Which would probably involve setting the lc_time_names system variable, then using the MONTHNAME() function.

I'll get back to you with on calc filtering thing.

-- hugh

 

[cheesegrits]

So for instance, if your month name field is 'month_name' and your date field is called 'yourdate', and you table is 'yourtable', to set all existing rows up correctly with Italian month names, you would do this:

SET lc_time_names = 'it_IT';
UPDATE yourtable SET month_name MONTHNAME(yourdate);

This is just a once off thing, if you have existing rows you need to "prime" with the new data.

-- hugh

 

[cheesegrits]

I set up a test case exactly as described, and it works.

The only wrinkle I noticed is that when I specify it_IT, PHP returns either %b or %B as all lower case, so 'febbraio' instead of 'Febbraio', whereas in the default English locale, the months are capitalized. Likewise MySQL's MONTHNAME() function.

Anyway, if you find that it is returning all lower case for your locale language, and you want to capitalize the first letter from your calc, you'd do:

$old_locale = setlocale(LC_TIME, 'it_IT');
$month_name = ucfirst(strftime("%B", strtotime('{yourtable___yourdate}'))); 
setlocale(LC_TIME, $old_locale);
return $month_name;

-- hugh

 

[Carlos_SM]

Ok, it worked with new records!
I got 11,000 registers in my table, but the filter does not work for them :/
It is possible?
My language is pt_BR, but still in english! Any clue?

Thank you very much for the support!

 

[Carlos_SM]

Friendly bump!

 

[cheesegrits]

Did you try the method I described in post #6?

You may wish to make a copy of that table and experiment, before applying the query to the real one. Or at least back it up prior to trying any queries by hand.

OBTW, if your locale in MySQL produces all lower case month names, there is a way to change the first letter in a given field to upper case in all rows of a table:

UPDATE `yourtable` SET `month_name` = CONCAT(UPPER(LEFT(`month_name`, 1)), SUBSTRING(`month_name`, 2))

... which you would do after setting up the month names as per post #6. Obviously change table and field name to suit.

-- hugh

 

[Carlos_SM]

Let me see if I understand:

My calc field will be like this?

SET lc_times_names = 'pt_BR';
UPDATE cadastro_pessoas SET month_name MONTHNAME(aniversario);
$old_locale = setlocale(LC_TIME, 'pt_BR');
$month_name = ucfirst(strftime("%B", strtotime('{cadastro_pessoas___aniversario}')));
setlocale(LC_TIME, $old_locale);
return $month_name;

If I put the two first lines in the CALC element, every time I add a new record, does not show in the filter.
So.. where I put those two first lines?

Thank?s!!

 

[cheesegrits]

No. If you re-read my post #5, it should make more sense.

The SQL query(s) I mentioned only need to be run once, by hand, in something like phpMyAdmin, in order to set up the new month field for existing rows.

The point being, when you add (or change) a calc to a list with existing data, that calc is not automagically applied to existing data. So you'll need to apply the query I gave you, by hand, just once, to create the format you want for the existing rows.

-- hugh

 

[Carlos_SM]

Ok! I?m having a problem with the syntax in phpmyadmin. It seems that:
SET lc_time_names = 'pt_BR';
UPDATE `cadastro_pessoas` SET `filtro_mes` MONTHNAME(`aniversario`)
Is not working.
The first line executes ok, but the second.. always says that has an syntax error.. and I am still searching in the documentation what is the problem with the syntax.. and I don?t see anything wrong yet. The error msg that appears is this:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MONTHNAME(`aniversario`)' at line 1.

Maybe is there something I?m missing!
But I?m still searching!

Thank you very much!

 

[rob]

try:

UPDATE `cadastro_pessoas` SET `filtro_mes` = MONTHNAME(`aniversario`[/COLOR])

 

[Carlos_SM]

Thank you, it worked!
I already did with = but I guess I should have an space between monthname!

One more thing, my language is in pt_BR, but for some misterious ways, only one part of fabrik appear in portuguese, the details of the elements, all that are in english (and I have the translated files in place), some options are in portuguese. I am checking to see if I can change without translating the en_GB file!
Other thing is that the month names that appears on my list, is ordered by name, like april, august, december and so forth (YES... it is in english.. and I already executed both lines in phpmyadmin and put that string that chessegrits told me.. it works for adding months.. but the language is still in english!

How can I translate to pt_BR the month names and also put in order like January, February and so on?

Thank you very much for your help!

 

[cheesegrits]

First thing ... when you did that UPDATE in phpMyAdmin, did it set the names in Portuguese? I noticed that Rob fixed the UPDATE line in his post, but it would also be necessary to set the lc_time_names variable before you execute the UPDATE.

Can you paste the code you have in your calc element?

For ordering the filter, have you tried changing the "Order by" setting the element's list filter settings? I can't remember offhand if we apply that to things that aren't joins.

-- hugh

 

[Carlos_SM]

Hi!

Well when I insert the update in phpMyAdmin nothing become portuguese, because the colum `aniversario` is in numbers, like d-m-Y, and even with the SET lc_time_names = 'pt_BR'; which I executed first, it seems that has no effect because the dropdown names in the calc field is still in English.

So.. in steps what did I do:
In phpMyAdmin I executed this lines:

SET lc_time_names = 'pt_BR';
UPDATE `cadastro_pessoas` SET `filtro_mes` = MONTHNAME(`aniversario`)
cadastro_pessoas is my table, filtro_mes is my calc hidden element, and aniversario is my date field with d-m-y string!

After that I have my calc (filtro_mes) with this code:

$old_locale = setlocale(LC_TIME, 'pt_BR');
$month_name = ucfirst(strftime("%B", strtotime('{cadastro_pessoas___aniversario}')));
setlocale(LC_TIME, $old_locale);
return $month_name;

And about ordering the element, I tried to change to value or none, but it seems that didn?t work.

Its almost done, everything is fine, except the translations for the month names and ordering them like we know in our calendar

Thank you very much for your help!

 

[cheesegrits]

Try this:

SET lc_time_names = 'pt_BR';
SELECT MONTHNAME('2013-02-012');

... and see what it spits out. It should give you 'fevereiro', as per the attached screenshot of my system (I'm using Navicat, not phpMyAdmin, but it's still just executing some SQL).

If it doesn't give you the Portuguese, and just gives you February, then your MySQL doesn't have the pt_BR language installed, and you'll need to talk to your host support about that.

-- hugh

 

[Carlos_SM]

Ok, it returned "fevereiro".
But in my fabrik appears february.. still don?t know why, but in phpmyadmin it works!

Thank you!

 

[cheesegrits]

OK, so ... after you do the table UPDATE as you described in post #17, and you look at the raw table data in phpMyAdmin, has it pre-set all your filtro_mes fields to the Portuguese?

-- hugh