MySQL Forums
Forum List  »  Full-Text Search

fulltext search on two tables with mapping IDs
Posted by: Alex Gunshot
Date: October 05, 2006 10:00AM

Hello,

I'm taking the liberty to cross-post from http://forums.mysql.com/read.php?10,119372,119372, as it turns out my problem is a bit more specific. I have the following situation:

I have two tables, one with posts ('posts') that have an ID ('post_id'), and another one where the post IDs are mapped to categories; this table is called 'post2cat'. In 'post2cat', for each 'post_id', there is one or more rows with 'category_id'.

Now I'm constructing a query which should select a row from 'posts' only if the post_id has an entry in 'post2cat'.

I want to do a fulltext search, my query currently looks like this (as PHP variable):

$ctas_query = '
SELECT DISTINCT *,' .
' MATCH(' .
'post_title, post_content)' .
' AGAINST ("' . $ctas_any . $ctas_all . $ctas_none . '")' .
' AS score' .
' FROM ' . $table_prefix . 'posts, ' . $table_prefix . 'post2cat' .
' WHERE' .
' MATCH(' . $table_prefix . 'posts.post_title, ' . $table_prefix . 'posts.post_content)' .
' AGAINST("' . $ctas_any . $ctas_all . $ctas_none . '"' .
' IN BOOLEAN MODE ' .
' )' .
' AND post_date_gmt <= "' . date('Y-m-d H:i:s') . '"' .
' AND (' .
' (post_password = "" AND (post_status = "static" OR post_status = "publish"))' .
' AND post_status != "draft"' .
' AND post_status != "static"' .
')' .
' AND post_status != "attachment" ' .
$ctas_cats .
$ctas_authors .
' GROUP BY ' . $table_prefix . 'posts.ID' .
' ORDER BY ' . $ctas_sortby . $ctas_order .
' LIMIT 0, 10';

where I thought $ctas_cats could be the categories. (I hope the above code is not too confusing. If so, I'd post it again without the variables.)

Could somebody possibly give me hint on how to construct the query? Thanks in advance.

Options: ReplyQuote


Subject
Views
Written By
Posted
fulltext search on two tables with mapping IDs
4407
October 05, 2006 10:00AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.