Using DBI, Set Select to Variable

I know how to load info into an array:

my $sth = $dbh->prepare("SELECT username, password FROM users WHERE
username=?");
$sth->execute($given_username);
while (my ($userID, $passID) = $sth->fetchrow_arrayref()) { ... }


What I can't figure out is how to load it to a single variable if I
know that there's only 1 value, instead of an array. For instance,
what if I only need to read "password" instead of "username,
password"?

Currently, the only thing I have been able to find is:

my $sth = $dbh->prepare("SELECT password FROM users WHERE
username=?");
$sth->execute($given_username);
while (my ($passID) = $sth->fetchrow_arrayref()) { ... }


In this case, "username" is a PRIMARY, so it can only be found once;
meaning, "password" either equals 1 field, or nothing. It seems stupid
to use a loop when I know that there's only one value in it, but I
can't find how to pull it out otherwise.

TIA,

Jason

Re: Using DBI, Set Select to Variable

Jason wrote:
> Currently, the only thing I have been able to find is:
>
> my $sth = $dbh->prepare("SELECT password FROM users WHERE
> username=?");
> $sth->execute($given_username);
> while (my ($passID) = $sth->fetchrow_arrayref()) { ... }
>
>

my $sth = $dbh->prepare("SELECT password FROM users WHERE username=?");
$sth->execute($given_username);
if($sth->rows > 0)
{
my ($passID) = $sth->fetchrow_arrayref()) { ... }
}

--

Petr Vileta, Czech republic
(My server rejects all messages from Yahoo and Hotmail. Send me your mail
from another non-spammer site please.)

Re: Using DBI, Set Select to Variable

On Aug 27, 12:35 am, Jason wrote:
> I know how to load info into an array:
>
> my $sth = $dbh->prepare("SELECT username, password FROM users WHERE
> username=?");
> $sth->execute($given_username);
> while (my ($userID, $passID) = $sth->fetchrow_arrayref()) { ... }
>
> What I can't figure out is how to load it to a single variable if I
> know that there's only 1 value, instead of an array. For instance,
> what if I only need to read "password" instead of "username,
> password"?
>
> Currently, the only thing I have been able to find is:
>
> my $sth = $dbh->prepare("SELECT password FROM users WHERE
> username=?");
> $sth->execute($given_username);
> while (my ($passID) = $sth->fetchrow_arrayref()) { ... }
>
> In this case, "username" is a PRIMARY, so it can only be found once;
> meaning, "password" either equals 1 field, or nothing. It seems stupid
> to use a loop when I know that there's only one value in it, but I
> can't find how to pull it out otherwise.

my ( $pass ) = $dbh->selectrow_array("SELECT password FROM users WHERE
username=?");

Re: Using DBI, Set Select to Variable

On Aug 27, 12:35 am, Jason wrote:
> I know how to load info into an array:

.... no you don't. :-)

> my $sth = $dbh->prepare("SELECT username, password FROM users WHERE
> username=?");
> $sth->execute($given_username);
> while (my ($userID, $passID) = $sth->fetchrow_arrayref()) { ... }

This code is broken. This code sets $userID to be a reference to an
array which contains the username and password, and sets $passID to
undef.

I think you meant either:
while (my $row = $sth->fetchrow_arrayref()) {
my ($userID, $passID) = @{$row};
...
}
OR
while (my ($userID, $passID) = $sth->fetchrow_array()) { ... }

> What I can't figure out is how to load it to a single variable if I
> know that there's only 1 value, instead of an array. For instance,
> what if I only need to read "password" instead of "username,
> password"?

> Currently, the only thing I have been able to find is:
>
> my $sth = $dbh->prepare("SELECT password FROM users WHERE
> username=?");
> $sth->execute($given_username);
> while (my ($passID) = $sth->fetchrow_arrayref()) { ... }

This is similarly broken. Again, $passID is a reference to an array
that contains only one element - the password.

> In this case, "username" is a PRIMARY, so it can only be
> found once; meaning, "password" either equals 1 field, or
> nothing. It seems stupid to use a loop when I know that
> there's only one value in it, but I can't find how to pull
> it out otherwise.

You don't need a loop. If you know there's only one result row, then
just read that one result row. Don't bother putting it into a loop:

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

Paul Lalli

Re: Using DBI, Set Select to Variable

Jason wrote:
> I know how to load info into an array:
>
> my $sth = $dbh->prepare("SELECT username, password FROM users WHERE
> username=?");
> $sth->execute($given_username);
> while (my ($userID, $passID) = $sth->fetchrow_arrayref()) { ... }

Presumably you mean fetchrow_array, not fetchrow_arrayref.


> What I can't figure out is how to load it to a single variable if I
> know that there's only 1 value, instead of an array. For instance,
> what if I only need to read "password" instead of "username,
> password"?

You seem to be confusing rows with columns. How you deal with multiple
columns and how you deal with multiple rows are basically orthogonal.


> Currently, the only thing I have been able to find is:
>
> my $sth = $dbh->prepare("SELECT password FROM users WHERE
> username=?");
> $sth->execute($given_username);
> while (my ($passID) = $sth->fetchrow_arrayref()) { ... }
>
> In this case, "username" is a PRIMARY, so it can only be found once;
> meaning, "password" either equals 1 field, or nothing. It seems stupid
> to use a loop when I know that there's only one value in it, but I
> can't find how to pull it out otherwise.

If you don't want the loop, then remove the loop. On the other
hand, what is wrong with having a loop that you know will loop
either 0 or 1 times? If you know it will loop exactly one time, then
it seems pointless, but if 0 times is possible and looping does what you
want done in that case, then use looping. If 0 times is an error
condition, then something like this:

my ($passID) = $sth->fetchrow_array() or die "No $user";

Xho

--
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service $9.95/Month 30GB

Re: Using DBI, Set Select to Variable

In article <1188229483.566269.31330@o80g2000hse.googlegroups.com>,
it_says_BALLS_on_your forehead wrote:
>On Aug 27, 12:35 am, Jason wrote:
>> I know how to load info into an array:
>>
>> my $sth = $dbh->prepare("SELECT username, password FROM users WHERE
>> username=?");
>> $sth->execute($given_username);
>> while (my ($userID, $passID) = $sth->fetchrow_arrayref()) { ... }
>>
>> What I can't figure out is how to load it to a single variable if I
>> know that there's only 1 value, instead of an array. For instance,
>> what if I only need to read "password" instead of "username,
>> password"?
>
>my ( $pass ) = $dbh->selectrow_array("SELECT password FROM users WHERE
>username=?");

This suggestion is missing how to pass the placeholder value to the query:

my ($pass) = $dbh->selectrow_array(
"SELECT password FROM users WHERE username=?",
undef, $given_username);

The second argument is used to pass options to the query. When no
options are being specified, as is the case here, you still must
pass in an undef (or {}) so that the values for the placeholders
will be found correctly in the remainder of the argument list.

Gary
--
The recipe says "toss lightly," but I suppose that depends
on how much you eat and how bad the cramps get. - J. Lileks