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.)

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.