[Resolved] fulltext search without boolean mode

buraks78

hi all,

i was planning to provide my clients with different search options and realized that i could use the boolean mode of fulltext queries. After a few minutes of joy, i was devastated by the fact that it was not supported until mysql version 4.0.1 which is not available at my hosting company. sadly... 🙁

so i came up with this code to search for records containing all of the keywords specified....


$Keys=explode(" ",$Keywords);
$n_elements=count($Keys)-1;

$sql_append="MATCH(Products,Introduction,CompanyName,CompanyType) AGAINST('" . $Keys[0] . "')";

for($i=1;$i<=$n_elements;$i++){
$sql_append .= " AND MATCH(Products,Introduction,CompanyName,CompanyType) AGAINST('" . $Keys[$i] . "')";
}

$sql = "SELECT * FROM Textile WHERE " . $sql_append;
$result = mysql_query($sql, $db) or die (mysql_error());
$n = mysql_num_rows($result);

If ($n!=0){
$offset = 10;
$sql = "SELECT * FROM Textile WHERE " . $sql_append . " LIMIT $page,$offset";
$result = mysql_query($sql, $db) or die (mysql_error());
}

the problem is that it seems to scan only products, and introduction fields. for instance when i search for "silk" and "manufacturer" at the same time i can not retrieve any results (there is at least on company matching this criteria and i do not receive any error). but when i search for "silk" and "manufacture" i can retrieve three records since both of these words are included in products and introduction fields.

is this caused by the use of multiple fulltext queries at the same time? if the answer to the previous question is yes, is there any way to search for records containing all of the keywords?

any help would be appreciated.

regards...

timj

I don't know the MATCH( ) AGAINST ( ) operator in SQL, but you might try something like:
where field1 like 'searchpattern' and field2 like 'searchpattern2' etc. You can thus combine more than 1 search pattern (for multiple fields), using And, Or, Not in any combination.
Searchpatterns would look like:
'%userstring%' with userstring=the string your user wants to search for.

If you want to search for multiple patterns on the same field, you could use the "IN" operator:
WHERE field1 IN (possible values). However, wildcards are not permitted here. You could simulate this behaviour (with search patterns) by using the OR operator:
WHERE field1 like patt1 OR field1 like patt2, etc (this will check if the value of field1 matches any of the patterns). Use And instead of Or if you want to search for the records that match ALL the patterns.

buraks78

i knew about the LIKE operator, but if you search this forum you will probably find a lot complaints about its performance problem, and thats why i dont use it.

however, i dont know anything about the IN operator. visited mysql.com and searched the documentation but no luck (i guess they are also using the Fulltext syntax which just ignores words shorter than 4 characters.)

it would be great if you could provide me with an example..

tx...

buraks78

nevermind i found the page....

thanks for your reply...

timj

Well, I don't know MySQL, but the IN operator is part of the standard SQL language, so it should be supported. Personnally, I've only used Oracle, but I plan to use MySQL soon.

An example of the IN operator? Well, it's quite simple actually, but like I said, it does not work with patterns, only literal strings.
eg:
Let's say that you're looking for car prices (in a price catalogue table that stores some info on cars as well as their prices). You're only interested in expensive sportscars. Then you could use a query like:
select car_price, car_model from car_catalogue where car_brand IN ('Ferrari','Porsche','Lamborghini');
This will simply select all records where the field car_brand matches any of the 3 brands that are given. It's as simple as that.