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

I'm trying to run a perl script that calls MySQL to SELECT files that match a given word that may be embedded within a phrase. For instance, I want to select every row the contains 'San Diego' in the REGION column ... a basic pattern match that I initially thought would be very straightforward based on the MySQL documentation using %San Diego%, but it selects nothing. When i just select 'San Diego', it will pull out all the files that have only San Diego, but misses any file that has anything else in that column.

any expert tips?


$dsn = "DBI:mysql:database;db.database.com";
           
$dbh = DBI->connect($dsn,'user','pass');

$sth = $dbh->prepare("SELECT * FROM company_contact_info WHERE region 
+= '%San Diego%' ";");

$sth->execute();

[download]

thanks, cdherold

Replies are listed 'Best First'.
Re: MySQL pattern matching
by JayBonci (Curate) on Feb 24, 2002 at 19:28 UTC
    This is way off-topic for this, but I can see two things wrong. First off, you aren't actually returning any records.

    You may want to use this... 
     my $csr = $dbh->sqlSelectMany("*", "company_contact_info", "region='S
+an Diego'");

while(my $row = $csr->fetchrow_hashref())
{
  #row is now a single returned row that matched your query;
}

    [download]
    As well, your SQL regular expression is also wrong. When doing regular expressions in SQL, you want to use LIKE (for mySQL and SQL Server I believe at least) 
    SELECT * FROM company_contact_info WHERE REGION LIKE "%San Diego%"

    [download]
    Without LIKE, it won't interpret the string as a regular expression, and thus will be searching for "\%San Diego\%"

    This begs another question. Why are you storing items in your database as strings. If they are raw address fields, then there's no good way to do it (unless you parse the results as you receive them and store a string independant region key, but that depends on the application in which you are calling this database.) Indexes and numerical keys can be quite helpful and much faster.

    Hope this helps.    --jay
[reply]
[d/l]
[select]
      Jay, That did help. Turns out all i needed was to use LIKE.

      to answer your question ... I'm only storing addresses as strings. I needed to go and find key words in the address and based on this assign the row a 'region' identifier. 'LIKE %keyword%' worked exactly as i needed. for my simplictic projects this seems to work right now. the other stuff you're talking about i've heard and seen mentioned, but haven't really gotten into. still just feeling my way around this area. hopefully i'll grow into more advanced and efficient structures as i use it and learn more. but for now, just trying to get along. thanks.

[reply]
Re: MySQL pattern matching
by gt8073a (Hermit) on Feb 24, 2002 at 19:29 UTC

    $sth = $dbh->prepare("SELECT * FROM company_contact_info WHERE region = '%San Diego%' ";");

    This has almost nothing to do with perl, but you have an error in your SQL statement. instead of WHERE region = '%San Diego%' try WHERE region LIKE '%San Diego%'.

    Will perl for money
    JJ Knitis
    (901) 756-7693
    gt8073a@industrialmusic.com

[reply]
Re: MySQL pattern matching
by webfiend (Vicar) on Feb 24, 2002 at 19:29 UTC

    It's not a Perl question, but a SQL syntax question. Still, it's something I can answer, so I don't want to let it drop =)

    '%' is used in SQL SELECT ... LIKE statements, so you really want to be preparing this statement instead:

    my $sth = $dbh->prepare("SELECT * FROM company_contact_info WHERE region LIKE '%San Diego%'");

    There's other stylistic issues (like whether '%San Diego%' should be declared as a string and bound to the query), but that's extra. To get more info on how MySQL handles LIKE, check the online documentation page.

    "All you need is ignorance and confidence; then success is sure." -- Mark Twain
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom