On Thu, 11 Apr 2002 01:58:16 +0000, [EMAIL PROTECTED] (Matthew Harrison)
wrote:
> I am writing a login script using cookie and DBI to connect to a mysql
> database and I need to run a query like:
>
> "SELECT username from users"
>
> and have it return all the usernames in preferably an array so I can do
> something like:
>
> foreach $names (@results){
> if ($names eq $username){
> check the password and set the cookie
> }
> else{
> say that username not found
> }
>
> problem is that I can find a way to fetch all the rows. I have been using
> @results = fetchrow_array but that of course only gets one row. I don't
> understand arrayrefs at all so what can i do?
Roughly in ascending order of complexity --
Solution 1: if you're only fetching usernames to determine whether the
user exists in the database, ask the database:
# error checking left to the reader, or to 'RaiseError'
$sth = $dbh->prepare('SELECT username from users where username = ?');
$sth->execute($username);
if($sth->fetch) {
# user was found in database
} else {
# user was not found
# (or an error occurred)
}
This solution also has the advantage of less database traffic. And if
you have an index on the 'username' column (which I guess you do, since
it looks like a primary key of the 'users' table), then this query
should execute very quickly.
Solution 2: use selectcol_arrayref, which returns an arrayref, which you
can dereference and put in a foreach column:
$result = $dbh->selectcol_arrayref('select username from users');
foreach $name (@$result) {
# do whatever you want here: $name is a name
}
Solution 3: use selectall_arrayref or fetchall_arrayref, which returns
an arrayref of arrayrefs. Since you're only fetching one column, you
need element 0 of each arrayref.
$result = $dbh->selectall_arrayref('select username from users');
# or:
# $sth = $dbh->prepare('select username from users');
# $sth->execute;
# $result = $sth->fetchall_arrayref;
foreach $row (@$result) {
$name = $row->[0];
# do stuff with $name
}
Solution 4: Build up the array yourself:
$sth = $dbh->prepare('select username from users');
$sth->execute;
$sth->bind_columns( \$name );
while($sth->fetch) {
# $name is now automatically filled with the 'username' column
push @users, $name; # or you could process $name here directly
}
$sth->finish;
Check the documentation for your version of DBI.pm. For example, I
believe selectcol_arrayref was added relatively recently; if you have an
older version, it may not be implemented yet, and you might have to use
fetchall_arrayref instead.
Hope this helps.
Cheers,
Philip
