The problem is that you are not naming your DBI queries and the content of the DBI object is getting mangled by you calling it again.
There are two solutions to this:
1. Create more than one DBI object like so....
[% USE q1 = DBI(.....) %]
[% USE q2 = DBI(.....) %]
Which is my preferred approach because some DBI connections do not allow you to have more than one result set open at any one time.
More on this in: http://www.template-toolkit.org/docs/plain/Modules/Template/Plugin/DBI.html look for Using Named DBI connections.
2. As you are using mysql you can actually get away with having more than one result set open so you can do something like this:
[% USE DBI(....) %]
[% results1 = DBI.query("the sql for your outer query") %]
[% FOREACH result = results %]
[% FOREACH inner = DBI.query("your inner sql") %]
[% END %]
[% END %]
You can find examples of this in t/dbi.t in the Template Toolkit distribution
Hope this helps
S
