Note: I'm a Postgres user, so don't know much about mySQL -- somethings below may not make sense in mySQL

since everybody else is suggesting an approach with two tables, I'll try to be creative and suggest a way with one table. I don't know the pros and cons between two tables and one table, but here goes...

If you want to push the processing to the backend DB, then you probably will have make table like so:

   CREATE TABLE name2ip (
          name   text -- don't make this a primary key!
          ip     text -- or inet, or whatever mySQL has
   );

[download]

Then just insert all the IP's in name:ip pairs. So it would be like ( John Doe, ip1 ), ( John Doe, ip2 ), ( Mary Jane, ip3 ), etc.... When you retrieve all IP's for a name, you do 

   SELECT ip FROM name2ip WHERE name = somename;

[download]

This way you can push the processing of the ip's to the DB, withou doing any manual comparison. Like this

   SELECT name FROM name2ip WHERE ip = someip; -- or, LIKE blah or wha
+tever

[download]

Other ways that I can think of is maybe using an array for the type of the IP address column, but that suffers the same disadvantages as just storing a long text, with each IP address concatnated with some delimiter. Namely, since you can't do a "Does this array contain element X?" type of call from the SQL statement ( at least not in Postgres! ), you're back to processing your data manualy in perl, and you also most likely have some sort of upper limit to the size of the column. ( I think in Postgres it was 4k or 8k )

You may be able to use large objects as well, but again, you need to do some manual processing in perl, plus it's kind of annoying ( in my experience dealing with large objects, anyway )

In reply to Re: Yet Another Half Perl Half mySQL Question by lestrrat
in thread Yet Another Half Perl Half mySQL Question by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • 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! —
  • 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:
    & &amp;
    < &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.