Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

MySQL hashes with same key

by jcrush (Novice)
on Nov 16, 2012 at 03:24 UTC ( #1004111=perlquestion: print w/ replies, xml ) Need Help??
jcrush has asked for the wisdom of the Perl Monks concerning the following question:

# # # # # # # # # # # # 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 !

Comment on MySQL hashes with same key
Select or Download Code
Re: MySQL hashes with same key
by roboticus (Canon) on Nov 16, 2012 at 03:30 UTC

    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.

      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.
Re: MySQL hashes with same key
by NetWallah (Abbot) on Nov 16, 2012 at 05:01 UTC
    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.

                 "By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest."           -Confucius

      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.

        So, don't do that. It's the wrong thing to do in this case. :-/
        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.

                     "By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest."           -Confucius

      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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1004111]
Approved by shmem
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (8)
As of 2014-08-21 16:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (136 votes), past polls