Hash table with no fully distinct keys

starburst has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I am creating a table that displays users and their associated systems that they have access to. I am using two database tables in order to map information between them. When a user uses my search bar, the bar uses that query to search a two column database for a matching user. It then uses the associated ID number and takes that ID number to query the other database for an associated system. The output of my queries should be a user with a system... My problem is that I have users that have access to multiple systems (therefore, multiple IDs from the first database)...but then there are also IDs (systems) with multiple users that have access to it. Here is the incomplete code I have in my fetch after the SQL command executes the initial search:

# Display content of search
while (  $ref = $sth->fetchrow_hashref()) {
   # $uniqueKey is the column 'user' from the DB. (ie $key is the spec
+ific user name)
   $key = $ref->{$uniqueKey};
   # GetSystem uses the user name to get the associated ID number from
+ DB1, and uses that value to get an associated system name from DB2  
   my $systemKey = GetSystem($key); 
   if (exists $referenceTable{$key}) {
      if (defined $referenceTable{key} eq $systemKey) {
         # do i need to do this?
      }
      else {
         push @{$referenceTable{$key}},  $systemKey;
      }
    } else {
         push @{$referenceTable{$key}},  $systemKey;
    }
}

my ($k,@v);
  
print "<table>";
print "<tr><th>User</th><th>System</th></tr>\n";
# SET UP THE TABLE
# EXECUTE THE WHILE LOOP
while (($k, $v) = each(%referenceTable)){
     print "<tr><td>".$k."</td>";
     print "<td>";
     foreach (@$v) {
        print "$_\n";
     }
     print"</td></tr>";
} 
print"</table>\n";
[download]

I am pretty sure my if (exists) and if (defined) statements are wrong/not the best way...but if you have some sort of idea of how I can continue to search for associated ID numbers if there are multiple instances of a user in the database I would appreciate it. Thanks Monks!

Comment on Hash table with no fully distinct keys Download Code

Replies are listed 'Best First'.
Re: Hash table with no fully distinct keys by toolic (Bishop) on Apr 16, 2010 at 22:00 UTC
`if (defined $referenceTable{key} eq $systemKey) {` [download] I don't believe you want to use defined that way. Here is a simple demonstration why: `use strict; use warnings; my %referenceTable = (me=>5, you=>2); my $systemKey = 5; for my $key qw(me she) { if (defined $referenceTable{$key} eq $systemKey) { print "$key if\n"; } else { print "$key else\n"; } } __END__ me else she else` [download] You probably would have expected to see "me if". You should use strict and warnings to catch typos such as `$referenceTable{key}`, which probably should be `$referenceTable{$key}`.	[reply] [d/l] [select]
Re: Hash table with no fully distinct keys by graff (Chancellor) on Apr 17, 2010 at 01:35 UTC
You don't say much about your tables, but I would assume they are something like: `>describe user; id ... name ... >describe system; id ... userid ...` [download] There would be one row in the user table per user. The system table could have multiple rows with the same userid (one person with access to multiple machines) and multiple rows with the same system.id (systems that handle multiple users) -- and, hopefully, no duplicate rows. You also weren't too clear about the kind(s) of search(es) people will want to use on this data, but I assume it would be something like: `select system.id from user, system where user.id = system.userid and user.name = ?` [download] or `select user.name from user, system where user.id = system.userid and system.id = ?` [download] In the first case, people want to know the systems available to a chosen user, whose name is passed to go into the placeholder; in the second case, people want to know which users have access to a given machine, so the system id goes into the placeholder. Other kinds of queries are possible -- e.g. if you add more fields to one or both tables in order to define groups of users or systems, and select one or more columns according to those fields instead of name/id, etc). But in all cases, you are just getting a list of rows, with one or more columns per row for display. And you can include things like "order by" and/or "limit" clauses. You don't really need much in the way of data structures in your perl script, because the SQL should be doing most of the work for you, and you can just loop over the array of arrays returned by DBI::fetchall_arrayref -- something like: `my $sth = $dbh->prepare( "select ..." ); # sql with "?" in there some +where $sth->execute( $param ); # pass a value from cgi params my $rows = $sth->fetchall_arrayref; if ( ref( $rows ) eq 'ARRAY' and @$rows ) { print "<table>"; print "<tr><th>User</th><th>System</th></tr>\n"; for my $row ( @$rows ) { print "<tr><td>" . join( "</td><td>", @$row ) . "</td></tr>\n" +; } print "</table>\n"; } else { print "<p>No matches found.</p>"; }` [download] Or maybe I'm missed something in the OP's statement of the problem? (UPDATE: I rearranged the last snippet, putting the DBI prepare / execute / fetch statements first, and adding a check for non-empty results before printing the markup for a table display. Also added missing double-quote in last print statement, and fixed missing "td" tag in the for loop.)	[reply] [d/l] [select]
Re: Hash table with no fully distinct keys by umasuresh (Hermit) on Apr 16, 2010 at 19:48 UTC
`use Data::Dumper; .... print Dumper(\%referenceTable);` [download] to dump the data structure	[reply] [d/l]
Re: Hash table with no fully distinct keys by rev_1318 (Chaplain) on Apr 17, 2010 at 19:40 UTC
Your basic problem is not a Perl problem, but a data modelling problem. You have a many-to-many relation between users and systems, so you should have 3(!) tables, 1 descibing users (id, name, etc), 1 describing systems (id, name, etc) and 1 for the relationship user_system (user_id, system_id) In the users and systems table, id is a unique identifier for the user/system, which are used as foreign keys in the relation table. If your data modle isn't sane, how do you expect your code to be sane? Paul	[reply]