Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Translating data from another table

Quote Reply
Translating data from another table
Hi guys

I've created a database with two tables; one called "SfP_Music" and the other "SfP_Composers", the tables are connected in a many to one relationship. The "music" table has a column in it called "composer_id" which is a foreign key from the Composers table and allows me to link a piece of music and its composer together.

My problem scenario is as follows. A user adds a piece of music to the database and selects from a drop down list the relevant composer (generated via a global from the composers table).
What I want to do is display the "human" readable name for the composer (stored in the column "Name" in the composers table) rather than the 'Composer_id' that is currently returned on the add_success page.

I have attempted doing this through a global with the following code, but it doesn't seem to work.

sub {
my $tags = shift;
my $table = $DB->table('SfP_Composers');
my $sth = $table->get('Composer_ID', 'HASH', ['Name']);
my $output .= $sth;
}

I hope someone can point me in the right direction!

Cheers,

Hannah
Quote Reply
Re: [hannahmackenzie] Translating data from another table In reply to
Try

Code:
sub { $DB->table('SfP_Composers')->get('Composer_ID', 'HASH', ['Name'])->fetchrow }

Last edited by:

Paul: Mar 14, 2002, 9:48 AM
Quote Reply
Re: [Paul] Translating data from another table In reply to
Paul,

Thanks for the suggestion - however, we get the following error:

"Can't call method "fetchrow" on an undefined value at (eval 20) line 4."

I tried adding "_hashref" after fetchrow but, that didn't seem to help either.

Hannah
Quote Reply
Re: [Paul] Translating data from another table In reply to
lol: you edited that one damn quick!

Wink

I just tried your edited alternative - still no joy though

Hannah
Quote Reply
Re: [hannahmackenzie] Translating data from another table In reply to
Whoops, I've not used get() before but it wouldn't help using fetchrow and returning a HASH Blush

How about:

Code:
sub {

my $rec = $DB->table('SfP_Composers')->get('Composer_ID', 'HASH', ['Name']);
return $rec->{Name};

}

Last edited by:

Paul: Mar 14, 2002, 10:04 AM
Quote Reply
Re: [hannahmackenzie] Translating data from another table In reply to
I just edited the code above, just making sure you get the final version :)
Quote Reply
Re: [hannahmackenzie] Translating data from another table In reply to
Hi,

Try the script below:

sub {
my $tags = shift;
my $rs = $DB->table('SfP_Composers')->get($tags->{Composer_ID},['Name']);
return $rs->{Name};
}


TheStone.

B.
Quote Reply
Re: [TheStone] Translating data from another table In reply to
Hey you copy cat Cool
Quote Reply
Re: [Paul] Translating data from another table In reply to
Noway.......Look at it closer..

B.

Last edited by:

TheStone: Mar 14, 2002, 10:09 AM
Quote Reply
Re: [TheStone] Translating data from another table In reply to
Edit: Ok I see the difference now. Cool

Last edited by:

Paul: Mar 14, 2002, 10:19 AM
Quote Reply
Re: [Paul] Translating data from another table In reply to
Sorry Paul, your code still didn't work

Hannah
Quote Reply
Re: [TheStone] Translating data from another table In reply to
Thanks for that "TheStone" works a treat!!!

Many thanks

Hannah
:-)
Quote Reply
Re: [hannahmackenzie] Translating data from another table In reply to
Hey thats not fair Angelic
Quote Reply
Re: [Paul] Translating data from another table In reply to
Hi guys

Thanks for the help with yesterdays' problem. I have another related one for you now.
What i'd like to do is to be able to search on the "Composer Name" field in the "SfP_Composer" table and gain results from the SfP_Music table. What do I need to do to the search module to allow me to perform such a search?

Hope someone can help (again).

Cheers,

Hannah