Intelligent Product Search help

[nerhael]

I have a site with a bunch of products. Searchable fields are in various tables. Ie, main table is say product, which has 1 text field that's searchable.

There's also a product_extra table with another text field. There's a 1 to many relationship between those two.

When doing a search, I'd like it to poll all that data, and then return results in some intelligent order based on a match ratio. I would like words like 'the' 'a' etc, basically all articles, ignored.

I have a feeling using fulltext columns, and the match function are what I need, but wondering if others would agree. Would I basically then have to create a new field in the main product table full of keywords based on all the pollable fields across all associated tables? And create the fulltext field based on that? Is there an easy way to strip out words like 'the' and such? Or am I left to my own devices there?

I welcome all input.

Pete.

[Dylan Leblanc]

If you are doing searches on text columns, you may want to use a keyword table to search through instead. Doing a "brute-force" search on text columns could overburden your server if there is a lot of text and many searches done.

They keyword table would contain one entry for every distinct word from the text columns. There would also need to be an associative table to link keywords to the correct rows in the Text table.

Here is an example of this, which you could try on your machine.

The SQL to create the three tables, and data for the Text table

# Table structure for table `Keyword-Text`
CREATE TABLE `Keyword-Text` (
`keywordID` int(11) NOT NULL default '0',
`textID` int(11) NOT NULL default '0',
PRIMARY KEY (`keywordID`,`textID`)
) TYPE=MyISAM;

# Table structure for table `Keyword`
CREATE TABLE `Keyword` (
`keywordID` int(11) NOT NULL auto_increment,
`keyword` varchar(20) NOT NULL default '',
PRIMARY KEY (`keywordID`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

# Table structure for table `Text`
CREATE TABLE `Text` (
`textID` int(11) NOT NULL auto_increment,
`text` text,
PRIMARY KEY (`textID`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

# Dumping data for table `Text`
INSERT INTO `Text` VALUES (1, 'fore because that because nevermind figure disable position word thought');
INSERT INTO `Text` VALUES (2, 'hat fought test might mess cat found able determin caught okay');
INSERT INTO `Text` VALUES (3, 'house plane book dog spoon book paper found chimney tree able');
INSERT INTO `Text` VALUES (4, 'down mountain boat ocean fought south moon tree because away');
INSERT INTO `Text` VALUES (5, 'stop dead tree because fought find speaker plant shirt red green');


The following code will take all the words in the Text table and put them into the Keyword table and Keyword-Text associative table accordingly. It's not the most robust code, but just an example. (Make sure you don't already have a table named Keyword, or it will be tuncated!)

mysql_query('TRUNCATE Keyword');
mysql_query('TRUNCATE `Keyword-Text`');

$query = mysql_query('SELECT textID, text FROM Text');

while ($row = mysql_fetch_object($query)) {

// turn the text into an array of the individual words
$words = explode(' ', $row->text);

foreach ($words as $word) {

// see if that word already exists in the Keyword table
$row2 = mysql_fetch_object(mysql_query("SELECT keywordID FROM Keyword WHERE keyword = '{$word}'"));

// if it doesn't, then insert it and get the id
if (!$row2) {
mysql_query("INSERT INTO Keyword (keyword) VALUES ('{$word}')");
$keywordID = mysql_insert_id();
}

// else get it's id
else {
$keywordID = $row2->keywordID;
}

// insert both ids into Keyword-Text associative table
mysql_query("INSERT INTO `Keyword-Text` (keywordID, textID) VALUES ($keywordID, $row->textID)");
}
}


Then you can do a query like this to search for words in the text:
SELECT
COUNT(Keyword.keywordID) AS countKeywords,
Text.text

FROM Keyword
INNER JOIN `Keyword-Text` ON `Keyword-Text`.keywordID = Keyword.keywordID
INNER JOIN Text ON Text.textID = `Keyword-Text`.textID

WHERE
Keyword.keyword IN ('down', 'because')

GROUP BY Text.textID

ORDER BY countKeywords DESC, Text.text ASC

The query will return:
countKeywords text
------------- ----
2 down mountain boat ocean fought south moon tree because away
1 fore because that because nevermind figure disable position word thought
1 stop dead tree because fought find speaker plant shirt red green
Each of these returned rows contain either the word down or because, but notice how using ORDER BY countKeywords DESC in the query gives the row containing both of the words first.