Regex Searching help 4

[perlone]

Hello,

I'm really new to regular expressions and I was wondering if anyone could give me a hand. What I have is some data on mySQL which contains `title`, and `keywords` and I want the user to search through the title and keywords. Here's an example:

Table: games
ID | Title | Keywords
1 | Blah 1 | rpg,quest,3D
2 | Blah 2 | 3D,quest,real-time

I want to search through the title and keywords and this is what I have:

$sql = qq[select title,keywords from index_tble];

my $sth = $dbh->prepare("$sql"
$sth->execute or print(&quot;Cannot execute statement!<br>&quot;.$sth->errstr);
while(@row = $sth->fetchrow) {
if ($row[0] =~/$q/ || $row[1] =~/$q/) {
print &quot;$row[0]&quot;
}
###Blah....
}

But If I search for like: some quest games, it doesn't search for the 'quest' that is already on the keyword list. Any idea or tips? Thanks!

 

[raklet]

From your post, I am guessing that $q = &quot;some quest games&quot;;

The reason it is not returning a match from your db is that it is literally looking for &quot;some quest games&quot;.

To make it look for individual elements, you would have to split the user input up and run a regex for each item.

$q = &quot;some quest games&quot;;
@array = split(/ /,$q);
foreach $word (@array) {
my $sth = $dbh->prepare(&quot;$sql&quot;
$sth->execute or print(&quot;Cannot execute statement!<br>&quot;.$sth->errstr);
while(@row = $sth->fetchrow) {
if ($row[0] =~/$word/ || $row[1] =~/$word/) {
print &quot;$row[0]&quot;
}
}

Or something like that.

 

[perlone]

Thank You! It works a lot better

 

[MikeLacey]

That looks to me as if it's running the query for each word in the query string -- so if there're 8 words in the query string that code will go through the whole table 8 times.

What about something like this?
[tt]
$q = &quot;some quest games&quot;;
@array = split(/ /,$q);
my $sth = $dbh->prepare(&quot;$sql&quot;
$sth->execute or print(&quot;Cannot execute statement!<br>&quot;.$sth->errstr);
while(@row = $sth->fetchrow) {
foreach $word (@array) {
if ($row[0] =~/$word/ || $row[1] =~/$word/) {
print &quot;$row[0]&quot;
}
}
}
[/tt]


Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

It's like this; even samurai have teddy bears, and even teddy bears get drunk.

 

[raklet]

Thanks Mike,

I'm glad you could present the proper syntax for executing the query.

I haven't written any db calls in perl so I wasn't sure of how/where to put the query stuff. I was just trying to give the user an idea of what to do.

 

[MikeLacey]

Hmmm <wry smile> the reason I spotted it was that I've done the same thing myself, and once on a table with 256,000 rows....

Mike

Want to get great answers to your Tek-Tips questions? Have a look at faq219-2884

It's like this; even samurai have teddy bears, and even teddy bears get drunk.

 

[perlone]

Thank You Guys. You guys are eally helpful

 

[Coderifous]

Expanding into the realm of efficiency, a good rule of thumb is to pass as much processing off to the DB as possible. In this scenario, you would rather have the database do the matches and return only the rows that you care about, as opposed to returning all values and then using regexes to match or discard certain rows.

Here's my suggested modification (using Mike's code):

my $q = "some quest games";

my $sql = 'select title,keywords from index_tble';

@array = split(/\s+/,$q);

if(@array){
    $sql .= " where ";
    my @a;
    push @a, 'keywords like "%?%"' for (0 .. $#array);
    $sql .= join(' or ', @a);
}

$sql .= ' order by title desc';  # give it some order
 
my $sth = $dbh->prepare($sql);
$sth->execute(@array) or print(&quot;Cannot execute statement!<br>&quot;.$sth->errstr);

while(@row = $sth->fetchrow) {
        print &quot;$row[0] <br>\n&quot;;
}
[code]
This should run quicker, it will be especially noticeable if you have large sets of data returned from MySQL.

Hope this helps,

--jim

 

[perlone]

Hello Coderifous,

Thank You so much for your code, but when I tried your code, I got the following error:

Cannot execute statement!
called with 1 bind variables when 0 are needed

 

[Coderifous]

whoopsy, code is untested.

probably this:

push @a, 'keywords like "%?%"' for (0 .. $#array);

should become this:

push @a, ' keywords like ? ' for (0 .. $#array );
$_ = "%${_}%" for @array;

and then it should work... I think. But really, we should just refactor the script. So the whole snippet should look like this:

my $q = "some quest games";

my $sql = 'select title,keywords from index_tble';

@array = map {'%' . $_ . '%' } split(/\s+/,$q);

if(@array){
    $sql .= " where ";
    my @a;
    push @a, 'keywords like ?' for (0 .. $#array);
    $sql .= join(' or ', @a);
}

$sql .= ' order by title desc';  # give it some order
 
my $sth = $dbh->prepare($sql);
$sth->execute(@array) or print(&quot;Cannot execute statement!<br>&quot;.$sth->errstr);

while(my @row = $sth->fetchrow) {
        print &quot;$row[0] <br>\n&quot;;
}

OK, I apologize for the untestedness of the code, but hopefully this will do what we intend.

--jim

 

[perlone]

Hello Coderifous,

Thank you so much for your kind code. It works very well and it loads faster as well. Thanks again

 

[perlone]

Hello again,

If I wanted to search for the game `title` as well, can you show how to do it? So far I did this but don't work:

my $sql = 'select title,keywords from index_tble';

@array = map {'%' . $_ . '%' } split(/\s+/,$q);

if (@array){
$sql .= &quot; where &quot;;
my @a;
push @a, 'keywords OR title like ?' for (0 .. $#array);
$sql .= join(' or ', @a);
}

$sql .= ' order by title desc'; # give it some order

Thank you...

 

[Coderifous]

my $sql = 'select title,keywords from index_tble';

@array = map {'%' . $_ . '%' } split(/\s+/,$q);

if (@array){
$sql .= &quot; where &quot;;
my @a;
push @a, 'keywords like ? OR title like ?' for (0 .. $#array);
$sql .= join(' or ', @a);
push @bind_values, ($_, $_) for @array;
}

$sql .= ' order by title desc'; # give it some order

# ... and then later on, use the bind_values array in the
# execute statement.
# since we have two place-holders for each search param
# (question marks), we need to double up the
# array elements so everything is in sync.

$sth->execute(@bind_values) or die &quot;...&quot;;


Hope this helps.

--jim

 

[perlone]

Thanks again, it works like a charm

 

[Coderifous]

Great!

--jim