On Jan 28, 2008 4:12 PM, <[EMAIL PROTECTED]> wrote:
> I have a table "customer_table" with the following fields:
>
> Id int,
> firstname varchar(64),
> lastname varchar(64),
> emailaddress varchar(64) not null primary key
> city varchar (32),
>
> Can some one help me and show me how to print only records that
> matches a given regexp using, for example if I run:
>
> #> getRecord.pl A+
>
> should return all record from the database if the first name, last
> name, or email address starts with a capital A
>
> OR:
>
> #> getRecord.pl
>
> should return all records from the table, which I have it this way and
> it works just fine:
snip
This really isn't a job for a regex. It is a job for a where clause.
Also, if the user where to pass A+ it would match any record whose
fields had one or more contiguous "A"s, not records that have fields
that start with "A". That match would be ^A. This further points to
the fact that you want the SQL operator LIKE (which does behave the
way you expect it to, but uses % instead of +).
snip
> my $sth = $dbh->prepare("SELECT * FROM $tableName");
snip
my $arg = shift;
my $where_clause = "";
if ($arg) {
#handle meta characters, match uses % for one or more characters,
* for 0 or more, _ for any character
$arg =~ s/\+/%/g;
$arg =~ s/\?/_/g;
#try to handle SQL injection attacks, users should not be able to
break out of the string
#FIXME: this may not be a complete solution, it also probably
breaks character classes like [']
$arg =~ s/'/\\'/;
$where_clause = "where firstname like $arg or lastname like $arg
or email like $arg";
}
my $sth = $dbh->prepare("SELECT * from $tableName $where_clause");
By the way, comments are there to explain why you are doing something,
not to tell us what you are doing. Comments like "#print the data"
are useless. I can see that you are printing the data, why are you
printing it now?, what are you trying to achieve by printing it?
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
