RE: Number Searches
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. You might want to look into MySQL functions INET_ATON() and INET_NTOA(): http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you are not an intended recipient of this message, or an agent responsible for delivering it to an intended recipient, you are hereby notified that you have received this message in error, and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you received this message in error, please notify the sender immediately, delete the message, and return any hard copy print-outs. This message has been scanned for viruses by McAfee's Groupshield. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number Searches
On Fri, 2006-02-24 at 10:02 -0600, John Trammell wrote: 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. You might want to look into MySQL functions INET_ATON() and INET_NTOA(): http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html WOW, thank you! learn something every day. This is why I love the IT field! :) Thanks again. -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) signature.asc Description: This is a digitally signed message part
RE: Number Searches
Probably the problem is in php, or, more probably, in how you store first and then look for the IP address in your query. You should try your query in the mysql console; varchars work almost with anything and I put this example where I look for an IP address with your table, and it finds it correctly. Hope this helps; if you can't find the problem, try little steps with select * from portal_forums_users where ip = '192.168.1.0'; To try and find where you have a problem. You can even try select * from portal_forums_users where ip like '%192.168.1.0%'; The % are wildcards, and that would take care of periods you inadvertenly added/erased. I really think this is not a MySQL problem. mysql describe portal_forums_users; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | user_id | bigint(255) | | PRI | NULL| auto_increment | | ip| varchar(200) | YES | | NULL|| | signup_date | varchar(30) | YES | | NULL|| | city | varchar(200) | YES | MUL | NULL|| | state | varchar(100) | YES | | NULL|| | email_address | varchar(200) | YES | | NULL|| | username | varchar(100) | YES | | NULL|| | password | varchar(100) | YES | | NULL|| | yim | text | YES | | NULL|| | aol | text | YES | | NULL|| | web_url | text | YES | | NULL|| | post_count| varchar(255) | YES | | NULL|| | info | text | YES | | NULL|| | sig | text | YES | | NULL|| | avatar| text | YES | | NULL|| | css_id| int(11) | YES | | NULL|| | mod_f | varchar(20) | YES | | NULL|| | admin | varchar(20) | YES | | NULL|| +---+--+--+-+-++ 18 rows in set (0.00 sec) mysql select * from portal_forums_users; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | | 2 | 10.100.1.1 | y | y| y | y | y | y| y| y| y | y +-+-+-+--+---+---+--+--+--+--+-+ 2 rows in set (0.00 sec) mysql select * from portal_forums_users where ip = '192.168.1.0'; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | +-+-+-+--+---+---+--+--+--+--+-+ 1 row in set (0.00 sec) -Mensaje original- De: CodeHeads [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 22 de febrero de 2006 17:35 Para: Ariel Sánchez Mora CC: mysql@lists.mysql.com Asunto: RE: Number Searches 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
RE: Number Searches
On Thu, 2006-02-23 at 10:49 -0600, Ariel Sánchez Mora wrote: Probably the problem is in php, or, more probably, in how you store first and then look for the IP address in your query. You should try your query in the mysql console; varchars work almost with anything and I put this example where I look for an IP address with your table, and it finds it correctly. Hope this helps; if you can't find the problem, try little steps with select * from portal_forums_users where ip = '192.168.1.0'; To try and find where you have a problem. You can even try select * from portal_forums_users where ip like '%192.168.1.0%'; The % are wildcards, and that would take care of periods you inadvertenly added/erased. I really think this is not a MySQL problem. mysql describe portal_forums_users; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | user_id | bigint(255) | | PRI | NULL| auto_increment | | ip| varchar(200) | YES | | NULL|| | signup_date | varchar(30) | YES | | NULL|| | city | varchar(200) | YES | MUL | NULL|| | state | varchar(100) | YES | | NULL|| | email_address | varchar(200) | YES | | NULL|| | username | varchar(100) | YES | | NULL|| | password | varchar(100) | YES | | NULL|| | yim | text | YES | | NULL|| | aol | text | YES | | NULL|| | web_url | text | YES | | NULL|| | post_count| varchar(255) | YES | | NULL|| | info | text | YES | | NULL|| | sig | text | YES | | NULL|| | avatar| text | YES | | NULL|| | css_id| int(11) | YES | | NULL|| | mod_f | varchar(20) | YES | | NULL|| | admin | varchar(20) | YES | | NULL|| +---+--+--+-+-++ 18 rows in set (0.00 sec) mysql select * from portal_forums_users; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | | 2 | 10.100.1.1 | y | y| y | y | y | y| y| y| y | y +-+-+-+--+---+---+--+--+--+--+-+ 2 rows in set (0.00 sec) mysql select * from portal_forums_users where ip = '192.168.1.0'; +-+-+-+--+---+---+--+--+--+--+-+ | user_id | ip | signup_date | city | state | email_address | username | password | yim | aol | web_url | post_count +-+-+-+--+---+---+--+--+--+--+-+ | 1 | 192.168.1.0 | x | x| x | x | x | x| x | x| x | x | x| x| x | 0 | x | x | +-+-+-+--+---+---+--+--+--+--+-+ 1 row in set (0.00 sec) -Mensaje original- De: CodeHeads [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 22 de febrero de 2006 17:35 Para: Ariel Sánchez Mora CC: mysql@lists.mysql.com Asunto: RE: Number Searches 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
Re: Number Searches
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]
Re: Number Searches
On Thu, 2006-02-23 at 22:04 -0500, Mathieu Bruneau wrote: 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 Thanks for the reply :) Yes I was figured that was the problem but I wanted to make sure that was it. I did get it working like Ariel suggested. (where ip='192.168.1.1') That worked. I just did a separate search for the IP's. Thanks again for all your help. -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) signature.asc Description: This is a digitally signed message part
Re: Number Searches
- Original Message - From: CodeHeads [EMAIL PROTECTED] To: MySQL-List mysql@lists.mysql.com Sent: Wednesday, February 22, 2006 4:52 PM Subject: Number Searches Hello all, I have searched but cannot find what I am looking for. I have a full index index on a table and on of the fields is a number field (IP Address). Can MySQL search for numbers?? I don't know the answer to your question but I'm pretty sure it has come up before in this list. You might want to do a search on this topic in the MySQL archives at http://lists.mysql.com/. I think the answer depends on exactly how you store your IP addresses, i.e. datatype and number of columns used. -- Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.12/266 - Release Date: 21/02/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Number Searches
On Wed, February 22, 2006 4:52 pm, CodeHeads wrote: I have a full index index on a table and on of the fields is a number field (IP Address). Can MySQL search for numbers?? I'm potentially introducing confusion here, because I've never actually _used_ MySQL, but can't you do one of the following things: a)Store the number as a string with guaranteed uniqueness, then search by a string (which I think MySQL will do). In the case of an IP address, to make the (IP) - (STRING) mapping have no collisions, I think all you need to do is prepend with zeros to reach length three on each number, i.e. 192.168.0.10 would become 19216810. b)Store the IP as a 64-bit integer. I think MySQL will key on strings, right? Dave. --- David T. Ashley ([EMAIL PROTECTED]) Thousand Feet Consulting, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number Searches
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 -Mensaje original- De: CodeHeads [mailto:[EMAIL PROTECTED] Enviado el: miércoles, 22 de febrero de 2006 15:53 Para: MySQL-List Asunto: Number Searches Hello all, I have searched but cannot find what I am looking for. I have a full index index on a table and on of the fields is a number field (IP Address). Can MySQL search for numbers?? Thanks, Will -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Number Searches
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 -- Best regards, ~WILL~ Key: http://code-heads.com/keys/ch1.asc Key: http://code-heads.com/keys/ch2.asc Linux Commands: http://code-heads.com/commands Linux Registered User: 406084 (http://counter.li.org/) signature.asc Description: This is a digitally signed message part
