Gossamer Forum
Home : Products : DBMan SQL : Discussion :

Build select menu from other table

Quote Reply
Build select menu from other table
Can't get my head around this one:

How do you have a select menu automatically generated from another table? I'm trying to associate several contacts (lawyers, realtors, processors, etc.) with each record in a table of real estate deals. I'm keeping the contacts in a separate contacts table, but I want users to be able to choose each of these contacts from several select menus (or, more likely, multiple-select menus) when they add a new record to the first table.

I'm new to SQL, so pardon me if this is a newbie question.
Quote Reply
Re: [Halito] Build select menu from other table In reply to
You might add a new global which returns a select tag (or any HTMLs) like the example below:

Code:
sub {
my $tags = shift;
my $table = $DB->table('contacts');
my $sth = $table->select (['Column1', 'Column2']);
my $output = '<select name=select_name multiple>';
while (my $row = $sth->fetchrow_hashref) {
$output .= '<option value='.$row->{Column1}.'>'.$row->{Column2}.'</option>';

}
$output .= '</select>';

return $output;
}

Last edited by:

jean: Mar 1, 2002, 11:02 AM
Quote Reply
Re: [jean] Build select menu from other table In reply to
Thanks for the quick reply! I modified your subroutine by changing your references to Column1 and Column2 to First_name and Last_name, which correspond to column names in my contacts table. I wasn't sure where I should stick this subroutine, so I put it in HTML.pm and I named it sub build_contact_select. Then, in one of my templates (add_form.html), I tried calling the new subroutine with the following command:

<%Dbsql::HTML::build_contact_select%>

But the browser displays the following text in the place where the select menu should appear:

Error: Unable to load module: Dbsql::HTML. Reason:
Error: No subroutine 'Dbsql::HTML::build_contact_select' in 'Dbsql/HTML.pm',
Error: No subroutine 'Dbsql::HTML::build_contact_select' in 'Dbsql.pm'


When I run the Emacs Perl debugger on the HTML.pm file, I get the following warning:
Global symbol "$DB" requires explicit package name at /path-to-DBSQL-directory/admin/Dbsql/HTML.pm line 206.

Does this mean that the scalar $DB needs to be replaced by a package name? Being a newbie, I tried replacing it with a couple of package names like GT::CGI and GT::SQL::Table, but each time I get the fatal error screen with the first line that reads:

Unknown method 'table' called at /path-to-DBSQL-directory/admin/Dbsql/HTML.pm line 206.

Sorry for opening this can of worms, but there are probably other users who would benefit from learning this procedure. It's probably very simple, but I'm still trying to get familiar with all the different files and what they do.
Quote Reply
Re: [Halito] Build select menu from other table In reply to
No, just add the code above to the globals (Admin panel >> Templates >> Globals) like :

global_var => sub {
.......
}


then put a new tag <%global_var%> in your templates.

Cheers,

jean(at)Gossamer Threads
Quote Reply
Re: [jean] Build select menu from other table In reply to
Aha! Now I see. I keep trying to bypass the web interface, but it works like a charm they way you showed me. I'm really starting to like DBSQL!Smile

Still, it's unfortunate that the only way to manage these automatically generated select menus is through the browser. I'll be dealing with about 15 different menus, and it would be nice to manage the code in an editor. It appears the program doesn't store these globals in any of the ASCII files, but instead it writes them to a binary file (or files) within the /var/lib/mysql/name_of_database directory. At least that's the path on my machine. If there's another way to manage the globals, please let me know.

Uh-oh. It suddenly occurs to me that I need to build these menus using queries to another field, contact_type, in my contacts table. That way, I can have all the lawyers appear in one select menu, all the borrowers in another menu, etc.

It's probably easy if you only need one menu and one query, but I bet you're going to tell me it gets complicated to generate multiple menus built with multiple queries on one page, right? Thanks again for all your great help. Sorry to be such a bother...
Quote Reply
Re: [Halito] Build select menu from other table In reply to
Thanks for asking this question. It was an exact answer to my question. That little bit of searching paid off. Now onto my next problem. Crazy