Need Help With Search Query

[arandlett]

I have built a search query. However doesn't seem to be working like I need it. I can search by just typing one word that will match one column however if I add multiple words the search will not work. I didn't think full text searching would work because the columns that I am searching do not have a lot text. At the most a sentance of text. Should I still use Full Text searching & how do I do that? Or can something be done in the query below to solve my problem.


[code]SELECT *
FROM Members
WHERE `First` LIKE %colname% OR `Last` LIKE %cname1% OR `City` LIKE %cname2% OR `dac` LIKE %cname3% OR `dnc` LIKE %cname4% OR `min_teams` LIKE %cname5% OR `Industry` LIKE %cname6% OR `status` LIKE %cname7%[/code]

[bibby]

[b]LIKE '%blah%'[/b]  is a really bad idea. Putting % at both ends is impossible to index, so it's incrediblt inefficent.

Try [b]MATCH field AGAINST (blah);[/b]

[arandlett]

Can I use that sytax without setting up a fulltext index?

So would be something like this:


[b]SELECT *
FROM Members
MATCH (First,Last,City,dac,dnc,min_teams,Industry,status) AGAINST (colname)  AND level <> 3[/b]

[fenway]

You'd need a FULLTEXT index, yes.

[arandlett]

Ok I set a fulltext index in the table with two columns "First, Last" for testing.  Second I changed the sql query to read :

[b]SELECT *, MATCH (First,Last) AGAINST ('$searchKey') FROM Members[/b]

However when I go to test it I get the same results.  Where I can only search by one word and not to.  So I can either type in a "First" word or a "Last" word to pull any results.  When I type " First  Last " in the keyword field nothing returns. 

[fenway]

Weird... you should be able to include as many words as you want.

[arandlett]

Does the name of my Index make a difference?  Right now I have it labled "Name";   

So when I create the index can I add as many columns as I want out of that table?  Or is it better practice to make a second fultext index?

[arandlett]

Ok I figured It out however need some wildcard to it.  Right now you can search by multiple words but those words have to exactly like they are minus capitals in the columns.
[b]
SELECT *, MATCH (First,Last,education,Address1,City,Zip,Email,officer,dac,min_teams,status) AGAINST ('$searchKey') FROM Members WHERE  MATCH (First,Last,First,Last,education,Address1,City,Zip,Email,officer,dac,min_teams,status) AGAINST ('$searchKey') AND level <> 3[/b]

How do I change this so that if they type a part of that word or if it is almost like that word it will still pull results. Thank you for all the help.

[fenway]

I'm fairly sure there's a maximum, I think it's 16 columns; and I don't see why you can't use wildcards.