Jump to content


Photo

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


  • Please log in to reply
5 replies to this topic

#1 metho

metho
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 07 October 2005 - 11:03 AM

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.


#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 07 October 2005 - 06:39 PM

use the % wildcard: like '%a%'; also see fulltext.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 metho

metho
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 08 October 2005 - 04:25 AM

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

View Post



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

#4 metho

metho
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 08 October 2005 - 07:18 AM

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

View Post



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?

#5 metho

metho
  • Members
  • Pip
  • Newbie
  • 4 posts

Posted 08 October 2005 - 07:57 AM

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?

View Post



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

#6 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 09 October 2005 - 04:21 AM

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.


Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users