CAN YOU HELP?


Do you know how do i do to make a dropdown from a column field of the database but listing only the data but non duplicates.


For example my table has a column called Status that can be set using checkboxes to Active or Inactive
so there are two possibilities for a record (Active or inactive) this is that every record has a active and inactive field column.

Now, my idea is make a dropdown that takes the data from the columns of the database that i know how to do it with this global but i cannot make it do not repeat in the list the data like:

active
active
active
Inactive.... and so on

many times....


this is what i am doing right now and it works but the problem is that the generated dropdown shows a list with Active and Inactive repeatly...acording the quantity of records in the table. So.... how can i just only mask this and show non duplicates in the list i mean just showing active and inactive option one time!
thanx



This is what i got now: see below: this create the list now but showing all with duplicates.....

<!-- SEARCH BOX STATUS -->

<%bselect('t_phonec')%> <!-- Select from t_phonec table -->
<select name='phonec_status'> <!-- The Column Field phonec_name -->
<option value=''>--</option> <!-- Set initial -- dropdown search mask -->
<%loop loop_select%> <!-- Loop all the table records -->
<option value='<%phonec_status%>'><%phonec_status%> <!-- Make dropdown list of values and search list -->
</option>
<%endloop%>


********************************************************


I tried also to make it simply just adding a select option list without pulling data from the table column field above but is does not work to search and i do not know how to make search to work using this below form:

<select>
<option value=''>--</option>
<option value='Active'>Active </option>
<option value='Inactive'>Inactive </option>
</select>
KevinW ANDRE
webmaster@cyberbrickell.com
KevinW ANDRE
webmaster@cyberbrickell.com

The SQL command is 'Select Distinct' when you want only the unique items in a field.
I'm not sure how that translate for you in DBManSQL 2 -- I'm still struggling with 1.02 which the select statement comes from db.cgi and works for me in my build_select_from_db code. which reads:

sub build_select_field_from_db {
# ---------------------------------------------
# Builds a select list from fields in the database.
#
my ($column, $value, $name) = @_;
my (@fields, $field, %selectfields, $ouptut, $fieldnum, $found);
# Make sure this is a valid field.
(grep $_ eq $column, @db_cols) or return "error building select field: no fields specified!";

$name = $name || $column;
my $query = qq!
SELECT DISTINCT $column FROM $db_table
!;
my $sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
# Make a select list out of those names.
$output = qq|<SELECT NAME="$name"><OPTION>---|;

while (($field) = $sth->fetchrow_array) {
($value =~ /^\s*$field\s*/) ?
($output .= "<OPTION SELECTED>$field\n") :
($output .= "<OPTION>$field\n");
}
$output .= "</select>";

$sth->finish;
return $output;
}

Maybe that will help you!
Lynette
Hollister, Ca

do i use this as a global? because i couldn;t make it work?

can you tell me if so how do i called?

thanx
KevinW ANDRE
webmaster@cyberbrickell.com

Take a look at the global template below:

sub {
my $results = $DB->table('table_name')->select(['DISTINCT column1', 'column2'])->fetchall_hashref;
return { loop_select => $results };
}

Your html code should look like:

<%global_name%>
<select name="select">
<%loop loop_select%>
<option value="<%column2%>"><%column2%></option>
<%endloop%>
</select>

Hope that helps.

TheStone.

B.

stone! this works eliminating the duplicates scalar values when adding it in the html but is not working when searching i mean it does not search by Actvie or Inactive as before it looks like it does not search ... or show me all records independenly it they are Active or Inactive matches.

I just wanna show when selecting active or inactive all records that has this match in the field status.

Another by the way what is the column1 in the global for? if in the html i do not use it?

tell me and thanx
KevinW ANDRE
webmaster@cyberbrickell.com

   
I just modified like this and now works emilinatin duplicates and also searching by options!
thanx Stone for your help!!!


this is what i use in the html

<%status_distinct('t_phonec')%>
<select name="phonec_status">
<option value=''>--</option>
<%loop loop_select%>
<option value="<%phonec_status%>"><%phonec_status%></option>
<%endloop%>
</select>

and the global changed like:

sub {
my $results = $DB->table('t_phonec')->select(['DISTINCT phonec_status'])->fetchall_hashref;
return { loop_select => $results };
}

i took off the column1


i hope this help for others that want to use distinct to eliminate scalar values in a select field that take data from a table. and want this select be used for searching.!
KevinW ANDRE
webmaster@cyberbrickell.com

STONE ! i am trying to fight with this code now? that i still have problems
maybe u can help to figure it out like the other to know where it is the problem!!


i want to sort alphabetically select field values that take data from a table column so when a add a new record this do not appears in the select list in the bottom but in the alphabetically order it should be!

i am using this but i do not know what i am doing worg

Global : bselect_ord

sub {
my $table = shift;
$DB->table($table)->select_options("ORDER BY phonec_name");
my $results = $DB->table($table)->select()->fetchall_hashref;
return { loop_select => $results };
}


Html

<%bselect_ord('t_phonec')%>
<select name='phonec_name'>
<option value=''>--</option>
<%loop loop_select%>
<option value='<%phonec_name%>'><%phonec_name%> </option>
<%endloop%>
KevinW ANDRE
webmaster@cyberbrickell.com

Just change it like:

sub {
my $table = shift;
my $db = $DB->table($table);
$db->select_options("ORDER BY phonec_name");
my $results = $db->select()->fetchall_hashref;
return { loop_select => $results };
}

That should work.

TheStone.

B.

Just to let you know this thread helped me solve an problem with distinct select's