title: How to list the product by category_id and order by item_id from database?
body: <p>i have <strong>item</strong> table where columns are:</p>
  
  <ol>
  <li>item_id    =      01,   02,   03,   04,   05,     06,     07,     08....</li>
  <li>ctgr_id    =      01,   01,   01,   02,   02,     03,     03,     03....</li>
  <li>item_name  =    Wcar, Bcar, Rcar, Abus, Bbus, Qtruck, Ttruck, Ftruck....</li>
  </ol>
  
  <p>And have one more table called <strong>ctgr</strong> where columns are:</p>
  
  <ol>
  <li>ctgr_id = 01, 02, 03...</li>
  <li>ctgr_name = car, bus, truck... </li>
  </ol>
  
  <p><strong>Now coding are showing the product like as below:</strong></p>
  
  <ul>
  <li>car
  
  <ul>
  <li>01 - Wcar</li>
  </ul></li>
  <li>car
  
  <ul>
  <li>02 - Bcar</li>
  </ul></li>
  <li>car
  
  <ul>
  <li>03 - Rcar</li>
  </ul></li>
  <li>bus
  
  <ul>
  <li>04 - Abus </li>
  </ul></li>
  <li>bus
  
  <ul>
  <li>05 - Bbus</li>
  </ul></li>
  <li>truck
  
  <ul>
  <li>06 - Qtruck</li>
  </ul></li>
  <li>truck
  
  <ul>
  <li>07 - Ttruck</li>
  </ul></li>
  <li>truck
  
  <ul>
  <li>08 - Ftruck</li>
  </ul></li>
  </ul>
  
  <p><strong>But i want to show the product like as below:</strong></p>
  
  <ul>
  <li><p>car</p>
  
  <ul>
  <li>01 - Wcar</li>
  <li>02 - Bcar</li>
  <li>03 - Rcar</li>
  </ul></li>
  <li><p>bus</p>
  
  <ul>
  <li>04 - Abus</li>
  <li>05 - Bbus</li>
  </ul></li>
  <li><p>truck</p>
  
  <ul>
  <li>06 - Qtruck</li>
  <li>07 - Ttruck</li>
  <li>08 - Ftruck</li>
  </ul></li>
  </ul>
  
  <p><strong>from index.cgi</strong>:</p>
  
  <pre><code>sub action_index {
      my $self = shift;
      $self-&gt;set_property(paginate_row_count =&gt; 0);
      my $m_item = $self-&gt;model('item');
  
      $self-&gt;set_data(
          'ctgrs',
          $self-&gt;model('ctgr')-&gt;get_list('del_flg = 0 and show_web = 1')
      );
  
      $m_item-&gt;set_input_params;
      $self-&gt;search_execute($m_item-&gt;get_list_sql-&gt;where("v_item.item_flg = 1"));
      $self-&gt;set_data('cart',$self-&gt;get_list_cart);
  }
  </code></pre>
  
  <p><strong>from index.html</strong>:</p>
  
  <pre><code>&lt;table&gt;
      [% if(@{$c{list}}&gt;0) %]
          [% foreach my $rec (@{$c{list}}) %]
  
              [% if($rec-&gt;{is_item_public}==1) %]
  
                      &lt;tr&gt;
                          &lt;td align="center"&gt;
                          [# $rec-&gt;{ctgr_name} #]
                          &lt;/td&gt;
                      &lt;/tr&gt;
                      &lt;tr&gt;    
                          &lt;td class="item"&gt;
                          [# $rec-&gt;{item_id} #]&lt;br&gt;
                          [# $rec-&gt;{item_name} #]&lt;br&gt;
                          &lt;/td&gt;
                      &lt;/tr&gt;
              [% end %]
           [% end %]
      [% else %]
      &lt;tr&gt;
      &lt;td&gt;no data&lt;/td&gt;
      &lt;/tr&gt;
      [% end %]
  &lt;/table&gt;
  </code></pre>
  
  <p><strong>from file TinyApplication.pm</strong></p>
  
  <pre><code>sub search_execute {
      my $self = shift;
      my $sql  = shift;
  
      my @wheres = ();
  
      foreach my $key (keys %{$self-&gt;{search_params}}){
          my $value = $self-&gt;param("search/${key}");
          if(defined $self-&gt;{search_params}-&gt;{$key}-&gt;{func}){
              $self-&gt;{search_params}-&gt;{$key}-&gt;{func}($sql,$value);
          }
          elsif (defined $value &amp;&amp; $value ne '') {
              my $type = defined $self-&gt;{search_params}-&gt;{$key}-&gt;{type}
                ? $self-&gt;{search_params}-&gt;{$key}-&gt;{type}
                : '= ?';
              my $field = defined $self-&gt;{search_params}-&gt;{$key}-&gt;{field}
                ? $self-&gt;{search_params}-&gt;{$key}-&gt;{field}
                : $key;
              $value =~s/^\s+//;
              $value =~s/\s+$//;
              my @where_or = ();
              foreach (split(/\s+/,$value)) {
                  my $replace = $self-&gt;{search_params}-&gt;{$key}-&gt;{replace} || '?';
                  $replace =~ s/\?/$_/;
                  push @where_or, ["${field} ${type}",$replace];
              }
              if ($self-&gt;{is_search_where_or}) {
                  push @wheres,@where_or;
              }
              else {
                  $sql-&gt;where_or( @where_or );
              }
          }
          elsif ( defined $self-&gt;{search_params}-&gt;{$key}-&gt;{default} ) {
              my $field = $self-&gt;{search_params}-&gt;{$key}-&gt;{field} || $key;
              my $type = $self-&gt;{search_params}-&gt;{$key}-&gt;{type} || '= ?';
              if( $self-&gt;{is_search_where_or} ) {
                  push @wheres,
                    ["${field} ${type}", $self-&gt;{search_params}-&gt;{$key}-&gt;{default}];
              }
              else{
                  $sql-&gt;where(
                      ["${field} ${type}", $self-&gt;{search_params}-&gt;{$key}-&gt;{default}] );
              }
          }
      }
      if( $self-&gt;{is_search_where_or} ) {
          $sql-&gt;where_or(@wheres) if(@wheres);
      }
      my $tmp_orderby = $sql-&gt;{ORDERBY};
      $sql-&gt;{ORDERBY} = [];
      my $total = $sql-&gt;get_count(@_); 
      if($total&gt;0){
          my $page = $self-&gt;param('page') || 1;
          $page = 1 unless(($page=~/^\d+$/) &amp;&amp; $page&gt;=1);
          my $offset = ($page-1)*$self-&gt;{paginate_row_count};
          $sql-&gt;{ORDERBY} = $tmp_orderby; 
          if($self-&gt;{paginate_row_count}&gt;0){
              $sql-&gt;limit($offset,$self-&gt;{paginate_row_count});
          }
          my $sth = $sql-&gt;execute(@_);
          my @data = ();
          while(my $ra = $sth-&gt;fetchrow_hashref){
              $self-&gt;filter_in_all($ra);
              push @data,$ra;
          }
          $self-&gt;set_data('list',\@data);
      }
  }
  </code></pre>