Gossamer Forum
Home : Products : DBMan SQL : Discussion :

select field based on table data

Quote Reply
select field based on table data
An introduction to this thread is here

in dbman sql 2, is there an equivalent of the useful build_select_field_from_db subroutine in dbman? (builds a select field on a search form (or anywhere else) based on data already in a field in the db.)

Many thanks for any help / suggestions.
Tim Ault
Oxford UK
Quote Reply
Re: [timbo] select field based on table data In reply to
You can use the tags below to generate:

<%Dbsql::HTML::generate_select('field_name')%>: a select box for a field which form type is select
<%Dbsql::HTML::generate_checkbox('field_name')%>: check boxes for a field which form type is checkbox
<%Dbsql::HTML::generate_radio('field_name')%>: radios for a field which form type is radio

TheStone.

B.
Quote Reply
Re: [TheStone] select field based on table data In reply to
Thanks that's helpful.

But is there a way I can put on the search form a select field where the dropdown list is based on the data currently in the table, not on a static list in the table definition?

Last edited by:

timbo: Aug 26, 2003, 12:08 AM
Quote Reply
Re: [timbo] select field based on table data In reply to
No, it doesn't provide this feature. But you can create a global template like:

sub {
my $name = shift;
my $results = $DB->table($name)->select()->fetchall_hashref;
return { loop_select => $results };
}

and the html code should be:

<%global_name('table_name')%>
<select name="select_name">
<%loop loop_select%>
<option value="<%column_id%>"><%column_description%></option>
<%endloop%>
</select>

Hope that helps.

TheStone.

B.
Quote Reply
Re: [TheStone] select field based on table data In reply to
Please could someone point me to another thread that explains how to do this? (Still trying to get my brain round dbman-sql 2.... )

thanks Cool
Tim Ault
Oxford UK
Quote Reply
Re: [timbo] select field based on table data In reply to
Have you tried the code in my previous message?

TheStone.

B.

Last edited by:

TheStone: Sep 3, 2003, 9:36 AM
Quote Reply
Re: [TheStone] select field based on table data In reply to
So a global template is a piece of code that can be referenced from anywhere in the program?

Is there a tutorial somewhere on how to make global templates? I had a quick search but couldn't find anything.

The code looks just what we need, if you could just help me understand what to do with it! Sorry for being thick!
Tim Ault
Oxford UK
Quote Reply
Re: [timbo] select field based on table data In reply to
You can create a global at admin - templates - global templates

TheStone.

B.
Quote Reply
Re: [604] select field based on table data In reply to
Digging up an old thread here...

I've tried this code and it works, thanks! Is there something that can be done to sort the contents of the select field that has its data pulled from another table? It currently works, but the contents aren't alphabetical.

Thanks!
Quote Reply
Re: [Dempsey] select field based on table data In reply to
Yes, there is. You can use $tab->select_options('ORDER BY field_name1 ASC/DESC, field_name2 ASC/DESC, ...') for sorting. Below is an example:

sub {
my $name = shift;

my $tab = $DB->table($name);
$tab->select_options('ORDER BY field_name');
return { loop_select => $tab->select()->fetchall_hashref };
}

B.
Quote Reply
Re: [604] select field based on table data In reply to
Thanks, that works great.

Last thing I would like to do is add something that checks the column and only adds certain records to the select list.

Let's say that right now it's making a select list of the contents of a column called "Fruit". I would like to add a check so that it only shows "Fruit" that contains another column with a value of "Apples"

Is this possible? Thank you
Quote Reply
Re: [Dempsey] select field based on table data In reply to
Sure. You can either do 'exact match' or 'like' search. Below are examples:

1. Exact match:
....
return { loop_select => $tab->select({ Fruit => 'Apples' })->fetchall_hashref };

2. 'like': search for 'apple', 'apples', 'apple pie', etc...
....
$tab->select_options('ORDER BY field_name');
require GT::SQL::Conditions;
return { loop_select => $tab->select(GT::SQL::Condition->new(Fruit => 'like' => '%Apple%' })->fetchall_hashref };

B.
Quote Reply
Re: [604] select field based on table data In reply to
Worked like a charm, thanks!
Quote Reply
Re: [604] select field based on table data In reply to
Is there a way that when the form is loaded, the current entry is selected on the select list? I'm trying the following but it's not working:

<%loop loop_select%>
<option value="<%Fruit%>" <%if FruitNameColumn eq <%Fruit%> %> selected<%endif%>><%Fruit%></option>
<%endloop%>