Using DBI, Set Select to Variable
Using DBI, Set Select to Variable
am 27.08.2007 06:35:58 von jason
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
am 27.08.2007 16:09:04 von Petr Vileta
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
am 27.08.2007 17:44:43 von it_says_BALLS_on_your forehead
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
am 27.08.2007 17:55:03 von Paul Lalli
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
am 27.08.2007 18:20:01 von xhoster
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
am 06.09.2007 20:24:29 von ansok
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