[Zope-DB] ZSQL

Israel Carr icarr at compx.com
Mon Apr 17 16:17:03 EDT 2006


I have a table with the columns:
id, username, trans_type

Sample records:
1,user1,trans1
2,user1,trans3
3,user2,trans4
4,user2,trans2
5,user1,trans1
6,user2,trans3


I would like to generate output that contains the username and the
total(count) number of each trans_type.  Would also like to have a total
of all trans_type.
Roughly:
        trans1  trans2  trans3  trans4
user1     2        0       1       0
user2     0        1       1       1
   TOTAL  2        1       2       1


I have a ZSQL method with the following:
select COUNT(*) 
from table 
where trans_type = <dtml-sqlvar transtype type=string> 
	and username = <dtml-sqlvar username type=string>

When testing and feeding parameters it does give me the count for one
user and one transaction type.  


How can I create the desired output and have a general enough ZSQL
Method that I can feed these parameters?  I've been playing with some
<dtml-in> tags and trying to call the ZSQL method but am not really sure
how to pass parameters or if I'm way out in left field.  Would I need to
call the ZSQL method 8+ times to generate output like the table above?

I can do pretty basic form inputs into database and selects out based on
form or static values but this is a little more complex for me.  Any
help would be appreciated.

Israel



More information about the Zope-DB mailing list