Gossamer Forum: General: Perl Programming: Pattern Matching

Sep 26, 2001, 8:03 AM

Wil

Veteran / Moderator (4108 posts)

Sep 26, 2001, 8:03 AM

Post #1 of 11

Shortcut

Pattern Matching

Can anyone please explain to me what is wrong with the following? I'm really running out of hair to pull out here!

Code:
#     $sqlstat_isrecordthere = "SELECT id FROM geog"; 
#     $sth0 = $dbh->prepare($sqlstat_isrecordthere); 
#     $sth0->execute; 
#     $listofids = $sth0->fetchall_arrayref({}); 
#     $sth0->finish; 

#     foreach $isidthere (@$listofids) { 

#	    if ($isidthere->{'id'} != "$A::id") { 
#		&error_html("No such record."); 
#		&exit(0); 
#	    } 
#     }

What this snippet of code should do:

Dip into the database.
Select colum id from all records.
See if my $A::id that's passed to the script matches any id found in the database.
If it doesn't, transfer to error subroutine and exit.
If it does find a match, continue onto next sub.

For some reason, the sub keeps telling me that it can not find a match every time. I just can't think of what I'm doing wrong. Anyone with any ideas?

Thanks!!

Wil

Sep 26, 2001, 8:24 AM

sh2sg

Novice (40 posts)

Sep 26, 2001, 8:24 AM

Post #2 of 11

Shortcut

Re: [Wil] Pattern Matching In reply to

Code:
...... 

my $found = 0; 
foreach $isidthere (@$listofids) { 
  if ($isidthere->{'id'} == "$A::id") { 
    $found = 1; 
    last; 
  } 
} 

if (!$found) { 
  &error_html("No such record."); 
  &exit(0); 
} 

......

Sep 26, 2001, 8:31 AM

Wil

Veteran / Moderator (4108 posts)

Sep 26, 2001, 8:31 AM

Post #3 of 11

Shortcut

Re: [sh2sg] Pattern Matching In reply to

Thank you!

Wil

Sep 26, 2001, 8:44 AM

sh2sg

Novice (40 posts)

Sep 26, 2001, 8:44 AM

Post #4 of 11

Shortcut

Re: [Wil] Pattern Matching In reply to

one query is enough.

Code:
... 
$sqlstat_isrecordthere = "SELECT id FROM geog where id = '$A::id'"; 
$sth0 = $dbh->prepare($sqlstat_isrecordthere); 
$sth0->execute; 
$found = $sth0->fetchrow_array(); 
$sth0->finish; 

if (!$found) { 
  &error_html("No such record."); 
  &exit(0); 
} 

...... 

Sep 26, 2001, 8:51 AM

Wil

Veteran / Moderator (4108 posts)

Sep 26, 2001, 8:51 AM

Post #5 of 11

Shortcut

Re: [sh2sg] Pattern Matching In reply to

That doesn't work. Comes up with No records found for every permutation.

Cheers

Sep 26, 2001, 9:12 AM

sh2sg

Novice (40 posts)

Sep 26, 2001, 9:12 AM

Post #6 of 11

Shortcut

Re: [Wil] Pattern Matching In reply to

my $myid = $A::id;
$sqlstat_isrecordthere = "SELECT id FROM geog where id = '$myid' ";

if id is int type, remove the quote from query.

Sep 26, 2001, 9:13 AM

Wil

Veteran / Moderator (4108 posts)

Sep 26, 2001, 9:13 AM

Post #7 of 11

Shortcut

Re: [sh2sg] Pattern Matching In reply to

But I want to change:

SELECT id FROM geog where id = '$myid' ";

to

SELECT * FROM geog where id = '$myid' ";

so I can get more information later. I could show more fields, that is, not only the ID field.

That was the whole purpose of narrowing this down into one query.

Thanks

Sep 26, 2001, 9:17 AM

Wil

Veteran / Moderator (4108 posts)

Sep 26, 2001, 9:17 AM

Post #8 of 11

Shortcut

Re: [sh2sg] Pattern Matching In reply to

Done your changes, and still doesn't work.

Cheers

Sep 26, 2001, 9:30 AM

Matt G

User (155 posts)

Sep 26, 2001, 9:30 AM

Post #9 of 11

Shortcut

Re: [Wil] Pattern Matching In reply to

Hi,

This may not be the problem, but you might want to give it a try:

.....
my $sth0 = $dbh->prepare_cached('SELECT * FROM geog WHERE id = ?')
or die "Couldn't prepare statement: " . $dbh->errstr;

$sth0->execute($myid)
or die "Couldn't execute statement: " . $sth0->errstr;

my (@recordfields) = $sth0->fetchrow_array();
.....

--
Matt G

Sep 26, 2001, 10:03 AM

Alex

Administrator (9387 posts)

Sep 26, 2001, 10:03 AM

Post #10 of 11

Shortcut

Re: [Wil] Pattern Matching In reply to

Hi,

Again, don't do the work in perl, if you can do it in SQL.

my $query = "SELECT id FROM geog WHERE id = ?";
my $sth = $dbh->prepare($query);
$sth->execute($A::id);

my ($match) = $sth->fetchrow_array;
if ($match) {
... # got a match
}
else {
... # no match
}

Try to avoid:

->fethcall_arrayref({});

it won't work except on the newer DBI.pms.

Cheers,

Alex
--
Gossamer Threads Inc.

Sep 27, 2001, 1:40 AM

Wil

Veteran / Moderator (4108 posts)

Sep 27, 2001, 1:40 AM

Post #11 of 11

Shortcut

Re: [Alex] Pattern Matching In reply to

Hi Alex

That doesn't seem to want to work for some reason. Here's the code I have now (which does not work):

Code:
    $| = 1; 

    print $query->header; 

    &get_date; 

    &connect_to_db; 

     my $sqlquery = "SELECT * FROM geog WHERE id = $A::id"; 
     my $sth = $dbh->prepare($sqlquery); 
     $sth->execute; 

     my ($match) = $sth->fetchrow_array; 

     if ($match) { 

    while ($pointer 	 = $sth->fetchrow_hashref)	{ 
	  $res_id 	 = $pointer->{'id'}; 
          $res_type 	 = $pointer->{'res_type'}; 
          $res_physical  = $pointer->{'res_physical'}; 

    . . . . . .  
    } # end while 

    } # end if $match 

    else { 
  	&error_html("No such record."); 
  	&exit(0); 
     }

Now here's a summary of what I'm trying to do again:

Form with an input box labeled id.
Send the form off to the script above.
Search the database, if we find an ID match, return information on record.
If database does not have an ID match, return error sub.

Any more ideas anyone?

Thanks!

Wil