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

[interchange-users@icdevgroup.org (interchange-users@icdevgroup.org)]

Howdy! heres what I'm running:

interchange 4=2E8=2E6
mysql 3=2E23
apache 1=2E29
perl 5=2E6 (if it matters=2E=2E=2E)

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

[perl tables=3D"orderline products" reparse=3D1]

  my $sql =3D
   	"select
  	  orderline=2Edescription as 'description',
      sum(truncate(orderline=2Equantity,0)) as 'quantity',
      sum(truncate(orderline=2Esubtotal,2)) as 'subtotal',
      orderline=2Eorder_date as 'date'
    from
      orderline,
      products
    where
      orderline=2Esku=3Dproducts=2Esku";


  if ($CGI->{day}) {
    $sql =2E=3D "
    and
      orderline=2Eorder_date =3D '[cgi date]'";
  }

  if ($CGI->{week}) {
    $sql =2E=3D "
    and
      week(orderline=2Eorder_date) =3D week([cgi date])";
  }

if ($CGI->{month}) {
	$sql =2E=3D "
	and
		date_format(orderline=2Eorder_date,'%M') =3D '[cgi month]'
	and
		orderline=2Eorder_date between trim(concat([cgi year]-1,'0701')) and=20=
trim(concat([cgi year],'0631'))";
}


if ($CGI->{quarter}) {

		@quarters =3D (($year - 1) =2E '0701',
								 ($year - 1) =2E '1001',
								 $year =2E '0101',
								 $year =2E '0401',
								 $year =2E '0701');
								
		if ($CGI->{quarter} =3D~ /1/) {
			$sql =2E=3D "
				and
					orderline=2Eorder_date >=3D '$quarters[0]'\n
				and
					orderline=2Eorder_date < '$quarters[1]'";
		
		} elsif ($CGI->{quarter} =3D~ /2/) {
			$sql =2E=3D "
				and
					orderline=2Eorder_date >=3D '$quarters[1]'
				and
					orderline=2Eorder_date < '$quarters[2]'";

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

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

}


if ($CGI->{year}) {
	$sql =2E=3D "
	and
		orderline=2Eorder_date >=3D trim(concat([cgi year]-1,'0701'))
	and
		orderline=2Eorder_date <=3D trim(concat([cgi year]1,'0631'))";
}


  $sql =2E=3D "
    group by orderline=2Edescription;";

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

  my $hash_results =3D $Tag->query( {     sql =3D> $sql,  hashref =3D> 'my_result=
s' }=20=
);

  $results_display =3D "";

  for $i ( 1 =2E=2E $#{$hash_results} ) {

    $results_display =2E=3D "
      <TR>
        <TD ALIGN=3DLEFT>$hash_results->[$i]->{description}</TD>
        <TD ALIGN=3DMIDDLE>$hash_results->[$i]->{quantity}</TD>
        <TD ALIGN=3DLEFT>\$$hash_results->[$i]->{subtotal}</TD>
        <TD ALIGN=3DLEFT><a href=3D\"[area=20=
@@MV_PAGE@@]&date=3D$hash_results->[$i]->{date}&day=3D1\">today</a> - <a=20=
href=3D\"[area @@MV_PAGE@@]&date=3D$hash_results->[$i]->{date}&week=3D1\">this=20=
week</a></TD>
      </TR>";
  }

  return "$results_display" =2E "<tr><td>" =2E $sql =2E "</td></tr>";


[/perl]

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

Jonny

P=2ES=2E
Thankyou Racke for that explanation of what $Tag->uneval() does=2E Infinitely=20=
helpful :)=2E