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

Hello

I have a mysql database and there's a NAME field, all
records are saved the following way:
Norton, Peter
Monks, Perl

everything is: Last name, First name I want that when the
user searches for: First Name Last Name it makes the
search for both (First Last/Last First). I tried this: 
$name=param("name");
$name=~s/ /%/g;

$sql="select * from table where name like "%$name%";

[download]
But, its not working...

Replies are listed 'Best First'.
Re: MySQL Select problem
by kabel (Chaplain) on Oct 09, 2002 at 15:02 UTC
    you have a quoting problem. consider this examples:
    C:\Perl Testskripte\580>perl
use strict;

my $name = "kabel";
my $sql_statement = "select * from users where name like '%$name%'";
print "executing [$sql_statement]\n";
^D
executing [select * from users where name like '%kabel%']

C:\Perl Testskripte\580>

    [download]
    or use the qq operator:
    C:\Perl Testskripte\580>perl
use strict;

my $name = "kabel";
my $sql_statement = qq~select * from users where name like '%$name%'~;
print "executing [$sql_statement]\n";
^D
executing [select * from users where name like '%kabel%']

C:\Perl Testskripte\580>

    [download]
    or just escape the " characters: \"
[reply]
[d/l]
[select]
      It's not working anyway.
[reply]
        the only code piece you posted had this quoting error. you should post more code because the error definitively is in another code snippet. post more code and a monk will know the answer. :)
[reply]
Re: MySQL Select problem
by Mr. Muskrat (Canon) on Oct 09, 2002 at 16:35 UTC

    The problem doesn't appear to be in your select statement at all (once you fix the quote issue). The problem appears to be your logic concerning the name parameter.

    Quick guess at a solution (not tested at all): 

    my @names = split(/\s/, param("name"));
my $name = join("%", @names) . " OR " . join("%", reverse @names);
my $sql = "select * from table where name like $name";

    [download]
    This should at least get you thinking in a different direction. :)

[reply]
[d/l]
Re: MySQL Select problem
by rdfield (Priest) on Oct 10, 2002 at 09:04 UTC
    Are you using 'strict' and 'warnings'? Are you getting some funny errors about unexpected names and values and variables not being defined? Have you counted the number of quotes in your SQL statement definition?

    Hint: it should be an even number :)

    rdfield

[reply]
Re: MySQL Select problem
by screamingeagle (Curate) on Oct 10, 2002 at 19:17 UTC
    you might want to check if the problem is being caused by case-sensitivity issues. try converting your search parameter to either all-uppercase or lowercase and converting your sql to use the LCASE or UCASE functions . e.g. : 
    select * from table where ucase(name) like '%name%'

    [download]
    hth
[reply]
[d/l]

Back to Seekers of Perl Wisdom