walkingthecow has asked for the wisdom of the Perl Monks concerning the following question:

Good morning and Happy Easter fellow monks. I come to you with a problem that has my mind melted to slush. Maybe there's a way to do this with my SQL query, or I can do it in HTML::Template, or it may be easiest to do it in Perl and send it to HTML::Template, but here's the deal: I have data in a MySQL table that basically looks like this: 
03-13-2014,Bob Jones,Pending
03-13-2014,George Manuel,Pending
03-13-2014,Frankie Avalon,Approved
03-13-2014,Robert Garcia,Approved
03-14-2014,John Doe,Pending

[download]
I grab this data like so: 
$sth = $dbh->prepare(
            " 
                SELECT date,name,status
                FROM requests 
                WHERE date >= '$date' 
                ORDER BY date
            "
        );
        $sth->execute();
        my $reqs;
        push @{$reqs}, $_ while $_ = $sth->fetchrow_hashref();

[download]
I send this information to HTML::Template: 
my $template = HTML::Template->new(filename => 'approve.html');
$template->param(REQS => $reqs);
print $template->output();

[download]
<form>
    <table class=approve>
        <tr>
            <th class=approve>Approve</th>
            <th class=approve>Decline</th>
            <th class=approve>Date</th>
            <th class=approve>Name</th>
            <th class=approve>Status</th>
        </tr>
        <TMPL_LOOP NAME=REQS>
                <tr class=approve>
                    <td class=approve><input type=radio name=approve v
+alue=Approved></td>
                    <td class=approve><input type=radio name=decline v
+alue=Declined></td>
                    <td class=approve>
                        <span class="hotspot" onmouseover="tooltip.sho
+w('<strong>Approved Vacation Requests</strong><br /><TMPL_VAR DATE>')
+;" onmouseout="tooltip.hide();">
                            <TMPL_VAR NAME=DATE>
                        </span>
                    </td>
                    <td class=approve><TMPL_VAR NAME=NAME</td>
                    <td class=approve><TMPL_VAR NAME=STATUS</td>
                </tr>
            </TMPL_IF>
        </TMPL_LOOP>
        <tr class=approve>
            <th class=approve colspan=6>
                <input type=submit value="Approve / Decline">
                <input type=hidden value=approve name=state>
            </th>
        </tr>
    </table>
</form>
<script type="text/javascript" language="javascript" src="js/script.js
+"></script>

[download]
Now, what I am trying to do is basically say "Show the user the pending requests in the table, AND, if there are any approved requests, show them the approved requests in a javascript tooltip. The javascript tooltip code is here: 
                        <span class="hotspot" onmouseover="tooltip.sho
+w('<strong>Approved Vacation Requests</strong><br /><TMPL_VAR DATE>')
+;" onmouseout="tooltip.hide();">

[download]
The problem I am having is this: I can easily put the pending requests in a table and present this in my HTML template file. However, I am trying to figure out how to create the tooltip that is connected IF the query has approved requests on the same date as the pending responses. I was thinking, but didn't know how to start this (new to HTML::Template), that I could grab the approved responses in a hash whose structure is like so: 
{
   '03-13-2014' => 'Frankie Avalon,Robert Garcia',
};

[download]
and somehow say if the date (as hash key) of the approved response matches the date of pending response, then add tooltip. I know that this is probably a simple problem to solve but at this point my mind has degraded to the point of total confusion (thus my terrible code). Sorry if this post is somewhat rambling. Please let me know if any clarification is needed.

Replies are listed 'Best First'.
Re: HTML::Template if key of hash matches value of hash.
by moritz (Cardinal) on Mar 31, 2013 at 15:16 UTC

    The trick is to add some more logic to your perl code: 

    my $has_approved_records = 0;
my $reqs;
while (my $row = $sth->fetchrow_arrayref) {
    $has_approved_records 1 = if $row->{status} eq 'Approved';
    push @$reqs, $row;
}
...
$template->param(REQS => $reqs, HAS_APPROVED_RECORDS => $has_approved_
+records);

    [download]

    And in the template

    <TMPL_IF HAS_APPROVED_RECORDS>
    <span class="hotspot" onmouseover="tooltip.show('<strong>Approved 
+Vacation Requests</strong><br /><TMPL_VAR DATE>');" onmouseout="toolt
+ip.hide();">
</TMPL_IF>

    [download]
    Perl 6 - the future is here, just unevenly distributed
[reply]
[d/l]
[select]
      While not exactly what I was looking for (my fault for lacking clarity in my post), your reply was very helpful. Thank you. I will be sure to post my code when I figure it out. I figured a break was what was needed to solve this issue.
[reply]
        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();

        [download]
        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>

        [download]
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom