On Jan 28, 6:11 pm, [EMAIL PROTECTED] (Chas. Owens) wrote:
> 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?
Thanks for your replay and instructions, as well as the tips regarding
the comments.
Sorry, I missed the "^" for the regexp ^A+
I applied your method but the query does not return any record from
the table.
Also when I try to match only one field using like:
my $arg = shift;
my $sth = $dbh->prepare (" SELECT * FROM $tableName firstname like
'$arg' ");
$sth->execute();
while(my $ref = $sth->fetchrow_hashref()) {
print "First Name: $ref->{'firstname'}\n";
print "Last Name: $ref->{'lastname'}\n";
}
if ($sth->rows == 0){
print "\n";
print "No record matched in the table \"$tableName\".\n";
print "\n";
}
query does not return any record, even I have some firstname staring
with "A", Albert, Aida in the table..
Thanks again for your help
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
