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.