Hi,
I am using a global to display my category listing, I would like to omit the leading "The" in the sort, but still display it, here is my code:
my ($db, $sth, $row, $oldletter, $letter, $i);
my $output="";
$db = $DB->table("Category");
$db->select_options("ORDER BY Name");
$sth = $db->select( ["ID","Name", "Full_Name"] );
my $cols = 1;
my $row_count = $sth->rows;
my $breakpoint = int (($row_count) / $cols) + ( (($row_count) % $cols) ? 1 : 0);
my $width = int (100 / $cols);
my $output = qq|\n|;
while ($row = $sth->fetchrow_hashref) {
$letter = uc(substr($row->{Name}, 0, 1));
if ($letter !~ /$oldletter/i ){
$output .= qq|<div class="col-sm-12 center"><div class="feature-box"><div class="feature-box-info"><h4 class="tall"><a name="#$letter"><strong>$letter</strong></a></h4></div></div></div>\n|;
$oldletter = $letter;
}
my $name = $db->as_url($row->{Full_Name});
$output .= qq|<div class="col-sm-4"><div class="feature-box"><div class="feature-box-info"><h4 class="tall"><a href="$CFG->{build_root_url}/$name/">$row->{Name}</a></h4></div></div></div>\n|;
($i > 0) and !($i % $breakpoint) and ($output .= qq|\n\n|);
$i++;
}
$output .= "\n";
return $output;
}
I would have to use a statement like:
but not exactly sure where and how... Anyone ?
I am using a global to display my category listing, I would like to omit the leading "The" in the sort, but still display it, here is my code:
Code:
sub { my ($db, $sth, $row, $oldletter, $letter, $i);
my $output="";
$db = $DB->table("Category");
$db->select_options("ORDER BY Name");
$sth = $db->select( ["ID","Name", "Full_Name"] );
my $cols = 1;
my $row_count = $sth->rows;
my $breakpoint = int (($row_count) / $cols) + ( (($row_count) % $cols) ? 1 : 0);
my $width = int (100 / $cols);
my $output = qq|\n|;
while ($row = $sth->fetchrow_hashref) {
$letter = uc(substr($row->{Name}, 0, 1));
if ($letter !~ /$oldletter/i ){
$output .= qq|<div class="col-sm-12 center"><div class="feature-box"><div class="feature-box-info"><h4 class="tall"><a name="#$letter"><strong>$letter</strong></a></h4></div></div></div>\n|;
$oldletter = $letter;
}
my $name = $db->as_url($row->{Full_Name});
$output .= qq|<div class="col-sm-4"><div class="feature-box"><div class="feature-box-info"><h4 class="tall"><a href="$CFG->{build_root_url}/$name/">$row->{Name}</a></h4></div></div></div>\n|;
($i > 0) and !($i % $breakpoint) and ($output .= qq|\n\n|);
$i++;
}
$output .= "\n";
return $output;
}
I would have to use a statement like:
Code:
select Name, case when Name like 'The %' then trim(substr(Name from 4)) else Name end as Name2 from tablename order by Name2;but not exactly sure where and how... Anyone ?