Search Two Tables in MATCH? help sql syntax.

[Elgan]

i am trying to write a search script. I have 3 tables where i need info from. 2 to be searched. I have tried various things but what i try usualy locks up the browser.

this is what i have to convert to FULL TEXT SEARCH

[code]

"SELECT p.fp_id, t.ft_title, t.ft_id, t.ft_creationdate, t.ft_firstpostername , t.ft_firstposterid,
t.ft_postcount , s.fs_id, s.fs_title
FROM db_forum_posts p, db_forum_topics t, db_forum_sections s
WHERE 1 AND (p.fp_text LIKE '".($sqstr)."' OR t.ft_title LIKE '".($sqstr)."')
$atleastq
$dspresultsp
AND p.fp_topicid=t.ft_id
AND p.fp_sectionid=s.fs_id
GROUP BY t.ft_id");

[/code]


This is what i tried at first:


[code]

SELECT p.fp_id,
t.ft_title, t.ft_id, t.ft_creationdate, t.ft_firstpostername , t.ft_firstposterid, t.ft_postcount , s.fs_id, s.fs_title
FROM sed_forum_posts AS p, sed_forum_topics AS t, sed_forum_sections AS s
WHERE p.fp_topicid=t.ft_id
AND p.fp_sectionid= s.fs_id
AND MATCH (p.fp_text) AGAINST ('admin pro')
OR MATCH (t.ft_title) AGAINST ('admin pro')

[/code]

but the browser locks up. i also tried

[code]
SELECT p.fp_id,
t.ft_title, t.ft_id, t.ft_creationdate, t.ft_firstpostername , t.ft_firstposterid, t.ft_postcount , s.fs_id, s.fs_title
FROM sed_forum_posts AS p, sed_forum_topics AS t, sed_forum_sections AS s
WHERE p.fp_topicid=t.ft_id
AND p.fp_sectionid= s.fs_id
AND MATCH (p.fp_text, t.ft_title) AGAINST ('admin pro')

[/code]
However i get a SQL error for MATCH. #1210 - Incorrect arguments to MATCH

how would i correctly structure this so i can search for text in one table and title text in another table at once and return them and their row? how also would i do it and order by score/relevance.

Would you like to see the tables?

[wickning1]

AND comes before OR in the order of operations. You just need to add parentheses. The way you had it, it was unable to use the join conditions. That's why it timed out your browser.

[code]SELECT p.fp_id,
t.ft_title, t.ft_id, t.ft_creationdate, t.ft_firstpostername , t.ft_firstposterid, t.ft_postcount , s.fs_id, s.fs_title
FROM sed_forum_posts AS p, sed_forum_topics AS t, sed_forum_sections AS s
WHERE p.fp_topicid=t.ft_id
AND p.fp_sectionid= s.fs_id
AND (MATCH (p.fp_text) AGAINST ('admin pro')
OR MATCH (t.ft_title) AGAINST ('admin pro'))[/code]

This is why I like to use the "INNER JOIN table ON joincondition=1" syntax. It separates your join conditions from the rest of the query, makes it easier to understand what's going on, and harder to mess up. Here is how I would do it (functions identically to what is above):

[code]SELECT p.fp_id,
t.ft_title, t.ft_id, t.ft_creationdate, t.ft_firstpostername , t.ft_firstposterid, t.ft_postcount , s.fs_id, s.fs_title
FROM sed_forum_posts p
INNER JOIN sed_forum_topics t ON p.fp_topicid=t.ft_id
INNER JOIN sed_forum_sections s ON p.fp_section_id=s.fs_id
WHERE MATCH (p.fp_text) AGAINST ('admin pro')
OR MATCH (t.ft_title) AGAINST ('admin pro')[/code]

[Elgan]

wow ty, I tried your 'INNDER JOIN' solution, it works flawless. I also learnt alot. Great reply.

The performance between INNER JOIN and the parentheses version is no different?

EDIT: WIll it automaticaly sort in order of score? I now have.


[code]
            $sql = sed_sql_query("SELECT p.fp_id,
            $sqlpost $sqltopic t.ft_title, t.ft_id, t.ft_creationdate, t.ft_firstpostername , t.ft_firstposterid, t.ft_postcount , s.fs_id, s.fs_title
            FROM sed_forum_posts p
            INNER JOIN sed_forum_topics t ON p.fp_topicid=t.ft_id $atleastq $dspresultsp
            INNER JOIN sed_forum_sections s ON p.fp_sectionid=s.fs_id $sqlsections
            WHERE MATCH (p.fp_text) AGAINST ('".$sqstr."')
            OR MATCH (t.ft_title) AGAINST ('".$sqstr."') GROUP BY t.ft_id $orderby $DACT");


[/code]

$orderby contains if the user wants to order by post count/ abc etc. If i leave the ORDER blank, How will it order? As i have read you have to do extra work to order it by score?



according to this example i googled.

[code]
        SELECT *,
            MATCH(title, body) AGAINST ('PHP') AS score
        FROM articles
        WHERE MATCH(title, body) AGAINST('PHP')

[/code]

it would look like this to get score

[code]
            $sql = sed_sql_query("SELECT p.fp_id,
            $sqlpost $sqltopic t.ft_title, t.ft_id, t.ft_creationdate, t.ft_firstpostername , t.ft_firstposterid, t.ft_postcount , s.fs_id, s.fs_title
            INNER JOIN sed_forum_topics t ON p.fp_topicid=t.ft_id $atleastq $dspresultsp
            INNER JOIN sed_forum_sections s ON p.fp_sectionid=s.fs_id $sqlsections
            WHERE MATCH (p.fp_text) AGAINST ('".$sqstr."')
            OR MATCH (t.ft_title) AGAINST ('".$sqstr."') GROUP BY t.ft_id AS score

            FROM sed_forum_posts p
            INNER JOIN sed_forum_topics t ON p.fp_topicid=t.ft_id $atleastq $dspresultsp
            INNER JOIN sed_forum_sections s ON p.fp_sectionid=s.fs_id $sqlsections
            WHERE MATCH (p.fp_text) AGAINST ('".$sqstr."')
            OR MATCH (t.ft_title) AGAINST ('".$sqstr."') GROUP BY t.ft_id $orderby $DACT");


[/code]


hm.

[wickning1]

Yes, both queries I wrote for you will do the exact same thing, have the exact same performance. They are just two ways to write the same thing.

FULL-TEXT searches will automatically be ordered by relevance, so the more times each word matches (plus some other stuff), the higher it is in the return set. I'm not sure how well it will behave when you're searching two tables, but it probably handles it fine.

It does take quite a bit of work to try and implement your own relevance rules. You basically need to create your own index and forget FULL-TEXT searching.

[Elgan]

well it works good. I dont reallly need to be going adding more relevancy rules, so order by works also, so i will leave it at that., thanx for the help:)

[Jandal]

Hello Elgan,

Did you get the score to work?

I am trying to do a similiar query to you.

I have a query like this

[code]
SELECT *,
MATCH( tableA.name ) AGAINST('$search')
OR MATCH( tableB.text ) AGAINST('$search') AS score
FROM tableA
INNER JOIN tableB ON tableA.id = tableB.id
WHERE MATCH( tableA.name ) AGAINST('$search')
OR MATCH( tableB.text ) AGAINST('$search') ORDER BY score DESC;
[/code]

But the problem is that all of my scores are 1 which doesn't help with the indexing.

Do you have any tips please?

Thanks in advance,
Jandal
=-)