Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Multiple tables in one template

Quote Reply
Multiple tables in one template
It's probably easy but I don't see it anywhere.

How would I list certain fields from more than one table in one template? Some tables are related, some are not.

Thanks
Quote Reply
Re: [bf] Multiple tables in one template In reply to
For the ones that are related, you can use queries, I think.

For the ones that aren't related (and IMHO, also a good approach for the ones that are) you'd use either globals or a custom external perl module.

Here's a thread that discusses the external custom.pm option:

http://www.gossamer-threads.com/...i?post=219245#219245

Hope that helps.

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Quote Reply
Re: [hennagaijin] Multiple tables in one template In reply to
Nope, sorry that didn't do it. All it did was dump a pile of info into the page that I don't want shown.

It should be simple, in a template that displays the basic info for a person, name, city, photo, comment etc. I want to bring in other details from other tables such as this persons schedule list or sponsors (so a keyword search is required). This other info is contained in different tables because the main table would be too large with the info included and it is easier to control that way. Unfortunetly some of these other tables cannot be related because I need to use a different Primary Key in them (unless I missed something else).

In related tables data from other tables aparently can be brought in and displayed with <%Dbsql::Relation::HTML::generate_description('Author','AuthID','AuthName')%> Is there a better way to do this, hopefully so it will bring in all the data taht applies to this person with a keyword search added ?

How would it be done for un-related tables?
Quote Reply
Re: [bf] Multiple tables in one template In reply to
Sorry - I didn't explain that as well as I should have. I'm not suggesting that you use TheStone's "template dumper" code, I'm suggesting that you create a file called Custom.pm (or whatever.pm) and put it in /admin/Dbsql. Then you can write custom perl sub routines using the GT libs that access other tables in your database. I have used this technique extensively on a site that uses over 80 different tables in DBManSQL, and it works beautifully, often accessing 5-6 different tables on the same page. You do need to be comfortable with a little basic perl programming, of course. If you decide to go this route, I would also suggest that you spend some time reading through the GT Module documentation, which you can access by clicking the "Help" link in your admin panel.

In any event, the way you would use this in a template is as follows. Say you put a subroutine called "doTheThing" in a new module called "Custom.pm". You could call that subroutine from any template in your setup by using <%Dbsql::Custom::doTheThing%>. If you write a subroutine that can have args passed to it, then you would use <%Dbsql::Custom::doTheThing($arg1, $arg2, etc.)%>

I hope that helps. I admit, this approach may be a little advanced for a new user without much perl experience. But like I said, I've had great success with it, and I'm a crappy programmer with VERY little perl knowledge. Once you get the hang of it, it's a great way to extend the functionality of DBMan SQL.

Fractured Atlas :: Liberate the Artist
Services: Healthcare, Fiscal Sponsorship, Marketing, Education, The Emerging Artists Fund
Quote Reply
Re: [bf] Anybody Home????? In reply to
Can anyone from GT answer this question??

Is there not a simple command to use or do I have to get involved in Plug-ins and Globals as hennagaijin was kind enough to offer.....



In Reply To:
It should be simple, in a template that displays the basic info for a person, name, city, photo, comment etc. I want to bring in other details from other tables such as this persons schedule list or sponsors (so a keyword search is required). This other info is contained in different tables because the main table would be too large with the info included and it is easier to control that way. Unfortunetly some of these other tables cannot be related because I need to use a different Primary Key in them (unless I missed something else).

In related tables data from other tables aparently can be brought in and displayed with <%Dbsql::Relation::HTML::generate_description('Author','AuthID','AuthName')%> Is there a better way to do this, hopefully so it will bring in all the data taht applies to this person with a keyword search added ?

How would it be done for un-related tables?
Quote Reply
Re: [bf] Anybody Home????? In reply to
In this case, you need to create a global template like:

sub {
my ($table, $field, $value) = @_;
my $sth = $DB->table($table)->select({ $field => $value });
my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}

Assumed that I have table 'profile' and 'interest' which are not related, now I want to display the interests of profile 'A01', the template should be:

<%get_info('interest', 'profile_id', 'A01')%>
<%loop loop_hash%>
<%Field1%>
<%Field2%>....
<%endloop%>

Hope that helps.

TheStone.

B.
Quote Reply
Re: [TheStone] Anybody Home????? In reply to
Worked like a charm!! Thanks.

Is there a better or more detailed set of instructions for all that this program will do and how to develop routines like this?
Quote Reply
Re: [604] Anybody Home????? In reply to
Digging up an old thread here with a question...

In the example posted by 604, is it possible to have the current ID number (in the example this is 'A01') not hard-coded into the code? I have tried changing it to <%ID%> so it gets the proper related data, but it doesn't recognize the tag. If I have it hard-coded in as, say, 12 (a typical ID number of mine), it works.

Any ideas? Thank you
Quote Reply
Re: [Dempsey] Anybody Home????? In reply to
I figured out my answer with help from this thread

Code:
sub {
my $tags = GT::Template->tags;
my $id = $tags->{ID};
my ($table, $field) = @_;
my $sth = $DB->table($table)->select({ $field => $id});
my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}

Change {ID} if neccessary to the ID column name, and call with:

Code:
<%name_of_above_sub('related_table_name', 'related_column_in_related_table')%>

Last edited by:

Dempsey: Sep 5, 2009, 2:48 PM
Quote Reply
Re: [Dempsey] Anybody Home????? In reply to
I'm hoping someone can provide me with some addition help on this...

Using the code in my post above, does anybody know how I could sort the results? I use loops, but the data is pulled from a different database through a subroutine so sb= in the URL doesn't work.

Also wondering if anybody would know how to add a max results limitation? I use this for letting users add comments to records, with the comments being stored in a seperate database, and I don't want to have the comments end up making the pages a mile long after a few years. I'm sure this code can be useful to a lot of people in the DBMan SQL community.

Any ideas? Thanks
Quote Reply
Re: [604] Anybody Home????? In reply to
Can anyone help with this?
Quote Reply
Re: [Dempsey] Anybody Home????? In reply to
I've decided to use iframes to accomplish what I need. With if and else checks to show the appropriate sized iframe I should be able to make it work seamlessly
Quote Reply
Re: [Dempsey] Anybody Home????? In reply to
 
Sorry about the late reply. Below are answers for your questions:

>> is it possible to have the current ID number (in the example this is 'A01')

<%get_info('interest', 'profile_id', $ID)%>

>> how I could sort the result
>> how to add a max results limitation

You can use $tab->select_options('GROUP BY...', 'ORDER BY field ASC/DESC, ..', 'LIMIT n_limit OFFSET n_offset') method for grouping, sorting, or setting a max results limitation. e.g.

sub {
my ($table, $field, $value) = @_;

my $tab = $DB->table($table);
$tab->select_options('ORDER BY column_name', 'LIMIT 25');
return { loop_hash => $tab->select({ $field => $value })->fetchall_hashref };

}

B.
Quote Reply
Re: [604] Anybody Home????? In reply to
Thank you! I have found the solution with iframes, but I have some more instances on my dbsql where this code may come in handy.
Quote Reply
Re: [604] Anybody Home????? In reply to
I've tried to incorporate this code, but couldn't get it working. If you could, would you be able to show me what my new global would be with a "sort by" column and limit of 25 and descending? My current global:

Code:
sub {
my $tags = GT::Template->tags;
my $id = $tags->{ID};
my ($table, $field) = @_;
my $sth = $DB->table($table)->select({ $field => $id});
my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}


Thanks for the help.

Last edited by:

Dempsey: Sep 17, 2009, 9:53 PM
Quote Reply
Re: [Dempsey] Anybody Home????? In reply to
Here is the code for that:

my $tab = $DB->table($table);
$tab->select_options('ORDER BY column_name', 'LIMIT 25');
my $sth = $tab->select({ $field => $id});
....

B.
Quote Reply
Re: [604] Anybody Home????? In reply to
Thanks, it's working!

I'm now trying to use this code in another area of my website, that pulls photos from another table. I have it set up to the point where it's pulling the photo name (whatever.jpg) from the file column of another table, using this global:

Code:
sub {
my $tags = GT::Template->tags;
my $id = $tags->{ID};
my ($table, $field) = @_;
my $sth = $DB->table($table)->select({ $field => $id});
my $tab = $DB->table($table);
$tab->select_options('ORDER BY Date DESC', 'LIMIT 25');
my $sth = $tab->select({ $field => $id});
my @output;
while (my $rs = $sth->fetchrow_hashref ) {
push @output, $rs;
}
return { loop_hash => \@output };
}

What I would like help with, if you can, is changing the above global so that it returns the file path name (such as example: "2/2-filename.jpg") included instead of just the bare filename.jpg

I have the following global which does this perfectly when the file is in the same table:

Code:
sub {
my $tags = GT::Template->tags;
my $id = $tags->{TankID};
my ($table, $image_col) = @_;
my $file = $DB->table($table)->file_info($image_col, $id) or return "ID $id does not have a file attached.";
return $file->File_RelativePath();
}


So again, if you know how to essentially combine these two globals so that the first global returns file path name instead of just the file name, I would really appreciate the help. I have tried so many different combinations of the code to get it to work but haven't had any success. I always just get the "ID $id does not have a file attached." Any ideas?

Also, this will unfortunately only be useful if I can use the loop command on my template to show the file path of all search results. Am I going to be able to do this since it's calculated in a global?

Last edited by:

Dempsey: Sep 19, 2009, 10:46 AM
Quote Reply
Re: [Dempsey] Anybody Home????? In reply to
Any ideas? Thank you