Nevermind, I was thinking too hard... this was actually quite trivial.
Here's what I added to the code:
my $row;my $date = '';
# set the `logbook` variableswhile ($row = $sth->fetchrow_hashref())
{my $id = $row->{id};my $date_added =
DateTime::Format::MySQL->parse_timestamp($row->{date_added});my $day
= DateTime::Format::MySQL->parse_timestamp($row->{date_added});my
$username = $row->{username};my $entry = $row->{entry};
my $entry_date = $date_added->strftime("%d");if ($date ne $entry_date) {
print "\\section*{" . $date_added->strftime("%a, %d %b %Y ") . "} ";
$date = $date_added->strftime("%d");}
On Sun, Sep 15, 2013 at 1:16 AM, J M <[email protected]> wrote:
>
>
> <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();
>
>
