Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Database Field Advice

Quote Reply
Database Field Advice
Hi,

I'm setting up a table of public (national) holidays in different countries. I guess each country will have between 10 and 25 dates to be entered. If my limited knowledge of good database planning is correct, it's not good practice to enter something like (mm/dd) - 01/15,02/25,03/04,04/21,05/23,06/18,07/03,08/30,etc. into one field. I think each date should be entered seperatly but that means the user will have to enter 10 to 25 records which I don't want.

Any suggestions on how to handle this kind of problem?

Thank you.

Simon.
Quote Reply
Re: [jai] Database Field Advice In reply to
You can write a PRE add_record prugin that takes a single field, splits it on spaces and store them as multiple entries.

Good luck, Jasper

http://www.bookings.org
Quote Reply
Re: [jai] Database Field Advice In reply to
You want a table structure perhaps like a country column and a date column and then the same country can have multiple dates, eg....

England 01/15
England 01/25
England 03/04

I think thats how best to do it. As you say multiple dates in one column is not what you want to be doing.

Depending how the dates are entered, as Jaspercram said, you can just split and insert...eg if commas:

Code:
my @dates = split ',', $IN->param('date_field');
for (@dates) {
$DB->table('Table_Name')->insert({ country_col => $country_name, date => $_ })'
}

Last edited by:

Paul: Jan 21, 2003, 8:45 AM
Quote Reply
Re: [Paul] Database Field Advice In reply to
Thank you Paul & Jaspercram.

I understand what you are saying about a plugin that splits the dates and enters them seperatly but I can't see how the dates could be modified by the user.

On the add_form the user could enter the dates in one field as - 01/15,02/25,03/04,04/21,05/23,06/18,07/03,08/30,etc.

How would I get them to appear in the same format (one field) on the modify_form???

I can also see some problems if the user increases or decreases the number of dates.

What do you think?

Thanks for you help.

Simon.
Quote Reply
Re: [jai] Database Field Advice In reply to
It shouldn't be a problem. You'd probably need to add a resource id column in that case also that matches the record the dates are for, then on the modify page you could use the following query to get the dates back:

Code:
my @dates = $DB->table('new_table')->select('date_col', { record_id => $the_id })->fetchall_list;
my $string = join ',', @dates;

Then you have the dates ready to be modified in string format. Then you just split to insert into the database as in the example in my other post.

Let me know if that made no sense :)
Quote Reply
Re: [Paul] Database Field Advice In reply to
Thanks Paul.

That does make sense but I'm wondering what happens to the original records (each individual set of dates) in the table. Does the modify process overight and/or delete the original recods?

e.g If the original record is -

01/15,02/25,03/04,04/21,05/23,06/18,07/03,08/30

and then we modify it to -

01/15,02/25,03/04,04/21

what happens to the individual record for - 05/23,06/18,07/03,08/30?????

Would I need a PRE modify_record plugin that deletes ALL existing records before adding the new individual modified records.

Do you understand what I mean??

Thanks again.

Simon.
Quote Reply
Re: [jai] Database Field Advice In reply to
Yes before you modify the dates you need to delete all rows from your new table for that record. You can do that just before you insert the modified rows.

Last edited by:

Paul: Jan 24, 2003, 5:46 PM
Quote Reply
Re: [Paul] Database Field Advice In reply to
Paul,

Thanks for your help.

With my limited experience with plugins and I would appreciate some more advice.

I know how to set up the plugin but I am not sure of the contents. As this plugin will basically do what the add_record subroutine does PLUS split my dates up and add eachone seperatly, I assume that I start by copying the add_record contents into my plugin subroutine. I'm thinking that I need to have the plugin run through all the usual steps for each split date and then display the success page after the last date has be inserted (or an error if applicable).

What I can't work out is where to add my loop to have each split date checked for errors and then inserted. I have modified the loop you gave me and have come up with the following (which works on it's own).

my $splitdate;
my @dates = split ',', $IN->param('public_holiday_202');
foreach $splitdate (@dates) {
$DB->table('public_holiday')->insert({ 'public_holiday_owner_id' => $self->{user}->{Username}, 'public_holiday_104' => $self->{cgi}->{public_holiday_104}, 'public_holiday_105' => $self->{cgi}->{public_holiday_105}, 'public_holiday_201' => $self->{cgi}->{public_holiday_201}, 'public_holiday_202' => $splitdate });
#end for
}


How do I incorporate this loop into my copy of the add_record subroutine???

Am I going about this the wrong way? Is there a simpler way to do this?

Thank you.

Simon.
Quote Reply
Re: [Paul] Database Field Advice In reply to
Hi,

I got the plugin working using the following but I am not sure about how to handle the add_success

part indicated in red. It works with (1) but I'm not sure what it really should be.

Any ideas?

Thanks.

Simon.



sub split_add_record {
# -------------------------------------------------------------------
# This subroutine will get called whenever the hook 'add_record'
# is run. You should call GT::Plugins->action ( STOP ) if you don't
# want the regular code to run, otherwise the code will continue as
# normal.
#
my ($self) = @_;
# Do something useful here
# Ignores the plugin if this is not contact table
($self->{cgi}->{db} ne 'public_holiday') and return @_;
GT::Plugins->action ( STOP );
#----------------------------------------------------------------------

my $splitdate;
my @dates = split ',', $IN->param('public_holiday_202');
foreach $splitdate (@dates) {

$self->{cgi}->{public_holiday_202}=$splitdate;


#----------------------------------------------------------------------
return $self->home($self->_language('PER_ADD')) unless ( $self->{user}->{add_p} );

#------------demo code----------------


# add data to related table
return $self->sadd_record() if ( $self->{cgi}->{sdb} and $self->{cgi}->{sdo} );



# Turn arrays into delimited fields
$self->format_insert_cgi;
if ( $self->{cfg}->{'auth_user_field'} ) {
$self->{cgi}->{$self->{cfg}->{'auth_user_field'}} = $self->{user}->{'Username'};
}

# Check foreign keys
my $msg = $self->_check_fk();
($msg) and return $self->add_form($msg);


# Setup the language for GT::SQL.
local $GT::SQL::ERRORS->{ILLEGALVAL} = $self->_language('ADD_ILLEGALVAL');
local $GT::SQL::ERRORS->{UNIQUE} = $self->_language('ADD_UNIQUE');
local $GT::SQL::ERRORS->{NOTNULL} = $self->_language('ADD_NOTNULL');

if ( defined (my $ret = $self->{db}->add($self->{cgi})) ) {



$self->auth_logging('add record ') if ( $self->{cfg}->{log_file} );

#------------demo code-----------


#Don't call up the success page
###$self->add_success($ret);
}
else {
local $^W;
my $error = $GT::SQL::error;
$error =~ s/\n/<br>\n<li>/g;
$self->add_form("<font color=red><ul><li>$error</ul></font>");
}

#end for
}


$self->add_success(1);

#end sub
}

# Always end with a 1.
1;