Just wanted to post my code as an update on how I did this in case anyone is ever interested in the same thing:
The trick was in my SQL statement...
my $sth = $dbh->prepare(
"
select t.id,t.user,t.email,t.status,t.date,t.req_date,
( SELECT
GROUP_CONCAT( (case when status = 'Approved' then user
+ end) separator ', ')
FROM requests s WHERE s.date = t.date)
AS approved
FROM requests t
WHERE status = 'Pending'
AND req_email = '$email'
AND date >= '$date'
ORDER BY employee,date
"
);
$sth->execute();
my $reqs;
push @{$reqs}, $_ while $_ = $sth->fetchrow_hashref();
my $template = HTML::Template->new( filename => 'approve.html'
+ );
$template->param(
REQS => $reqs,
);
print $template->output();
And here's my template code:
<TMPL_IF REQS>
<form>
<table class=approve>
<tr>
<th class=approve>Approve</th>
<th class=approve>Decline</th>
<th class=approve>Date</th>
<th class=approve>Employee</th>
<th class=approve>Status</th>
<th class=approve>Request Date</th>
</tr>
<TMPL_LOOP NAME=REQS>
<tr class=approve>
<td class=approve><input type=radio name=apid<TMPL
+_VAR NAME=ID> value=Approved></td>
<td class=approve><input type=radio name=apid<TMPL
+_VAR NAME=ID> value=Declined></td>
<td class=approve>
<TMPL_IF NAME=APPROVED>
<span class="hotspot" onmouseover="tooltip
+.show('<strong>Approved Vacation Requests</strong><br /><TMPL_VAR NAM
+E=APPROVED>');" onmouseout="tooltip.hide();">
</TMPL_IF>
<TMPL_VAR NAME=DATE>
<TMPL_IF NAME=APPROVED>
</span>
<script type="text/javascript" language="j
+avascript" src="js/script.js"></script>
</TMPL_IF>
</td>
<td class=approve><TMPL_VAR NAME=USER></td>
<td class=approve><TMPL_VAR NAME=STATUS></td>
<td class=approve><TMPL_VAR NAME=REQ_DATE></td>
<input type=hidden value=<TMPL_VAR EMAIL> name=emp
+_email<TMPL_VAR NAME=ID>>
<input type=hidden value=<TMPL_VAR EMPLOYEE> name=
+emp_name<TMPL_VAR NAME=ID>>
<input type=hidden value=<TMPL_VAR DATE> name=date
+<TMPL_VAR NAME=ID>>
</tr>
</TMPL_LOOP>
<tr class=approve>
<th class=approve colspan=6>
<input type=submit value="Approve / Decline">
<input type=hidden value=process name=state>
</th>
</tr>
</table>
</form>
<TMPL_ELSE>
<br /><b>There are no pending requests!</b><br />
</TMPL_IF>
|