# 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) , "'"; }