CodeHeads a écrit : > On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote: >> So far i've been able to store ip addresses as strings like you would >> type them in DOS, for ex, '192.168.0.1'. This serves me great since my >> application uses IP addresses as strings in all cases. I've done >> queries with the IP column , for example, select office_name from >> table_1 where ip='10.100.1.1'; and have never had any problems. >> However, if you plan on sorting based on this column, strings with >> periods do not behave correctly, and the answers to my previous >> question on this list do not apply; it makes a good aproximation, >> though. >> >> Hope this helps, but I must admit I am not sure if this answers your >> question. An example in the mysql console would be great for clearing >> up your objetive. >> >> Regards, >> >> Ariel > > OK, I think I did not explain things right the first time. :( > > I have a table like so: > CREATE TABLE `portal_forums_users` ( > `user_id` bigint(255) NOT NULL auto_increment, > `ip` varchar(200) default NULL, > `signup_date` varchar(30) default NULL, > `city` varchar(200) default NULL, > `state` varchar(100) default NULL, > `email_address` varchar(200) default NULL, > `username` varchar(100) default NULL, > `password` varchar(100) default NULL, > `yim` text, > `aol` text, > `web_url` text, > `post_count` varchar(255) default NULL, > `info` text, > `sig` text, > `avatar` text, > `css_id` int(11) default NULL, > `mod_f` varchar(20) default NULL, > `admin` varchar(20) default NULL, > PRIMARY KEY (`user_id`), > FULLTEXT KEY `full_index` > (`city`,`state`,`username`,`email_address`,`ip`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > Notice the FULLTEXT; I have the ip listed to be indexed. Using a PHP > script I have it will not grab the IP that I am searching for, even > though it *is* in the database. The ip's are entered into the database > as 192.168.1.10. When I search for a username it works great. > > Is it because of the "." (periods) in the search string?? > > Hopefully I explained that right this time!! :) LOL >
Ok I got 2 informations for you: 1) IPv4 address are actually 32 bit integer, easily store in 32 bits fast search etc etc etc (You can google for more on this storage format). Normally you could find a way to goes from the string 192.168.1.1 to the equivalent int. Look for ip2long() function in PHP for example! 2) FULLTEXT indexes are a special type of index in MySQL, their use on numeric field doesn't make sense. To be used on ip string they would require some tweaking as they normally don't remember word under 3 letters if i'm correct. And last but not least they aren't use with a "like" but with a "match" You could however use an typical index here, or even better an unique index to ensure the validation! Hope it helps you in you development! See for all information about fulltext index in the manual http://dev.mysql.com/doc/refman/4.1/en/fulltext-search.html -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
