MySQL Match...Against syntax for a partial match?

I am using the SEARCH AGAINST MySQL syntax in a site I am working on to search several fields for a submitted search. However, it's not working so well for partial matches. My client wants to be able to (for instance) search for a name, such as Smith, and have it find Bob Smith's record in addition to Jim Smithfield.

Here's the actual query:

PHP Code:

---

MATCH(Issue.keywords,Issue.description) AGAINST('{$search}' IN BOOLEAN MODE ) 


---

No, the fields aren't names, but that was the best example I can think of. Any thoughts? I appreciate any help that you can offer.

Thanks,
Frank

Hey

By adding a star after your $search, the query will match any pattern starting with $search, i.e. searching 'Smith*' will match 'Smith' 'Smithy' etc. Unfortunately there's no way to match $search inside another pattern so searching '*at*' won't match 'data' for example.
If you need this, you might need to explode the search string (split it using a given delimiter, i.e. space), and then use an OR combination of LIKE clauses. For example, suppose you have a people database where you need to search over 3 fields: firstname, lastname and address. Someone searches for 'ob mit'. First you would split the query in two separate strings 'ob' and 'mit' and then the SQL query would look like:

Code:

---

SELECT *
FROM sometable
WHERE UPPER(firstname + lastname + address) LIKE UPPER('%ob%')
OR UPPER(firstname + lastname + address) LIKE UPPER('%mit%');

---

That would concatenate the fields you need to search for and try to match them with each substring provided by the user. This should match something like:

Code:

---

Firstname    Lastname    Address
=================================
Bob          ...        ...
Robert        Smith      ...
Mitch        ...        ...
...          ...        ... Observatory ...

---

Good luck