Gossamer Forum
Home : Products : DBMan SQL : Discussion :

How to transform date input?

Quote Reply
How to transform date input?
Hi!

I have to transform german date form inputs (dd.mm.yyyy) to the mySQL database format (yyyy-mm-dd). How can I do this? I have no problems transforming the database output into the german format. My problem is the other direction: user enters dd.mm.yyyy inputs and I want to store yyyy-mm-dd in the database.

I tried to write a plugin that does the transformation but can't get it work.

BTW. In the manual p. 28 I read as explanation for the date type of table colums

Quote:
A data column contains a date that complies with the "Date Options" in the Setup menu.


But where are these "Date Options"? I can't find them.

I found an admin template (setup_date.html) which looks very promising but the template seems not to be used in my DBMan SQL installation.

Thank you!

Armin
Quote Reply
Re: [Armin] How to transform date input? In reply to
Hi, Armin.

The mention of date options in the manual is an error: sorry. A plugin is now being worked on to perform the date transformation.

Bruce.
_________________________
bruce@gossamer-threads.com
Quote Reply
Re: [Inertia] How to transform date input? In reply to
Quote:
A plugin is now being worked on to perform the date transformation.


This plugin would be very welcome!

Armin
Quote Reply
Re: [Armin] How to transform date input? In reply to
Attachment is the simple plugin for you to try out.

Cheers,
Jean
Gossamer Threads Inc.
Quote Reply
Re: [jean] How to transform date input? In reply to
Great!

I am quite sure that the plugin will work as it equals my own plugin very much that I ultimately succeeded to work out since my fist posting.

Thank you for the help!

Armin
Quote Reply
Re: [jean] How to transform date input? In reply to
Hi Jean

since our database is using European date format, I've installed your TransformDateInput plugin. I've seen that it has a hook for add_records and it works perfectly for the add_record form.

I've been wondering if the plugin could be "enriched" so that it also works for the modify_record form Wink. I see the following difference to the add_record hook: in the beginning, the date format stored in the database has to be converted to the European format (the date must be displayed in the correct format on the modify_record form -> <%GT::Date::date_transform($datefieldname,'%yyyy%-%mm%-%dd%','%dd%.%mm%.%yyyy%')%>) before it will be submitted back to the database and converted again (after the record modification). This means that the plugin should be able to do a two-way operation instead of a one-way operation. It would be nice if the plugin could handle both ways for the modify_record hook (otherwise, I would have to use the code mentioned above directly in the template).

Thanks for your help,
Oliver
Quote Reply
Re: [olivers] How to transform date input? In reply to
I think we would need two hooks for the modify part of the plugin:

1.) modify_form (convert the date from "DB format" to "displayed format")
2.) modify_record (convert the date from "displayed format" back to "DB format")

What do you think?

Cheers,
Oliver
Quote Reply
Re: [olivers] How to transform date input? In reply to
The code of Jean's subroutine for "add_record hook" also works for "modify_record hook":

Code:

sub modify_record {
my ($home) = @_;
my $cfg = Dbsql::Plugins->get_plugin_user_cfg ('PluginName');
$home->{cgi}->{$cfg->{date_col_name}} = GT::Date::date_transform($home->{cgi}->{$cfg->{date_col_name}},$cfg->{orig_format},$cfg->{new_format});
return @_;
}


I've tested it: first, I had a problem because I've installed several plugins that haven't been
loaded in the right order. After some research in the forum, I found a solution about how to
load the plugins in a specific order. Everything was fine after that Smile

Does anyone have a solution for the modify_form hook? And another thing: sometimes, you
might have several date fields in a database - what's the best way to "enrich" Jean's plugin
with this functionality?

Thanks for your help and have a good time
Oliver
Quote Reply
Re: [olivers] How to transform date input? In reply to
I've now added 3 date fields to the plugin user config and used this sub:

Code:
sub modify_record {

my ($home) = @_;
my $cfg = Dbsql::Plugins->get_plugin_user_cfg ('PluginName');
$home->{cgi}->{$cfg->{date_col_name1}} = GT::Date::date_transform($home->{cgi}->{$cfg->{date_col_name1}},$cfg->{orig_format},$cfg->{new_format}) if ( defined($cfg->{date_col_name1} and $home->{cgi}->{$cfg->{date_col_name1}}) );
$home->{cgi}->{$cfg->{date_col_name2}} = GT::Date::date_transform($home->{cgi}->{$cfg->{date_col_name2}},$cfg->{orig_format},$cfg->{new_format}) if ( defined($cfg->{date_col_name2} and $home->{cgi}->{$cfg->{date_col_name2}}) );
$home->{cgi}->{$cfg->{date_col_name3}} = GT::Date::date_transform($home->{cgi}->{$cfg->{date_col_name3}},$cfg->{orig_format},$cfg->{new_format}) if ( defined($cfg->{date_col_name3} and $home->{cgi}->{$cfg->{date_col_name3}}) );
return @_;
}

I'm now looking for a solution for the modify_form thingy. Any hints?

Cheers,
Oliver