How to implement FULL TEXT SEARCH?

Hi,
edit generated ..._list.php file for this purpose.

Use example in the thread mentioned before.

Jane, I replaced the code in main_list with :$strWhere = "MATCH ( Details, Title , PartNumber ) AGAINST ('".$strSearchFor."')";
//$strWhere = "1=0";

//if($where=StrWhere("Details", $strSearchFor, $strSearchOption, ""))

// $strWhere .= " or ".$where;

//if($where=StrWhere("Title", $strSearchFor, $strSearchOption, ""))

// $strWhere .= " or ".$where;

//if($where=StrWhere("PartNumber", $strSearchFor, $strSearchOption, ""))

// $strWhere .= " or ".$where;
$strWhere = "MATCH ( Details, Title , PartNumber ) AGAINST ('".$strSearchFor."')";
$strWhereClause = whereAdd($strWhereClause,$strWhere);
//$strSQL = AddWhere($strSQL,$strWhere);
I have created FULLTEXT indexes on Mysql server named Details, Ttitle and PartNumber
Indexes: Keyname Type Cardinality Action Field

PartNumber FULLTEXT 2508 PartNumber

Details FULLTEXT 235 Details

Title FULLTEXT 1505 Title
When I run a search I get this error message:
Error type 256

Error description Can't find FULLTEXT index matching the column list

URL www.myairparts.com/main_list.php?a=search&value=1&SearchFor=nbvbnv&SearchOption=Contains&SearchField=

Error file /home/content/l/o/r/lorenbond/html/include/dbconnection.php

Error line 26

SQL query SELECT main.Category, main.Details, main.Picture, main.Posted, main.Subcategory, main.Title, main.UserID, main.PartNumber, main.Price, main.Manufacturer, main.CC, main.Updated, main.URL, main.ID, register.email, register.fullname, register.Phone, register.`Company Name`, register.Logo, register.username FROM main LEFT OUTER JOIN register ON main.UserID = register.username where MATCH ( Details, Title , PartNumber ) AGAINST ('nbvbnv') ORDER BY main.ID DESC
Question: What am I doing wrong?

Hi,
this code is for Full text search.

To search through two or more keys parse $strSearchFor and construct custom where clause manually.

Here is just a sample:

$arr = explode(",",$strSearchFor);

Ok, This is what works for me:
Find the following code within the main_list.php or whatever list page you are working with.
*

$strWhere = "1=0";

if($where=StrWhere("Details", $strSearchFor, $strSearchOption, ""))

$strWhere .= " or ".$where;

if($where=StrWhere("Title", $strSearchFor, $strSearchOption, ""))

$strWhere .= " or ".$where;

if($where=StrWhere("PartNumber", $strSearchFor, $strSearchOption, ""))

$strWhere .= " or ".$where;

if($where=StrWhere("URL", $strSearchFor, $strSearchOption, ""))

$strWhere .= " or ".$where;

....

**
Replace the above listed code with the following code:
*****

$strWhere = "MATCH ( Details, Title , PartNumber, URL ) AGAINST ('".$strSearchFor."'IN BOOLEAN MODE)";

$strWhereClause = whereAdd($strWhereClause,$strWhere);
**
If you read through this thread you will see that I originally created 3 indexes, 1 for each Field and then attempted to search against all 3 fields.

at the same time. This produced an error that had me stumped for some time.
Solution:
The trick to is.... BEFORE running the code you must first apply a SINGLE Full text index to the search fields in your MSQL database.
I Used a SINGLE index that indexes 4 fields: Details, Title, PartNumber, URL and named it MASTER
It doesn't matter what you name the index but it does matter that the fields that you want to search are indexed.
With that said you could use a single index on a single field in you Mysql database example: (SomeIndexName on Description) and your code would look like this: $strWhere = "MATCH ( Description ) AGAINST ('".$strSearchFor."'IN BOOLEAN MODE)"; Where "Description" is some field in you database that you have assigned to it a FULLTEXT index.
There is a lot to say about this subject. BOOKS have been written on the subject!
After many hrs of trial and error the following link is what finally made things click for me:
Getting Started With MySQL's Full-Text Search Capabilities
Thanks Jane, for your imput. A little info goes a long way. Anything, even the smallest tid bits of information, is HIGHLY appreciated!
Anyone that can add something in regards to FULL TEXT SEARCHING to this thread would be appreciated by me and I'm sure many others <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=36101&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />

Using 4.2(build 379)
To make the changes permanent find this code in the source file list.php:
$strWhere = "1=0";

foreach Fields as @f filter @f.bSearch##

Replace the source code with the following code inclosing it within an (if then else statement) specifying what list you want effected by the change.

(Substitute "main" and "own_records" for the names of the tables in your database)
if($strTableName!="main" && $strTableName!="own_records")

{

$strWhere = "1=0";

foreach Fields as @f filter @f.bSearch##

I noticed that when I used a natural Msql FULL TEXT search the results would be returned in by rank. To make this meaningful I would have to alter the sort order of the query. No big problem but still I would get a lot of returns I didn't want.
A more explicit multiple keyword search was needed.
As the above listed examples show I used the BOOLEAN option to help with this problem; however, that would mean that the user would have to place a "+" sign in front of each word for a more explicit word search.
To work around this problem I used the following code:
$arr = explode(" ",$strSearchFor);

$strSearchFor = implode(" +", $arr);

$strSearchFor="+".$strSearchFor;
Basically I dissected the string ("THIS IS A TEST STRING") and added the "+" operands where the search string would look like this ("+THIS +IS +A +TEST +STRING")
Doing this forces the user to use the BOOLEAN function without having to include the operator.
The nice thing about doing this, in thisway is that you get a more explicit search return and the other operators "-", "absolute string" and "*" the wild card including the exclusion of noise words still works as they should . <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=36136&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />
Using this method simulates search returns used by ebay as opposed to search returns used by google. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=36136&image=2&table=forumreplies' class='bbc_emoticon' alt=':wub:' />
This is probably not a conventional way of doing things but it appears to resolve my search issues. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=36136&image=3&table=forumreplies' class='bbc_emoticon' alt=';)' />

Using version 4.2 (build 379)

UPDATE
How to implement Full Text Search Using PHP

NOT Mysql full text search
Find the following code within the main_list.php or whatever list page you are working with.
*

$strWhere = "1=0";

if($where=StrWhere("Details", $strSearchFor, $strSearchOption, ""))

$strWhere .= " or ".$where;

if($where=StrWhere("Title", $strSearchFor, $strSearchOption, ""))

$strWhere .= " or ".$where;

if($where=StrWhere("PartNumber", $strSearchFor, $strSearchOption, ""))

$strWhere .= " or ".$where;

if($where=StrWhere("URL", $strSearchFor, $strSearchOption, ""))

$strWhere .= " or ".$where;

....

**
Replace the above listed code with the following code:
*****
$strSearchFor=ltrim($strSearchFor); // removes left spaces

$strSearchFor=rtrim($strSearchFor); // removes right spaces

$kt=split(" ",$strSearchFor); // Breaking the string to array of words

$q=""; // Initialize a variable
while(list($key,$val)=each($kt)) // Generates the search string portion of the sql Statement

{

if($val<>" " and strlen($val) > 0)

{

$q .= "(Details like '%$val%' OR "; // Details = Column in database to be searched

$q .= " Title like '%$val%' OR "; // Title = Column in database to be searched

$q .= " PartNumber like '%$val%' OR "; // PartNumber = Column in database to be searched

$q .= " URL like '%$val%' OR "; // URL = Column in database to be searched

$q .= " Subcategory like '%$val%') "; // Sucategory = Column in database to be searched

}

}
$strWhereClause=substr($q,0,(strLen($q)-5)); // Deletes the last "AND" from the end of the string
**
The above code will search the database on DETAILS,TITLE,PARTNUMBER,URL and SUBCATEGORY.

The code will return all records that contain all search words.

Example: User Data imput = "King KI 209 Indicator"

you can customize the script to search for ANY record containing ANY of the words by removing the color=#FF0000 from the Sql string.

Once again, anything you would like to add to this thread would be appreciated.

Hi, The Parts Hanger,
Thanks very much for sharing. I am wondering whether you have the same working code for version 5.0 (Build 766)?