View Full Version : Minor "searching a table" problem - need quick help

09-22-2006, 08:01 PM
Hello everybody! Thank you for taking the time to help me.
Here's the issue:

USER types "car dog house" in a text field that sets the $criteria variable in php.
Is there any way that I can ask mySQL to search for all the three keywords separately?

$sql = mysql_query("SELECT * FROM thisismytable LIKE ".$criteria."%);

this is if I search for the whole string - if I make a query for all these three there is a chance that a row containing all of them would appear as a result three times...

It ought to be some fundamental trick that I am missing so please help..

09-22-2006, 08:44 PM
The fundamental trick is called fulltext searching.


09-22-2006, 11:51 PM
Shadowfox sent me a PM and said:

In the database you have:
Field1 ##### Field2 ##### Field 3
7235 ##### Red brown fox ##### canoe

When someone enters "Red 7235" I'd like mySQL to give me an array of the results but not searching for the whole string "Red 7235" but rather "Red" and then "7235". I parse the string and make "LIKE" queries for every single keyword. Thus I will get that "7235 Red brown fox canoe" row 2 times because it contains both the keywords - do you get it?

I do apologise in case I am asking bizarre questions or showing unquestionable confusion but I really do have to get this simple thing working...

I would GREATLY appreciate if you would give me a sample of how a query that I need should look like (and how many queries)

IN CASE you still recommend FULLTEXT I'd like to know whether it searches a "FULLTEXT" for a criteria or parses a "FULLTEXT" criteria and checks every keyword in the table.

Yes I would still recommend FULLTEXT searching. I don't have a lot of experience with it so I don't feel like I can guide you through the minutia, but I know how powerful it can be and it has everything built in to it so you can use it instead of writing a series of complex "like" queries.

You must create a FULLTEXT index on each field you intend to use in fulltext searches.

A fulltext query along the lines of what you're after would look something like this:

FROM table
WHERE MATCH (field1,field2)
AGAINST ('+string1 +string2' IN BOOLEAN MODE)

Spend some quality study time on the manual (http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html) and you'll catch on.