PERL and MySQL

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

Hi everyone, I have a script that searches a MySQL db and I am having a problem. First, the problem.... If I search the db through phpMyAdmin, all goes well. If I run rhis script, I may or may not get the results I am after. For example, I have several entries for furniture repair. If I search for furniture repair, I get no results. If I search for furniture, I get no results. If I search for repair, I also get auto repair, TV repair, etc... I am thinking there is something wrong with the script because as I said earlier, I can search for virtually anything using phpMyAdmin and get the proper results back. So, can anyone suggest any improvements to this script or point me to a resource? Thanks in advance, Jim

$query = sprintf (
               "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`Ci
+ty`,`Contact`.`Phone`,`Keys`.`Keywords`
                FROM
                (
                `Cats`
                    LEFT JOIN
                        `Address`
                            USING (`Name`)
                    LEFT JOIN
                         `Contact`      
                            USING (`Name`) 
                    LEFT JOIN
                         `Keys`      
                            USING (`Name`)         
                )
                WHERE
                `Keys`.`Keywords` LIKE '%$search%' 
                ORDER BY name asc LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page + 1);     
}
[download]

Comment on PERL and MySQL Download Code

Replies are listed 'Best First'.
Re: PERL and MySQL by gamache (Friar) on Oct 19, 2007 at 14:02 UTC
I think your culprit is here: WHERE `Keys`.`Keywords` LIKE '%$search%' [download] `sprintf` is trying to interpret the % characters as format string tokens. If the first character of $search is a valid field specifier, you get weird results. You want literal % characters in the finished expression, so use '%%' instead: WHERE `Keys`.`Keywords` LIKE '%%$search%%' [download] ...and I bet things might go your way. Proof of concept: `bash$ perl -e '$a=22; printf "%$a%\n";' % bash$ perl -e '$a=22; printf "%%$a%%\n";' %22%` [download]	[reply] [d/l] [select]
Re: PERL and MySQL by meraxes (Friar) on Oct 19, 2007 at 15:14 UTC
If I may suggest as well, doing this using sprintf is not ideal. Using placeholders is always a better idea: my $sql = q{ SELECT `Cats`.`Name`, `Address`.`Address`, `Address`.`City`, `Contact`.`Phone`, `Keys`.`Keywords` FROM ( `Cats` LEFT JOIN `Address` USING (`Name`) LEFT JOIN `Contact` USING (`Name`) LEFT JOIN `Keys` USING (`Name`) ) WHERE `Keys`.`Keywords` LIKE ? ORDER BY `Cats`.`Name` asc LIMIT ?,? }; # get the statement handle, assuming a valid DBI object in $dbh my $sth = $dbh->prepare( $sql ); $sth->execute( '%'.$search.'%', $start - 1, $per_page + 1 ); [download] You also had the "order by" clause stating name. I would have thought that'd be an error since you're not specifying the table in a join. I've changed it to `Cats`.`Name`. update: I'm an idiot. Fixed `execute` syntax. -- meraxes	[reply] [d/l] [select]
Re^2: PERL and MySQL by JimJx (Beadle) on Oct 20, 2007 at 07:13 UTC
Thanks for all of the suggestions everyone! The '%%' was the culprit... Also, since I am working with a premade script of sorts here, I am just trying to get it to work, but I do plan on using placeholders when I get the time to do a rewrite. IMO the whole script needs it.... Once again, thanks everyone! Jim	[reply]
Re: PERL and MySQL by eric256 (Parson) on Oct 19, 2007 at 13:59 UTC
Double or triple check what you are putting in $search, $start and $per_page. If they arn't perfect all three can mess up your results. My recommendation would be to drop the limit until you get the search working right. Also make sure that once you put your search in the querry, the query prints out looking exactly like the one you run in phpMyAdmin. Even cut and paste the finished query over and see if it runs there. ___________ Eric Hodges	[reply]