(OT) Setting up a MySQL REGEXP in Perl

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

Fellow Monasterians,

This my be slightly OT for a PM SoPW question, but ... In my HTML I allow the user to click groups of 3 consecutive letters to narrow what will be displayed in a list.

HTML:
<a href="../cgi-bin/listusers.pl?alpha=ABC">ABC</a>
<a href="../cgi-bin/listusers.pl?alpha=DEF">DEF</a>
etc....
[download]

My Perl attempts to set up the appropriate REGEXP in a MySQL SELECT statement to return only those lastnames beginning with those letters:

my $alpha = $query->param('alpha');

$alpha =~ /(\w)(\w)(\w)/;
$stmt = "SELECT * FROM teamnew ORDER BY lastname WHERE lastname REGEXP
+ '^$1|^$2|^$3'";
[download]

But I keep getting an error that there is a syntax error in my SELECT statement. A print Dumper for $stmt shows:

"SELECT * FROM teamnew ORDER BY lastname WHERE lastname REGEXP '^A|^B|
+^C'"
[download]

which appears fine. What am I missing? TIA.

—Brad
"The important work of moving the world forward does not wait to be done by perfect men." George Eliot

20050204 Edit by castaway: Changed title from 'Setting up a MySQL REGEXP in Perl'

Comment on (OT) Setting up a MySQL REGEXP in Perl Select or Download Code

Replies are listed 'Best First'.
Re: (OT) Setting up a MySQL REGEXP in Perl by edoc (Chaplain) on Jan 31, 2005 at 14:44 UTC
you need to change the ordering of the statement I believe.. `$stmt = "SELECT * FROM teamnew WHERE lastname REGEXP '^$1\|^$2\|^$3' ORD +ER BY lastname";` [download] cheers, J	[reply] [d/l]
Re^2: (OT) (OT) Setting up a MySQL REGEXP in Perl by bradcathey (Prior) on Jan 31, 2005 at 17:15 UTC
edoc, thanks, that was it. I didn't think the order mattered, but by moving "ORDERED BY lastname" to the end of the statement, all was fine. —Brad "The important work of moving the world forward does not wait to be done by perfect men." George Eliot	[reply]
Re: (OT) Setting up a MySQL REGEXP in Perl by dragonchild (Archbishop) on Jan 31, 2005 at 14:26 UTC
Does that line run from the MySQL command line? If it works there, then there's a DBD::mysql problem. If it doesn't work there, then you have a problem in your SQL. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]