Re^2: Referencing MySQL Results by Column

[TheBiscuit]

in reply to Re: Referencing MySQL Results by Column
in thread Referencing MySQL Results by Column

I apologize for not being clear enough, but this is a Perl question. The script I am trying to modify currently runs a SELECT query with a WHERE statement multiple times within a foreach loop. I would like to run the query once and pull the necessary data from an array instead. Below is the segement of code I'm working with:

# Bind to LDAP Server
$ldap = Net::LDAP->new( $ldapServer ) or die "$@";
$bind = $ldap->bind( $ldapUser, password => $ldapPass );

# Connect to SQL Server
$connect = Mysql->connect($sqlServer, $sqlDB, $sqlUser, $sqlPass);
$connect->selectdb($sqlDB);

$ldapResult     = $ldap->search(
        base => $ldapBaseOU,
        filter => "(&(usertype=Employee))",
        attrs => @ldapAttrs
);

$sqlQuery       = "SELECT username,email,phone FROM users";
$sqlResult      = $connect->query($sqlQuery);

print $sqlResult;

my @entries = $ldapResult->entries;

my $entry;
foreach $entry ( @entries )
{
        my $username = $entry->get_value ( 'sAMAccountName' );
        my $dn = $entry->dn;
        
        ### ***** Insert Match Here ***** ###
        $phone = ??
        $email = ??

}

# Unbind from LDAP Server
$bind = $ldap->unbind;

In the section noted as "***** Insert Match Here *****, I would like to be able to use the $username value pulled from LDAP and match it to the 'username' value pulled from MySQL to set the phone and email variables.

Thanks!

[Krambambuli]

What sort of object is Mysql ?

Maybe you could have what you're after via DBI's selectcol_arrayref.

[zwon]

Do not use Mysql, it is obsolete module and its own documentation recommends to use DBI. DBI has selectall_arrayref method which you can use to get all records from the table. If you want to be able to get phone and email by username you can store result into hash like this (not tested):

my $res = $dbh->selectall_arrayref("SELECT username, phone, email FROM
+ users");
my %users = map { $_->[0] => [ $_->[1], $_->[2] ] } @$res;
for (@usernames) {
    my ($phone, $email) = @{$users{$_}};
}