Re: match an IP address

Phoenix Kiula wrote:
>>> my IP addresses are stored in a TEXT type field. that field can actually
>>> contain usernames like 'joao' or 'scott' and it can contain IP
>>> addresses....
>
>
>
> I think this is common DB design on many websites that have registered
> user IDs.

Is it? Name one! Sounds like crappy design to me.

> My humble suggestion would be to make another column in the table
> called "user_registered" or something. Make this an int(1). If the
> user is registered, value is 1, otherwise 0.

Maybe "registration" (aka authentication) is mixed up with simple
session handling?

> To update your existing data onetime, run an SQL update query looking
> for IP pattern with 3 dots (which is likely IP address and not user
> id) or by the INET conditions in previous suggestions.

It could also just be a fdn (hostname) - still I'm a bit puzzled
how a username and an IP-address can get into the same field.

> Next, include the columns "user_id" and "user_registered" in the same
> index. This won't increase the size of the index too much, as the
> user_registered field is a small INT number. But from now on your SQL
> queries:
>
> ...WHERE user_id = 'testuser' AND user_registered = 1
>
> will return much faster. I have found this to the most convenient and
> fastest solution in PGSQL instead of haivng INET in the WHERE clauses.
>

Can you give example on where the inet entry is going to be used?

Cheers
Tino