Search results not what they should be

I have been trying desperately to fix a problem with either my code or the mysql DB!

I am using PHP.

I have a lot of rows about 4000 or so, all set as ‘text’ dont know why but still.

I have a search page i wrote but it does not perform correctly, it does not find words i search for.

i have been told to change to using a ‘full text search’ method but this seems too much work, or have i got the wrong idea? is it easy to convert?

it was suggested that the mysql DB could be corrupt but i have checked i think it is ok, but how can i be sure?

to test further i backed up everything and create a new DB and tables and inserted manually entries with a few words for me to search for and then tested my scripts and all seems ok, it worked. but my DB put back in place caused problems again.

just thinking about it are there any special characters that i should NOT use that may cause the DB to seem corrupt?

i am going to create a subdomain and setup a new DB so it can be tested again by maybe someone who could help solve this problem i am having. any offers?

also how easy is it to convert my DB to using the full text search method and what examples of code can anyone give me to explain how this actually works so that the exact phrase being searched for is shown and not all that look like the phrase, as in the … like %word% … method

thanks in advance for your help as i am now going mad with frustrastion as it should not be so hard to do this should it. but i seem to be getting it all wrong!


You may want to consider looking at sphinx search - if you can live with the fact that there is scheduled indexing so results are not always 100% up-to-date, it is much better than full text search: