sub search_biography
{
	my $search_text=shift;
	my @result;
	my $table;
	my $sth=$dbh->prepare("select distinct
hm_index,first_name,last_name,
category from Biography 
where first_name like '%$search_text%' 
or last_name like '%$search_text%' 
or bio_text like '%$search_text%'
or occupation like '%$search_text%'
or description like '%$search_text%'"
);
	$sth->execute;
	while(my @row=$sth->fetchrow)
	{
		push @results , "blah - was long HTML string"
	}
	return (\@result);
}


##</code><code>##


use DBIx::FullTextSearch;
use DBI;
use strict;

# the data source, that is the tables to be indexed
my $dbh = DBI->connect('dbi:mysql:database','user','password');

my $table_info = { 'DBIx_biography' => {
                                    table_name  => 'Biography',
                                    column_name => 'bio_text',
                 },
                   'DBIx_glossary' => {
                                    table_name  => 'Glossary',
                                    column_name => 'glossary_defination',
                   },
                   'DBIx_timeline' => {
                                    table_name  => 'Timeline',
                                    column_name => 'event_description',
                   },
                   'DBIx_quotes'   => {
                                    table_name  => 'Quotes',
                                    column_name => 'quote',
                   },
};


my $table_check = $dbh->prepare('show tables');
$table_check->execute;

my $tables;
while ( my ($table) = $table_check->fetchrow() ) {
    $tables{$table} = 1;
}

foreach my $front_end_table ( keys %{$table_info} ) {

    if ($tables{$front_end_table}) {

        $fts = DBIx::FullTextSearch->open($dbh, $front_end_table);
        $fts->empty;

    } else {
        
        $fts = DBIx::FullTextSearch->create($dbh, $front_end_table,
                                           
            frontend => 'table',        # type of frontend
            # table to be indexed
            table_name =>                $table_info->{$front_end_table}{'table_name'},        
            # column that has the info to be indexed
            column_name => $table_info->{$front_end_table}{'column_name'},  
            backend => 'column',        # type of storage for the backend

	    # you really should set these up, but I won't get
            # into that in this writeup

            #stoplist => 'sl_en',        # from example
            #stemmer => 'en-us'          # from example

            );
    }

    my $ids = $dbh->prepare(qq!SELECT hm_index
                            FROM $table_info->{$front_end_table}{'table_name'}!);
        
    $ids->execute() or die "$DBI::errstr problem";

    while ( my ($id) = $ids->fetchrow ) {
        $fts->index_document($id);
    }

}

##</code><code>##


# full_text_search.pl
use strict;

use DBIx::FullTextSearch;
use DBI;

# the data source, that is the tables to be indexed
my $dbh = DBI->connect('dbi:mysql:database','user','password');

my $front_end_table = shift @ARGV;

my $fts = DBIx::FullTextSearch->open($dbh, $front_end_table);
    
my $table = shift @ARGV;

# this is a mapping of the SQL statements for each table, I was able
# to get these directly from some existing code and then just
# replaced my where conditional.  This minimized the amount of
# refactoring and time required to swap things out.

my $table_column = {
	Biography => 'distinct hm_index,first_name,last_name,category',

	Quotes    => qq!distinct hm_index,quote_stamp,quote,
date_format(birth_year,'%Y'),
date_format(death_year,'%Y'),
name,bio_index,media_index,category!,

	Timeline  => qq!hm_index,event_title,event_brief,event_description,
timeline_stamp,date_format(event_date, '%Y')!,

	Glossary  => qq!hm_index,glossary_title,glossary_defination!,
};

my @docs = $fts->contains(@ARGV);
my $doc = join(',',@docs);
if ($doc) {
my $info = $dbh->prepare(qq!SELECT $table_column->{$table}
						 FROM $table WHERE hm_index IN($doc)! );
$info->execute;
while ( my @row = $info->fetchrow ) {
	# this is just for testing so it doesn't need
	# to be pretty :)
	print "'$row[1]' '$row[2]'\n";
}

} else {
	print "no matches for '", join(' ',@ARGV) , "'";
}


##</code><code>##

full_text_search.pl DBIx_biography Biography trs80

##</code><code>##

DBIx_biography = front end table

Biography      = table that contains our primary key, 
		 this is the table that we will get our final mathes from

trs80          = what to look for

##</code><code>##

full_text_search.pl DBIx_biography Biography trs80 jeffa Ovid