SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict trigger's Avatar
    Join Date
    Jun 2003
    Location
    Eagan
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Syntax for fulltext search through multiple tables

    http://www.sitepoint.com/forums/show...ultiple+tables

    this concerns me, but what I'm trying to do is do a fulltext search across multiple tables
    Code:
    $query_search_result = sprintf("SELECT * FROM ingram_inventory,sun_inventory WHERE MATCH (in_inventory.mfgPartNumber,sun_inventory.mfgPartNumber,in_inventory.description1,sun_inventory.description1,in_inventory.description2,sun_inventory.description2,in_inventory.vendorName,sun_inventory.vendorName,in_inventory.nickname,sun_inventory.nickname,in_inventory.CPUcode,sun_inventory.CPUcode,in_inventory.mediaCode,sun_inventory.mediaCode) AGAINST ('$colname_search_result')");
    this isn't working, I get a "Wrong arguments to MATCH" error message. So I tried
    Code:
    $query_search_result = sprintf("SELECT DISTINCT SKU FROM in_inventory WHERE govED='' 
    AND (MATCH (mfgPartNumber,vendorName,description1,description2,nickname,CPUcode,mediaCode) 
    AGAINST ('$colname_search_result')) 
    OR FROM sun_inventory WHERE govED='' 
    AND (MATCH (mfgPartNumber,vendorName,description1,description2,nickname,CPUcode,mediaCode) 
    AGAINST ('$colname_search_result')");
    and I'm getting an error code close to the "OR" statement. Is there something wrong with the OR statement, or is it something else? Is my logic in error completely? Is it useless to fulltext search varchar fields and such?
    Not another drugstore, not another town
    Tired of the overdrop, I had to put it down
    You know I was the man when I put the Crown down
    Then rolled around the Chemicals to pave the New Sound

  2. #2
    SitePoint Member donsimon's Avatar
    Join Date
    Apr 2004
    Location
    New Orleans, LA
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    I use Full Text all of the time and from my understanding you can do a join and a full text index at the same time. Then again, I haven't tried it before.

    I would recommend posting this one to the MySQL mailing list and I am sure you could find the answer in a few hours.

    But as I said before, I don't think it is possible based on my experience. And I've used every different variation of FULL Text around.

    Donny

  3. #3
    SitePoint Member donsimon's Avatar
    Join Date
    Apr 2004
    Location
    New Orleans, LA
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile

    You know what sometimes I speak to soon.

    It does work, and actually the example below was from a select with a subquery and a union in it.

    Code:
    SELECT MATCH ( 
    term1, term2)
    AGAINST ( 
    '$search'
    IN BOOLEAN
    MODE ) AS score2, term, related_count
    FROM BigDatabase.TermFull
    INNER JOIN XXXXXXXXX.TableTracker ON keyword = '$keyword' and keyword_related = '$search'
    WHERE MATCH ( 
    term1, term2)
    AGAINST ( 
    '+$search'
    IN BOOLEAN
    MODE )
    Have fun!

    Donny

  4. #4
    SitePoint Addict trigger's Avatar
    Join Date
    Jun 2003
    Location
    Eagan
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I do not quite understand

    I do not quite understand where the terms are coming from. So far I have:
    PHP Code:
    SELECT MATCH 
    mfgPartNumber,vendorName,description1,description2,nickname,CPUcode,mediaCode,subPartNumber)
    AGAINST 
    '$search_terms'
    IN BOOLEAN
    MODE 
    ) AS score2termrelated_count
    FROM BigDatabase
    .TermFull
    INNER JOIN XXXXXXXXX
    .TableTracker ON keyword '$keyword' and keyword_related '$colname_search_result'
    WHERE MATCH 
    mfgPartNumber,vendorName,description1,description2,nickname,CPUcode,mediaCode,subPartNumber)
    AGAINST 
    '+$search_terms'
    IN BOOLEAN
    MODE 

    "$search_terms" is the search terms entered in the search box from the form.
    "mfgPartNumber,vendorName,description1,description2,nickname,CPUcode,mediaCode,subPartNumber" are the terms in the fulltext index to search against in the tables.
    My table names are "in_inventory" and "sun_inventory". So I'm going to try to come to grips with "XXXXXXXXXXXXXX.TableTracker" and "keyword = '$keyword'" and "keyword_related" and the "BigDatabase.TermFull" stuff, but if you can lend some help, it would be much appreciated.
    Not another drugstore, not another town
    Tired of the overdrop, I had to put it down
    You know I was the man when I put the Crown down
    Then rolled around the Chemicals to pave the New Sound

  5. #5
    SitePoint Addict trigger's Avatar
    Join Date
    Jun 2003
    Location
    Eagan
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I'm working on it a little and I'm getting something that makes a little more sense.

    PHP Code:
    $search_result "((MATCH 
    (mfgPartNumber,vendorName,description1,descri
    ption2,nickname,CPUcode,mediaCode,subPartN
    umber)
    AGAINST ('
    $search_terms' IN BOOLEAN MODE) 
    AS score
    FROM sun_inventory)
    WHERE MATCH 
    (mfgPartNumber,vendorName,description1,descri
    ption2,nickname,CPUcode,mediaCode,subPartN
    umber)
    AGAINST ('
    $search_terms' IN BOOLEAN MODE))
    UNION
    (MATCH 
    (mfgPartNumber,vendorName,description1,descri
    ption2,nickname,CPUcode,mediaCode,subPartN
    umber)
    AGAINST ('
    $search_terms' IN BOOLEAN MODE) 
    AS score
    FROM in_inventory
    WHERE MATCH 
    (mfgPartNumber,vendorName,description1,descri
    ption2,nickname,CPUcode,mediaCode,subPartN
    umber)
    AGAINST ('
    $search_terms' IN BOOLEAN MODE))
    ORDER BY score DESC))"

    But I still get a error on line 1 of the query!
    Not another drugstore, not another town
    Tired of the overdrop, I had to put it down
    You know I was the man when I put the Crown down
    Then rolled around the Chemicals to pave the New Sound

  6. #6
    SitePoint Addict trigger's Avatar
    Join Date
    Jun 2003
    Location
    Eagan
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay, now I think I'm getting somewhere.
    PHP Code:
    $search_result "SELECT sun_inventory.mfgPartNumber,sun_inventory.vendorName,sun_inventory.description1,
    sun_inventory.description2,in_inventory.mfgPartNumber,
    in_inventory.vendorName,in_inventory.description1,
    in_inventory.description2,in_inventory.subPartNumber 
    AS relevance FROM 
    sun_inventory,in_inventory WHERE match
    (sun_inventory.mfgPartNumber,sun_inventory.vendorName,sun_inventory.description1,
    sun_inventory.description2,sun_inventory.subPartNumber) 
    against('
    $search_terms') 
    OR match
    (in_inventory.mfgPartNumber,
    in_inventory.vendorName,in_inventory.description1,
    in_inventory.description2,in_inventory.subPartNumber)
    against('
    $search_terms')
    ORDER BY relevance DESC"

    I keep timing out though, and its really slow. One table is 95,000 records, and the other is 200.
    Not another drugstore, not another town
    Tired of the overdrop, I had to put it down
    You know I was the man when I put the Crown down
    Then rolled around the Chemicals to pave the New Sound


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
  •