Jump to content


Photo

Search Two Tables in MATCH? help sql syntax.


  • Please log in to reply
5 replies to this topic

#1 Elgan

Elgan
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 14 March 2006 - 11:11 AM

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


"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");



This is what i tried at first:



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')


but the browser locks up. i also tried

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')

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?

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 14 March 2006 - 02:25 PM

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.

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'))

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):

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')


#3 Elgan

Elgan
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 15 March 2006 - 02:33 AM

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.


            $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");



$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.

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


it would look like this to get score

            $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");




hm.




#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 15 March 2006 - 07:13 AM

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.

#5 Elgan

Elgan
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 15 March 2006 - 02:51 PM

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:)

#6 Jandal

Jandal
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 02 April 2006 - 09:25 PM

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

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;

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
=-)





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users