| Mathieu Bruneau 2006-02-25, 9:43 am |
| CodeHeads a écrit :
> On Wed, 2006-02-22 at 16:49 -0600, Ariel Sánchez Mora wrote:
>
> 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`,`use
rname`,`email_addres
s`,`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...ext-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/mysql? unsub...sie.nctu.edu.tw
|