[ic] Problem with SQL query inside [perl] tag...

interchange-users@icdevgroup.org interchange-users@icdevgroup.org
Wed Oct 2 14:35:01 2002


Howdy! heres what I'm running:

interchange 4.8.6
mysql 3.23
apache 1.29
perl 5.6 (if it matters...)

I'm sorry about posting such a large chunk of code, but I'm really in a
fix. I'm attempting to write a script that generates sales reports, so
naturally im doing it in a [perl] tag. unfortunately, its doesn't always
work. I think the cause of it messing up is somewhere in those 'if
($CGI->{blah blah})' statements. If I make it so all the CGI-checking
if-statements return false, my query returns data, as it should. but when
any one of those if-statements returns true, and the code inside that
particular block is run, it doesn't return data.  for debugging purposes,
this script is displaying the SQL query that was run to retrieve that data.
The query always gets displayed, whether or not any data is displayed. And
when I run the generated query in the mysql client, it works fine, giving
me the proper result set. When I do a $Tag->uneval() on the resultset
reference ($hash_result), it returns nothing but '[]'. So I'm guessing
either the query isn't getting run (I should go check mysql's logs...), or
my array of hashes is getting dropped somewhere after that... Any ideas???
I am totally stumped. Thanks a whole bunch in advance! The code follows:

[perl tables="orderline products" reparse=1]

  my $sql =
      "select
        orderline.description as 'description',
      sum(truncate(orderline.quantity,0)) as 'quantity',
      sum(truncate(orderline.subtotal,2)) as 'subtotal',
      orderline.order_date as 'date'
    from
      orderline,
      products
    where
      orderline.sku=products.sku";


  if ($CGI->{day}) {
    $sql .= "
    and
      orderline.order_date = '[cgi date]'";
  }

  if ($CGI->{week}) {
    $sql .= "
    and
      week(orderline.order_date) = week([cgi date])";
  }

if ($CGI->{month}) {
      $sql .= "
      and
            date_format(orderline.order_date,'%M') = '[cgi month]'
      and
            orderline.order_date between trim(concat([cgi year]-1,'0701'))
and trim(concat([cgi year],'0631'))";
}


if ($CGI->{quarter}) {

            @quarters = (($year - 1) . '0701',
                                                 ($year - 1) . '1001',
                                                 $year . '0101',
                                                 $year . '0401',
                                                 $year . '0701');

            if ($CGI->{quarter} =~ /1/) {
                  $sql .= "
                        and
                              orderline.order_date >= '$quarters[0]'\n
                        and
                              orderline.order_date < '$quarters[1]'";

            } elsif ($CGI->{quarter} =~ /2/) {
                  $sql .= "
                        and
                              orderline.order_date >= '$quarters[1]'
                        and
                              orderline.order_date < '$quarters[2]'";

            } elsif ($CGI->{quarter} =~ /3/) {
                  $sql .= "
                        and
                              orderline.order_date >= '$quarters[2]'
                        and
                              orderline.order_date < '$quarters[3]'";

            } elsif ($CGI->{quarter} =~ /4/) {
                  $sql .= "
                        and
                              orderline.order_date >= '$quarters[3] '
                        and
                              orderline.order_date < '$quarters[4]'";

}


if ($CGI->{year}) {
      $sql .= "
      and
            orderline.order_date >= trim(concat([cgi year]-1,'0701'))
      and
            orderline.order_date <= trim(concat([cgi year]1,'0631'))";
}


  $sql .= "
    group by orderline.description;";

  my $results = $Tag->query( { sql => $sql, } );
  my ( $same_results, $col_name_hashref, $col_name_arrayref)  = $Tag->query
( { sql => $sql, } );

  my $hash_results = $Tag->query( {     sql => $sql,  hashref =>
'my_results' } );

  $results_display = "";

  for $i ( 1 .. $#{$hash_results} ) {

    $results_display .= "
      <TR>
        <TD ALIGN=LEFT>$hash_results->[$i]->{description}</TD>
        <TD ALIGN=MIDDLE>$hash_results->[$i]->{quantity}</TD>
        <TD ALIGN=LEFT>\$$hash_results->[$i]->{subtotal}</TD>
        <TD ALIGN=LEFT><a href=\"[area @@MV_PAGE@@]&date=$hash_results->
[$i]->{date}&day=1\">today</a> - <a href=\"[area @@MV_PAGE@@]&date
=$hash_results->[$i]->{date}&week=1\">this week</a></TD>
      </TR>";
  }

  return "$results_display" . "<tr><td>" . $sql . "</td></tr>";


[/perl]

I apologize if that is nasty looking code. Its pretty much my first perl
script ('hello world' doesn't count, does it?), and im no good at ITL (i
break into a cold sweat if I need something that isn't in foundation...),
so... bare with me. Hopefully I'll get better at it :).

Jonny

P.S.
Thankyou Racke for that explanation of what $Tag->uneval() does. Infinitely
helpful :).