MySQL hashes with same key

[jcrush]

# # # # # # # # # # #
#   SQL MAC Query   #
# # # # # # # # # # #
our $smq = $dbh->prepare("select distinct mac,comment from arp.filter 
where operation = 'filter' order by mac") or die "Couldn't prepare 
statement: " . $dbh->errstr;

# # # # # # # # # # # # # # # #
#   SQL Distinct MAC Table    #
# # # # # # # # # # # # # # # #
$smq->execute()
   or die "Couldn't execute statement: " . $smq->errstr;

print "# SQL Distinct MAC HASH #\n";

#   +----------------+-------------------------------------+
#   | mac            | comment                             |
#   +----------------+-------------------------------------+
#   | 1226.b0ab.317e | roving filtered mac                 |
#   | 1226.b0ab.317e | roving filtered mac [force filter   |
#   | 1226.b0ab.317e | [thor2] Excessive Campus Bandwidth  |
#   +----------------+-------------------------------------+
#   3 rows in set (0.04 sec)

our $mac_t = "1226.b0ab.317e"; 

my $ref = $dbh->selectall_hashref("select distinct mac,comment from 
arp.filter where  operation = 'filter' order by mac", 'mac');

foreach my $id (keys %$ref)
{
 if ($mac_t eq $ref->{$id}{mac})
 {
   print "Found a row: mac = $ref->{$id}{mac}, 
comment = $ref->{$id}{comment}\n";
 }
}

The results printed are missing all 3 rows of data

# SQL Distinct MAC HASH #
Found a row: mac = 1226.b0ab.317e, comment = [thor2] Excessive . . .
# END SQL Distinct MAC HASH #

Am I forced to use Arrays, and if so how is the best way to do that?
If using an Array
What happens if I want search one address, and print out all comments attached to that one mac address? (Reason for my hash usage)
For example, if I have 100 macs, and if one is in the database, then print out all the comments on that mac. Thank You, very much for the help Perl Monks !

[roboticus]

jason.c.rochon:

You don't *have* to use arrays. But it really depends on the result(s) you want. If you want all three comments to be tied to the same key, you could read your data like:

my $sth=$dbh->prepare("select ....");
$sth->execute;

# Stuff all the data in a hash, concatenating all comments
my %Macs;
while (my $hr = $sth->fetchrow_hashref) {
    if (! exists $Macs{$$hr{mac}}) {
        $Macs{$$hr{mac}} = $$hr{comment};
    }
    else {
        $Macs{$$hr{mac}} .= $$hr{comment};
    }
}

You could have each hash key hold an array of comments, too. It all boils down to how you're actually going to work with the data.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[jcrush]

Thank you for your response. I would like to print out a list of comments for one address. If I search for mac aaaa.aaaa.aaaa.aaaa Printout Mac: aaaa.aaaa.aaaa.aaaa Comments: filtered for virus filtered by dmca filtered for excessive bandwidth filter on spoofed mac Thanks again.

[NetWallah]

Your code and comments are confusing. If you want to obtain comments for a known mac address, why dont you use the database to do the appropriate query:

"select  comment 
from     arp.filter 
where    operation = 'filter' AND MAC='$mac_t'"

Also, Using fetchrow_arrayref instead of fetchrow_hashref will not make any difference to the resulting rows, as you seem to think it might.

[mje]

He wasn't fetching using fetchrow_arrayref or fetchrow_hashref he was fetching with selectall_hashref and it does make a difference:

SQL> create table mje(mac varchar(10), comment varchar(10));
SQLRowCount returns -1
SQL> insert into mje values('1226', 'comment1');
SQLRowCount returns 1
SQL> insert into mje values('1226', 'comment2');
SQLRowCount returns 1
SQL> insert into mje values('1226', 'comment3');
SQLRowCount returns 1
SQL> select * from mje;
+-----------+-----------+
| mac       | comment   |
+-----------+-----------+
| 1226      | comment1  |
| 1226      | comment2  |
| 1226      | comment3  |
+-----------+-----------+
SQLRowCount returns -1
3 rows fetched
SQL>

perl -le 'use DBI; my $h = DBI->connect;nect; my $r = $h->selectall_ha
+shref(q/select * from mje/, "mac"); use Data::Dumper; print Dumper($r
+);'
$VAR1 = {
          '1226' => {
                      'comment' => 'comment3',
                      'mac' => '1226'
                    }
        };

perl -le 'use DBI; my $h = DBI->connect;nect; my $r = $h->selectall_ar
+rayref(q/select * from mje/); use Data::Dumper; print Dumper($r);'
$VAR1 = [
          [
            '1226',
            'comment1'
          ],
          [
            '1226',
            'comment2'
          ],
          [
            '1226',
            'comment3'
          ]
        ];

If you use selectall_hashref or fetchall_hashref you need to provide a key column and if you have multiple rows where that key column has the same value you will lose some rows.

[NetWallah]

Thanks - I had missed the subtlities of the second parameter to 'selectall_hashref'.

However - my recommendation to use the database query to select comments for a mac stands, and answers the the questions he persists in posing.

[Anonymous Monk]

So, don't do that. It's the wrong thing to do in this case. :-/

[jcrush]

Thanks for the reply.
I was using my smartphone, at night. I cleared up the wording, I hope.
I would like to print out a list of comments for one address. If I search for mac aaaa.aaaa.aaaa.aaaa

Printout

Mac: aaaa.aaaa.aaaa.aaaa
Comments: filtered for virus 
filtered by dmca 
filtered for excessive bandwidth 
filter on spoofed mac

Thanks again.