MySQL, Perl, and LaTeX: Trying to loop through results by DAY (as a LaTeX section) for custom PDF report

[J M]

<http://stackoverflow.com/questions/18809731/mysql-perl-and-latex-trying-to-loop-through-results-by-day-as-a-latex-sectio#>

I have written a Perl script (shown below) to loop through my MySQL
database and then output a .tex file, as a sort of programmed custom
report. As it stands, it works rather well.

However, I'd like to "separate" each individual day (as a section in
LaTeX), so that I can eventually add a table of contents and see one day
per section (there can be many entries a day).

To clarify a little, I don't need assistance with the LaTeX part... I'm
just trying to figure out if it makes more sense to have Perl loop through
many MySQL queries (grabbing entries by a single day), or to have one query
(like I do now) and have Perl do something else.

Here's what the script looks like now (I know it's a little ugly, feel free
to give me pointers on anything weird you might notice):

Please let me know if you have any ideas/suggestions or need more
information.


#! /usr/bin/perl
use strict;use warnings;
use DBI;use DateTime::Format::MySQL;use HTML::Restrict;
# html removermy $hr = HTML::Restrict->new();
# database connection stuffmy $dbh = DBI->connect(
    "dbi:mysql:dbname=olin2",
    "user",
    "password",
    { RaiseError => 1 },        ) or die $DBI::errstr;

# query to grab ALL entries from `olin2`.`lobby`, ordered by datemy
$sth = $dbh->prepare("select l.id, l.date_added, l.username,
                        l.entry
                        from logbook l
                        order by l.date_added desc");
$sth->execute();
# the LaTeX document preambleprint<<EOF;
\\documentclass{article}
\\usepackage{framed,graphicx,xcolor}
\\usepackage[top=.5in, bottom=.5in, left=.5in, right=.5in]{geometry}
\\usepackage{etoolbox}
\\BeforeBeginEnvironment{shaded}{\\begin{center}
\\noindent\\begin{minipage}{.9\\linewidth}}
\\AfterEndEnvironment{shaded}{ \\end{minipage}\\end{center} }
\\raggedbottom
\\setlength{\\parskip}{.2em}
\\setlength{\\parindent}{0cm}
\\nonstopmode
\\setlength{\\topsep}{0pt}

\\begin{document}
\\definecolor{shadecolor}{gray}{.9}

EOF
my $row;
# set the `logbook` variableswhile ($row = $sth->fetchrow_hashref()) {
        my      $id = $row->{id};
        my      $date_added =
DateTime::Format::MySQL->parse_timestamp($row->{date_added});
        my      $username = $row->{username};
        my      $entry = $row->{entry};

        # filter some stuff out to make LaTeX behave
        $entry =~ s/<br \/>/\\\\/g;
        $entry =~ s/\\//g;
        $entry =~ s/\#/\\#/g;
        $entry =~ s/\&/\\&/g;
        $entry =~ s/\_/\\_/g;

        # filters out HTML tags
        $entry = $hr->process($entry);

        # get the first name of the `logbook`.`entry` author
        my $fname_query = 'select fname from users where username = ?';
        my @row1 = $dbh->selectrow_array($fname_query,undef,$username);
        my $fname = $row1[0];

        #print " \\fbox{";

        # each logbook entry is in a minipage (prevents line breaks in
the middle
        # of an entry)
        print " \\begin{minipage}{\\linewidth}";

        # the date and author are in a table (easier to read)
        print " \\begin{tabular}{l r} \\\\ ";
        print $date_added->strftime("%a, %d %b %Y at %l:%M %p & ") .
                'By: ' . $fname .
                '\\end{tabular} \\\\ \\vspace{.2em} \\\\' . $entry . '
\\smallskip ';

        # query to grab the comments associated with this particular
logbook entry
        my $comment_sth = $dbh->prepare("select c.date_added as cdate,
c.username cuser, c.comment from comments as c
                                                where c.logbook_id = ?
                                        order by c.date_added asc");
        $comment_sth->execute($id);

        # set up the comments variables
        while (my $row = $comment_sth->fetchrow_hashref()) {
                if(defined($row->{comment}) && $row->{comment} ne '') {
                        # comments are shaded to stand out
                        print "\\begin{shaded}";
                        my      $comment_date =
DateTime::Format::MySQL->parse_timestamp($row->{cdate});
                        my      $comment_username = $row->{cuser};
                        my      $comment = $row->{comment};

                        # filter some stuff out so LaTeX behaves
                        $comment =~ s/<br \/>//g;
                        $comment =~ s/\\//g;
                        $comment=~ s/\#/\\#/g;
                        $comment =~ s/\&/\\&/g;
                        $comment =~ tr/\n//d;
                        $comment =~ s/\_/\\_/g;

                        # filters out HTML
                        $comment = $hr->process($comment);

                        # gets the comment author's first name
(functionize this!)
                        my $fname_query = 'select fname from users
where username = ?';
                        my @row2 =
$dbh->selectrow_array($fname_query,undef,$comment_username);
                        my $comment_fname = $row2[0];

                        print "Date: " . $comment_date->strftime("%a,
%d %b %Y at %l:%M %p") . " \\\\ ";
                        print "By: $comment_fname \\\\";
                        print "$comment \\\\ ";
                        print "\\end{shaded} ";
                }
        }print '\\vspace{.1em} \\\\ ';print "\\end{minipage}";print "
\\hrule"; # seperates the logbook entries from each other
} # end of the main loop
print '\end{document}';
# clean up
$sth->finish();
$dbh->disconnect();