SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast dave_merwin's Avatar
    Join Date
    Apr 2003
    Location
    Eugene, OR
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    FULLTEXT and JOIN? How To?

    Here is my query.

    Here is I what I want it to do:
    Search 2 tables and return the results by relevancy.
    Using Full Text
    The Indexes work great because I can search each table seperately just fine. It is when I try to join them that I get the syntax errors from MySQL

    PHP Code:
    SELECT
    posts
    .post_content,
    categories.category_description 
    MATCH 
    (posts.post_contentcategories.category_description )
    AGAINST ('car')
    AS 
    score 
    FROM posts
    categories
    WHERE MATCH 
    (posts.post_contentcategories.category_description )
    AGAINST ('car'LIMIT 0,11 
    ORDER BY score DESC 
    Question 1. Why won't this work?
    Question 2. Where can I go to learn how it works?
    Dave Merwin

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What errors do you get?
    You will also have a cross join effect since you don't actually join the tables on a criteria from each table say tablea.id=tableb.id for instance.

    Also perhaps some rows from each table with sample output would make it clearer what you are trying to do.

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to create a full text index before you can use Match, and you cannot create a full text index on columns in 2 tables so you can not do this. Read the manual for Full Text Functions to learn about them.

  4. #4
    SitePoint Enthusiast dave_merwin's Avatar
    Join Date
    Apr 2003
    Location
    Eugene, OR
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do it. I read it in these forums. It has to do with adding 2 match statements. Then sorting what is combined. And, I already set up 2 seperate FULLTEXT indexes. I read the manual and it did not describe how to do a fulltext on two tables.

    I believe the principal is that the MATCH returns the number of records not the actual records. So you can add 2 MATCH statements in a query to get the total number of records and then sort by "score" or what ever.

    It is a syntax error. I can't reproduce it right this second. At home now. Basicaly it blows up when it enters the MATCH for the second table. That is where the error arrises. Like I said, the issue is in combining the two tables. Each individual query works fine.
    Dave Merwin

  5. #5
    SitePoint Enthusiast dave_merwin's Avatar
    Join Date
    Apr 2003
    Location
    Eugene, OR
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Also another FULLTEXT JOIN tutorial:
    http://www.onlamp.com/pub/a/onlamp/2.../fulltext.html

    That example does not work for me because I do not know enough about JOIN's. I know, however, that I will not be using the WHERE decleration the same way.
    Dave Merwin

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2005
    Location
    UK
    Posts
    121
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You have missunderstood me. The examples you have seen have a full text index on multiple columns in the SAME table. What I said was that you can not have such an index using columns from 2 tables, which is what your code is trying to do. Now if you have other columns that you can use to join the tables then you could do this:

    PHP Code:
    SELECT posts.post_contentcategories.category_description
    MATCH 
    (posts.post_contentAGAINST ('car') AS pscore,
    MATCH (categories.category_descriptionAGAINST ('car') AS cscore
    FROM posts INNER JOIN categories ON posts
    .cid=categories.cid
    WHERE MATCH 
    (posts.post_contentAGAINST ('car'
    OR 
    MATCH (categories.category_descriptionAGAINST ('car')  
    LIMIT 0,11
    ORDER BY score DESC 
    It will also work without the join condition but then you have a cartesian join of every row in posts with every row in categories and the query will take ages to run. So, I am assuming that posts ahve a category and that you should then use that for the join.

  7. #7
    SitePoint Enthusiast dave_merwin's Avatar
    Join Date
    Apr 2003
    Location
    Eugene, OR
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you hadn't guessed, this is for a blog. I am trying to use the FULLTEXT to search both posts and categories. Doesn't seem likely at this point however.

    So, it seems that I should run two different searches. Would that be faster? I.e. Run one search for posts and one for categories. I see your point about the "cartesian join" even if I have no idea what that means.

    Where can I learn all that stuff? Do you know any good books that could get me started? Thanks for your patience.
    Dave Merwin

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i wouldn't use fulltext searching on your categories, unless your categories have undisciplined, free form narrative type text in them (unlikely)

    i would search categories based on values in a MULTIPLE SELECT form element
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast dave_merwin's Avatar
    Join Date
    Apr 2003
    Location
    Eugene, OR
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A drop down box-- great idea. Duh!
    Dave Merwin


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •