RE: Need a query to show distinct IP dotted quad components [SOLVED]

[Daevid Vincent]

I may have just solved my own problem:

SELECT DISTINCT(SUBSTRING_INDEX(INET_NTOA(IP_Addr), '.', 3)) as niceip FROM
IPTable HAVING niceip LIKE '192.168.15%';

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html


> -----Original Message-----
> From: Daevid Vincent [mailto:daevid@stripped] 
> Sent: Friday, May 19, 2006 2:46 PM
> To: mysql@stripped
> Subject: Need a query to show distinct IP dotted quad components
> 
> I have a table of many IP addresses. I'm doing some 
> PHP/JS/AJAX to populate
> a select box based upon what someone types in a search field. 
> That works
> great, except that a user can spend a lot of time guessing as to what
> possible IPs exist. 
> 
> What I'd like to do now is one of those "google" suggestions 
> thingys where
> as you type an IP it suggests the DISTINCT possible next numbers in a
> drop-down <DIV>.
> 
> So if I had:
> 
> 	192.168.12.[1 .. 254]
> 	192.168.15.[1 .. 254]
> 	192.168.158.[1 .. 254]
> 	172.16.2.[1 .. 254]
> 
> Then if I typed "1", the suggestions would be:
> 
> 	192.168.12.
> 	192.168.15.
> 	192.168.158.
> 	172.16.2.
> 
> Then if I typed a "9" next (so I have "19" in the box) the 
> suggestions would
> be:
> 
> 	192.168.12.
> 	192.168.15.
> 	192.168.158.
> 
> Skipping forward, if I had "192.168.15" in the box the 
> suggestions should
> be:
> 
> 	192.168.15.
> 	192.168.158.
> 
> And so forth...
> 
> Anyone have any hints or ideas as to how to formulate a SQL 
> query or bunch
> of queries to get these 'lists' of results?
> 
> I'd also be okay with it only working on quad boundaries (.) 
> if that is
> substantially easier.
> 
> I currently store IPs as INT values for obvious reasons, but 
> there is the
> handy "INET_NTOA(IP) as niceip" so a HAVING clause should be 
> able to use
> that I suspect.
> 
> 
> Thanks,
> 
> Daevid.
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
> 
>