# Listing 2: search.pl

#!/usr/bin/perl -wT

# search.pl, which allows people to search through postings

use strict;
use diagnostics;
use CGI;
use CGI::Carp qw(fatalsToBrowser);
use DBI;

# This call to "use lib" should reflect the location of
# ATFConstants.pm, which defines $database, $server, $port, $username,
# and $password
use lib qw(/home/reuven/www/cgi-bin);
use ATFConstants;

my $message_id = "";
my $thread_id = "";
my $subject = "";
my $author = "";
my $thread_name = "";

# Remove buffering
$| = 1;

# ------------------------------------------------------------
# Create an instance of CGI
my $query = new CGI;

my $term = $query->param("term");
my $regexp = $query->param("regexp");

# Send a MIME header
print $query->header("text/html");

print $query->start_html(-title => "Search results",
                         -bgcolor => "#FFFFFF");

print "<H1>Search results</H1>\n";

# ------------------------------------------------------------
# Connect to the database
my $dbh = 
    DBI->connect("DBI:mysql:$database:$server:$port",$username,$password);
die "DBI error from connect: \"$DBI::errstr\"" unless $dbh;

# ------------------------------------------------------------
# Get information about the thread
my $sql = "SELECT M.id, M.thread, M.subject, M.author, T.subject ";
$sql .= "FROM ATFMessages M, ATFThreads T ";

# Should we treat this as a regular expression?
if ($regexp eq "yes")
{
    $sql .= "WHERE M.text REGEXP \"$term\" ";
}
# Or should we treat this as a literal phrase?
else
{
    # Remove SQL regexp characters
    $term =~ s|%|\\\%|g;
    $term =~ s|_|\\\_|g;

    $sql .= "WHERE M.text LIKE \"%$term%\" ";
}
$sql .= "AND M.thread = T.id ";
$sql .= "ORDER BY M.date desc";

# Send the query
my $sth = $dbh->prepare($sql);
die "DBI error with prepare: \"$sth->errstr\"" unless $sth;

# Execute the query
$sth->execute;
die "DBI error with execute: \"$sth->errstr\"" unless $sth;

# We should only have received a single row.  Print it out.
if ($sth->rows)
{
    print "<ul>\n";

    # Iterate through thread IDs and names
    while (my @row = $sth->fetchrow)
    {
        ($message_id, $thread_id, $subject, $author, $thread_name) = @row;

        print "<li><a href=\"/cgi-bin/view-thread.pl?";
        print "$thread_id#$message_id\">$subject</a>, ";
        print "by $author in ";
        print "<a href=\"/cgi-bin/view-thread.pl?$thread_id\">";
        print "$thread_name</a>\n";
    }

    print "</ul>\n";
}
else
{
    print "<P>No matches.</P>\n";
}

# ------------------------------------------------------------
# Menu bar
print "<P>\n";

# Send the user to the posting form
print "<a href=\"/cgi-bin/post-comment-form.pl\">[Post a message]";
print "</a>\n";

# Send the user to the thread list
print "<a href=\"/cgi-bin/list-threads.pl\">[View all threads]</a>\n";

# Send the user to the search
print "<a href=\"/atf/search-form.shtml\">[Search]</a>\n";

# Allow the user to create a new thread
print "<a href=\"/cgi-bin/add-thread-form.pl\">[Add a new thread]</a>\n";

# Give a plug for the ATF home page
print "<a href=\"/atf/\">[ATF home]</a>\n";

print "</P>\n";