how to query a field of type text for an item?

[metho]

I have a table with say 2 fields, one is username of type varchar, the other is info of type text.

 

I have an entry in the table:

 

username = 2

text = a b c d e f

 

I would like to query the table and return a value if there is a match for the username with any of 'a b c d e or f'. I tried this >

 

 SELECT * FROM `tablename` WHERE `username` = '2' AND `info` = 'a';

 

but it gives an error however, if I type >

 

SELECT * FROM `staff` WHERE `username` = '2' AND `info` = 'a b c d e f';

 

it will return a match, but I would ideally like a match if any of the letters are searched for in the query. I am just doing the queries in phpMyAdmin which is why the ` and ' are present. Any help would be great.

 

[effigy]

use the % wildcard: like '%a%'; also see fulltext.

[metho]

use the % wildcard: like '%a%'; also see fulltext.

303951[/snapback]

 

 

I tried using the wildcards % and * and had no luck..??

[metho]

I tried using the wildcards % and * and had no luck..??

304105[/snapback]

 

 

I converted the tables to engine type MYISAM in order to alter the field to fulltext, when I try the following in phpmyadmin :

 

SELECT * FROM `tablename` WHERE MATCH (info) AGAINST ('a');

 

It returns no errors, but doesn't display any result? info is indexed as type fulltext, and i have some entries with text in the 'info' field which contain the letter 'a' , any ideas?

[metho]

I converted the tables to engine type MYISAM in order to alter the field to fulltext, when I try the following in phpmyadmin :

 

SELECT * FROM `tablename` WHERE MATCH (info) AGAINST ('a');

 

It returns no errors, but doesn't display any result?  info is indexed as type fulltext, and i have some entries with text in the 'info' field which contain the letter 'a' , any ideas?

304132[/snapback]

 

 

I think i found what is wrong!! Whenever i try a search, if the text in the info field contains 1 word, less than 4 characters, it will not give back a result, also, if i have more than 1 word, it will only give a result if the first word in the field is searched for. This is my code

 

SELECT * FROM `tablename` WHERE username = 'test' AND MATCH (info) AGAINST ('+hello' IN BOOLEAN MODE)

 

That will return a result, but if i edit the field and change 'hello' to just 'hel' and then replace '+hello' to '+hel' or even '+hel*' or % it will not give a result.

 

Any ideas?

 

Thanks

[effigy]

fulltext:

 

Any word that is too short is ignored. The default minimum length of words that are found by full-text searches is four characters.

 

The minimum and maximum length of words to be indexed is defined by the ft_min_word_len and ft_max_word_len system variables (available as of MySQL 4.0.0). See Section 5.3.3, “Server System Variables”. The default minimum value is four characters. The default maximum depends on your version of MySQL. If you change either value, you must rebuild your FULLTEXT indexes. For example, if you want three-character words to be searchable, you can set the ft_min_word_len variable by putting the following lines in an option file:

 

[mysqld]

ft_min_word_len=3

 

Then restart the server and rebuild your FULLTEXT indexes. Also note particularly the remarks regarding myisamchk in the instructions following this list.