MATCH AGAINST? LIKE? Which is best?

[darthmahon]

Hey,

I've set up a MySQL select that searches a table for results with the keywords specified. At the moment I am using the following query:

$query = "SELECT * FROM MARKETINGDOCUMENTS WHERE MATCH (filename,description) AGAINST ('$VarDescription')";

Now, this picks up on all results that have the EXACT word typed in. For example if I have the word "shuttle" in a field within a description and I type in "shuttle" it picks it up. But if I type in "shuttl" it doesn't pick it up. I've been told doing my query like this might help:

$query = "SELECT * FROM MARKETINGDOCUMENTS WHERE MATCH (filename,description) AGAINST ('$VarDescription') IN BOOLEAN MODE";

But my host isn't running MySQL 4 and apparently, it doesn't work in 3 so I'm a bit stuck there.

I've tried using the LIKE command and it works fine but it only retrieves records that has the word I have typed in, at the front of the record.

For example, I type in "Shuttle" and it will only select the records where "Shuttle" is at the beginning. If it's the second word in a sentence, it won't pick it up.

Any ideas on this guys?

Cheers,
Chris

[LordShryku]

I'd say you're using like wrong then. Need to put wildcard on the front and end...

[sid]

$query = "SELECT *
FROM MARKETINGDOCUMENTS
WHERE filename LIKE '%" . $VarDescription . "%'
OR description LIKE '%" . $VarDescription . "%'";

[darthmahon]

works a treat, but one thing.

If I type in "demands" and I have a word in the table that says "demand" then it doesn't pick it up? Any idea why that is?

[LordShryku]

Because you pluralized it, and it's looking for the word "demands" with anything in front of it, and anything behind it.

[darthmahon]

oh right, fair enough. Thanx for your help 🙂