Fulltext Search On Multiple Tables

[jonpugh114]

Hello,

 

I need to set up fulltext searching using three tables, I can do it fairly easily with one table but I'm struggling with this problem.

 

I was thinking that if it can't be done using a single query, maybe I could do separate queries for each table and try to return a numerical relevance score, which would allow me to merge the results.

 

Can anyone point me in the right direction?

 

Thanks,

 

Jon

[jonpugh114]

Actually, I think I've got it...

 

This is what I'm doing, and it seems to work:

 

 

//$keywords variable from search form

 

$query = '

SELECT page_id AS id, "content" AS tablename, MATCH (page_content) AGAINST ("'. $keywords .'") AS score FROM content WHERE MATCH (page_content) AGAINST ("'. $keywords .'") AND deleted = "n"

UNION

SELECT cat_id AS id, "category" AS tablename, MATCH (cat_desc) AGAINST ("'. $keywords .'") AS score FROM category WHERE MATCH (cat_desc) AGAINST ("'. $keywords .'")

UNION

SELECT prod_id AS id, "products" AS tablename, MATCH (prod_desc) AGAINST ("'. $keywords .'") AS score FROM products WHERE MATCH (prod_desc) AGAINST ("'. $keywords .'") ORDER BY score DESC';

 

$result = mysqli_query($dbc, $query);

while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {

 

if ($row['tablename'] == 'content') {

echo 'page - '. $row['id'] .'<br />';

}elseif ($row['tablename'] == 'category') {

echo 'category - '. $row['id'] .'<br />';

}elseif ($row['tablename'] == 'products') {

echo 'product - '. $row['id'] .'<br />';

}

 

}

 

 

I'm just returning id's at the moment, but obviously I'll be creating more meaningful results.

 

Cheers,

 

Jon

[HartleySan]

Certainly, using UNION is a possibility, but if possible, it might be best to join your tables instead, and then search on those results.

 

If anything, I find it odd that you need to search three tables, all separately. If that's the case, then you may want to consider redesigning your database. I mean, just at first glance, I would imagine that there is a connection between your categories and products tables, in which case, a join shouldn't be a problem.

 

If you'd like to provide more specifics regarding what you want, maybe we can help. Thanks.

[Stuart]

As far as I was aware it's not possible to run a FULLTEXT search across more than a single table - from PHP 6 and MySQL 5:

 

But a fulltext index can only be applied to a single table at a time, so more elaborate Web sites, with content stored in multiple tables, would benefit from using a more formal search engines

 

I'm also under the impression that using a UNION statement doesn't really work because the scores generated from one dataset cannot be compared to that from another accurately. In my opinion the two options would be either creating a non-normalised search table where you dump and index the text (and just enough to display search results) then link to the relevant page where you extract the full dataset from the source tables.

 

The other option would be to use Zend_Lucene to index the relevant text and then do a similar display and link process as above - Larry's recently written a great article on Zend_Lucene found here: http://www.larryullman.com/2010/10/27/creating-a-search-index-with-zend_search_lucene/

 

I also found this article useful: http://www.kapustabrothers.com/2008/01/26/part-ii-indexing-pdf-documents-with-zend_search_lucene/

 

Hope that helps

[HartleySan]

I wasn't aware of that Stuart. Thanks. Certainly, full-text searches are a tricky beast. I try to avoid them altogether.

 

I suppose another option would be to acquire all the necessary data from a query, and then use regexes to parse the returned results.

[jonpugh114]

The query seems to work, I've tested it and it has returned results as expected.

 

Regarding the database design, the search is for the whole site, and users would be looking for page content along side category/product information. I'm sure that I could (or maybe should) have designed the database so that all content occupies it's own table and I'll bear that in mind next time it comes up.

 

Thanks,

 

Jon