Jump to content

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


metho

Recommended Posts

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.

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.