#!perl
use strict; 
use warnings; 
use DBI; 
use Data::Dump 'pp';

my $dbh = get_dbh();

$dbh->do('DROP TABLE IF EXISTS trees');
$dbh->do("
CREATE TABLE trees (
  id int(11) NOT NULL,
  uses TEXT ,
  flowering TEXT,
  ripe TEXT ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

while (<DATA>){
  chomp;
  $dbh->do('INSERT INTO trees VALUES(?,?,?,?)',undef,split ';');
}

my $sql = 'SELECT * FROM trees
           WHERE (uses      REGEXP(?))
           AND   (flowering REGEXP(?))
           AND   (ripe      REGEXP(?))';
           
my $ar = $dbh->selectall_arrayref($sql,undef,'cider|juice|jelly','spri
+ng','autumn');         
for (@$ar){
  print join ",",@$_,"\n";
}           

sub get_dbh{
  my $database = "test"; my $user = "root"; my $pw = "";
  my $dsn = "dbi:mysql:$database:localhost:3306"; 
  my $dbh = DBI->connect($dsn, $user, $pw, { RaiseError=>1, AutoCommit
+=>1 } );
  return $dbh;
} 

__DATA__
1;cider perry;spring winter;spring autumn winter
2;perry juice perry;winter autumn; autumn winter
3;jelly;spring autumn;spring autumn
4;cider cooking;spring autumn winter;spring winter
[download]