MySQL & Perl: Using a Dynamic SELECT Statement

I'm trying to set up a dynamic SELECT statement, but it's not doing
what I'm expecting. Is it not possible to make a dynamic SELECT
statement, or am I simply doing it wrong?

# FIRST ATTEMPT:
if ($check_username) {
if ($type eq "loose") {
$whereby1 = "WHERE username LIKE CONCAT('%',?,'%')";
}

else {
$whereby1 = "WHERE username=?";
}

my $sth = $dbh->prepare("SELECT id FROM posts " . $whereby1);
$sth->execute($search);

while (my ($this_id) = $sth->fetchrow_arrayref()) {
if (!(exists($topics{$this_id}))) { $topics{$this_id} = 1; }
}
}


This attempt is giving me the complete database, as if $whereby1
doesn't exist. I've checked to make sure that it does, so the SELECT
statement simply isn't allowing the dynamic WHERE operator. Which
really does not work well for me, because I have 3 other IF...ELSE
statements that I would like to use to extend that $whereby1; meaning
that, depending on the options selected, the end value might be:

$whereby1 = "WHERE username LIKE CONCAT('%',?,'%') OR subject LIKE
CONCAT('%',?,'%') OR comment REGEXP ? OR... (and so on)";

But since this isn't working, I thought that I would just remove the
$whereby1 variable altogether, and loop through $sth for every option,
even though this would add a LOT more overhead.


# SECOND ATTEMPT:
if ($check_username) {
if ($type eq "loose") {
my $sth = $dbh->prepare("SELECT id FROM posts WHERE username LIKE
CONCAT('%',?,'%')");
$sth->execute($search);
}

else {
my $sth = $dbh->prepare("SELECT id FROM posts WHERE username=?");
$sth->execute($search);
}

while (my ($this_id) = $sth->fetchrow_arrayref()) {
if (!(exists($topics{$this_id}))) { $topics{$this_id} = 1; }
}
}

This attempt gives me the error "Can't call method "fetchrow_arrayref"
on an undefined value", as if $sth hasn't been defined.


If I make the same statement without the IF...ELSE, it works like I
expected, so the problem is definitely with the dynamics:

my $sth = $dbh->prepare("SELECT id FROM posts WHERE username LIKE
CONCAT('%',?,'%')");
$sth->execute($search);

while (my ($this_id) = $sth->fetchrow_arrayref()) {
if (!(exists($topics{$this_id}))) { $topics{$this_id} = 1; }
}



TIA,

Jason

Re: MySQL & Perl: Using a Dynamic SELECT Statement

On Nov 14, 4:47 am, Jason Carlton wrote:
> I'm trying to set up a dynamic SELECT statement, but it's not doing
> what I'm expecting. Is it not possible to make a dynamic SELECT
> statement, or am I simply doing it wrong?
>
> # FIRST ATTEMPT:
> if ($check_username) {
> if ($type eq "loose") {
> $whereby1 = "WHERE username LIKE CONCAT('%',?,'%')";
> }
> else {
> $whereby1 = "WHERE username=?";
> }
> my $sth = $dbh->prepare("SELECT id FROM posts " . $whereby1);
> $sth->execute($search);

You're using variables without showing us what they contain. What's
in $search? The fact that you haven't shown it suggests you consider
it irrlevant, but since you don't know what's going wrong, you
shouldn't make that assumption.

> while (my ($this_id) = $sth->fetchrow_arrayref()) {
> if (!(exists($topics{$this_id}))) { $topics{$this_id} = 1; }
> }
> }
>
> This attempt is giving me the complete database, as if $whereby1
> doesn't exist. I've checked to make sure that it does

You've checked how? What debugging statement did you use? Did you
print the value of $sth->{Statement} to see what SQL is actually being
sent to the database handle?

> , so the SELECT statement simply isn't allowing the dynamic WHERE
> operator.

This makes no sense. The SELECT statement is a string. Nothing
more. It does not "allow" or prevent anything.

> But since this isn't working, I thought that I would just remove
> the $whereby1 variable altogether, and loop through $sth for every
> option, even though this would add a LOT more overhead.
>
> # SECOND ATTEMPT:
> if ($check_username) {
> if ($type eq "loose") {
> my $sth = $dbh->prepare("SELECT id FROM posts WHERE username
> LIKE CONCAT('%',?,'%')");

You are declaring $sth here.

> $sth->execute($search);
> }

And the block in which you declared it ends here. At this point, $sth
ceases to exist.

> else {
> my $sth = $dbh->prepare("SELECT id FROM posts WHERE
> username=?");

Now you've declared a second $sth.
> $sth->execute($search);
> }

And here the block in which you declared this second $sth ends. At
this point, the second $sth also ceases to exist.

> while (my ($this_id) = $sth->fetchrow_arrayref()) {

Now you are attempting to use a variable that doesn't exist.

> if (!(exists($topics{$this_id}))) { $topics{$this_id} = 1; }
> }
>
> }
>

> This attempt gives me the error "Can't call method "fetchrow_arrayref"
> on an undefined value", as if $sth hasn't been defined.

This tells me that you're not using strict. Why? It prevents you
from making mistakes like this. It's also rude to ask us for help
before you ask the computer for help.

Paul Lalli

Re: MySQL & Perl: Using a Dynamic SELECT Statement

> > if ($check_username) {
> > if ($type eq "loose") {
> > $whereby1 = "WHERE username LIKE CONCAT('%',?,'%')";
> > }
> > else {
> > $whereby1 = "WHERE username=?";
> > }
> > my $sth = $dbh->prepare("SELECT id FROM posts " . $whereby1);
> > $sth->execute($search);
>
> You're using variables without showing us what they contain. What's
> in $search? The fact that you haven't shown it suggests you consider
> it irrlevant, but since you don't know what's going wrong, you
> shouldn't make that assumption.

In this case:

$check_username = true;
$type = "loose"; OR $type = "strict";
$search = "Jason";

All three are set dynamically, but for testing I included a print
statement that showed the values.


> > while (my ($this_id) = $sth->fetchrow_arrayref()) {
> > if (!(exists($topics{$this_id}))) { $topics{$this_id} = 1; }
> > }
> > }

> You've checked how? What debugging statement did you use? Did you
> print the value of $sth->{Statement} to see what SQL is actually being
> sent to the database handle?

I did not print $sth, good idea. For testing, though, I simply added a
"print" statement to the loop.


> This makes no sense. The SELECT statement is a string. Nothing
> more. It does not "allow" or prevent anything.

My thought exactly, which is why I'm rather confused. I thought that
perhaps I might be writing the SELECT statement incorrectly.


> > if ($check_username) {
> > if ($type eq "loose") {
> > my $sth = $dbh->prepare("SELECT id FROM posts WHERE username
> > LIKE CONCAT('%',?,'%')");
>
> You are declaring $sth here.
>
> > $sth->execute($search);
> > }
>
> And the block in which you declared it ends here. At this point, $sth
> ceases to exist.
>
> > else {
> > my $sth = $dbh->prepare("SELECT id FROM posts WHERE
> > username=?");
>
> Now you've declared a second $sth.
>
> > $sth->execute($search);
> > }
>
> And here the block in which you declared this second $sth ends. At
> this point, the second $sth also ceases to exist.
>
> > while (my ($this_id) = $sth->fetchrow_arrayref()) {
>
> Now you are attempting to use a variable that doesn't exist.

Gotcha. That makes sense, and really was not the preferred way,
anyway. I was assuming that fetchrow_arrayref() was going to grab the
$sth->execute outside of the block, but I see now that this was
incorrect.


> This tells me that you're not using strict. Why? It prevents you
> from making mistakes like this. It's also rude to ask us for help
> before you ask the computer for help.

Actually, I had used strict, but it gave me less information than when
I used CGI::Carp. All it said was "aborted due to compilation errors."

Thanks for the help,

Jason

Re: MySQL & Perl: Using a Dynamic SELECT Statement

Jason Carlton wrote:
>> > if ($check_username) {
>> > if ($type eq "loose") {
>> > $whereby1 = "WHERE username LIKE CONCAT('%',?,'%')";
>> > }
>> > else {
>> > $whereby1 = "WHERE username=?";
>> > }
>> > my $sth = $dbh->prepare("SELECT id FROM posts " . $whereby1);
>> > $sth->execute($search);
>>
>> You're using variables without showing us what they contain. What's
>> in $search? The fact that you haven't shown it suggests you consider
>> it irrlevant, but since you don't know what's going wrong, you
>> shouldn't make that assumption.
>
> In this case:
>
> $check_username = true;


.... and what is the return value from the true() function then?

Or was that supposed to be a string?

(strings in Perl are delimited by quote characters.)


>> And here the block in which you declared this second $sth ends. At
>> this point, the second $sth also ceases to exist.
>>
>> > while (my ($this_id) = $sth->fetchrow_arrayref()) {
>>
>> Now you are attempting to use a variable that doesn't exist.


>> This tells me that you're not using strict. Why? It prevents you
>> from making mistakes like this. It's also rude to ask us for help
>> before you ask the computer for help.


At least he wasn't sleep-deprived this time and blaming the resulting
mess on everybody else:


Message-ID: <1158029547.380968.166590@e63g2000cwd.googlegroups.com>


--
Tad McClellan
email: perl -le "print scalar reverse qq/moc.noitatibaher\100cmdat/"