Performing queries with DBI and DBD::MySQL

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks, I am using the perl DBI module to query a mysql database. I want to be able to select any results which contain a keyword (e.g. not necessarily exact hits) and wondered how to do this?

This is the sort of thing I have:

my $sth = $dbh->prepare(qq{SELECT * FROM Prediction WHERE Prediction_i
+d = '$keyword' OR Target_id = '$keyword' Use = '$keyword'  });       
+                                                                     
+                                                                     
+                                   
$sth->execute()
        or die "Couldn't execute statement: " . $sth->errstr;         
+                                                                     
+                                                                     
+                                 
my @new_array;                                                        
+                                                                     
+                                                       
while (my $ary_ref = $sth->fetchrow_arrayref() )        {
        my $delim = "\n";
        for (my $i=0; $i<@{$ary_ref}; $i++)     {
        push @new_array, "$ary_ref->[$i]\n";
        }
        print "\n";
}                                                                     
+                                                                     
+                                          
print @new_array;
[download]

How can I alter this so that it doesn't just match exact hits?

Thanks

Retitled by davido.

Comment on Performing queries with DBI and DBD::MySQL Download Code

Replies are listed 'Best First'.

Re: Performing queries with DBI and DBD::MySQL
by Mutant (Priest) on Jan 10, 2005 at 16:04 UTC

SELECT * FROM Prediction WHERE Prediction_id like '%keyword%';
[download]

By the way, you should definitely read up on placeholders before going any further.

[reply]
[d/l]

Re: Performing queries with DBI and DBD::MySQL
by dbwiz (Curate) on Jan 10, 2005 at 16:18 UTC

In addition to what the others said about using "LIKE", I would like to add two things:

You may also use a FULLTEXT search. A bit more complex, but much more effective.
You are fetching single records and pushing each column of them into an array. You'll get a flat array of all your dataset. Are you sure this is what you want?
For example, if your data is the following:

Field1 Field2

aaa1 bbb1

aaa2 bbb2

aaa3 bbb3

After your loop, it will produce an array like
```
@array = (
    "aaa1\n",
    "bbb1\n",
    "aaa2\n",
    "bbb2\n",
    "aaa3\n",
    "bbb3\n" 
);
[download]
```
Perhaps you should consider fetchall_arrayref instead. For more guidance, you may refer to DBI recipes.

[reply]
[d/l]

Re: Performing queries with DBI and DBD::MySQL
by TrekNoid (Pilgrim) on Jan 10, 2005 at 16:04 UTC

TrekNoid

[reply]

Re: Performing queries with DBI and DBD::MySQL
by erix (Prior) on Jan 10, 2005 at 16:34 UTC

Or, if you want regex searching, look at mysql's "regexp" (or "regexp binary" for case insensitive):

select 
   'erix' regexp '[abc]',  --  returns 0
   'erix' regexp '[a-z]'   --  returns 1 
;
[download]

[reply]
[d/l]

Re: Performing queries with DBI and DBD::MySQL
by r34d0nl1 (Pilgrim) on Jan 10, 2005 at 16:14 UTC

http://dev.mysql.com/doc/mysql/en/index.html

http://dbi.perl.org

Mutant

[reply]