Pure SQL query question

[EMark]

TABLE example

fields: name, description, address

record1
-------
name: Judo Center The Judokas
description: Dojo in the heart of the City of Paris. Also for Kids
address: Blossom Avenue 333, Paris

record2
-------
name: The Aikis
description: Aikido gym in brussels, Belgium. Amateur & Professional.
address: Mainstreet 44, Brussels


User searches for "Judo Center Paris", or "Aikido Brussels"
(let`s call the search "searchstring")

So far, i have come up with:
SELECT * FROM example WHERE name LIKE '%searchstring%' OR description LIKE '%searchstring%' OR address LIKE '%searchstring%'";

Obviously, this doesn`t return any of the records. Which SQL query should i use?

[DaveSawers]

Split the search string up into its individual words and then search using something like:

select * from example where name like '%string1%' or name like '%string2%' or name like '%string3%' or description like '%string1%' ...

This will return any result that has any of the words in any of the fields.

[EMark]

hi Dave,

your solution means that searching for "Judo Center Paris" will return all records containing "Judo" or "Center" or "Paris"
You will get results like:

Judo Center New York
or
Box Gym Paris

which is not what that user is looking for. That user is looking for a judo center in Paris..

[williamc]

Assuming you have indexed the 3 fields for fulltext searches, You would split the search term up and do something similar to the below for each term using a UNION and then ordering all final results by relevance.

SELECT *,
MATCH(`name`,`description`,`address`)
AGAINST ('+searchrequest' IN BOOLEAN MODE) AS relevance
FROM `mytable`
WHERE MATCH(`name`,`description`,`address`)
AGAINST ('+searchrequest' IN BOOLEAN MODE);

[EMark]

hi William, thanks for the reply.

Assuming you have indexed the 3 fields for fulltext searches

No idea. I`m using MySQL with Wordpress. Customized plugin was developped for directory listings.

Would it be possible to write out the WHOLE query cause i really don`t know how to use the UNION together with your example above. Thanks !

[monstercoder]

hi William, thanks for the reply.

No idea. I`m using MySQL with Wordpress. Customized plugin was developped for directory listings.

You should be able to go in using PHPMyAdmin and create indexes on the fields. That will speed up queries and by reducing the amount of the disk I/O.