SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    FULLTEXT search on JOINed tables...?

    Hello All! I'm putting together a product search query and am having a bit of trouble..... Any assistance would be greatly appreciated. (Semi-new to MySQL, so I apologize if this is a dumb question)

    Here is my select, from and join statements (all work fine, just posted for reference.)
    PHP Code:
        $_select =     "SELECT inventory.id
                        , inventory.folderID
                        , inventory.title
                        , inventory.new
                        , inventory.manu
                        , inventory.modelYear
                        , inventory.special
                        , inventory.shortDesc
                        , inventory.loaFeet
                        , inventory.loaInches
                        , inventory.beamFeet
                        , inventory.beamInches
                        , inventory.engineConfig AS engineTypeNumber
                        , inventory.hp
                        , inventory.hours
                        , inventory.fuelType AS fuelTypeNumber
                        , inventory.fuelCap
                        , inventory.waterCap
                        , inventory.price
                        , inventory.standards
                        , inventory.options
                        , list_brands.name AS manuName
                        , list_engineType.name AS engineType
                        , list_fuel.name AS fuel
                        , inventory_images.fileName AS mainImageFileName
                        , inventory_images.title AS mainImageTitle
                        , inventory_images.alt AS mainImageAlt "
    ;
                            
        
    $_from " FROM inventory ";
                            
        
    // Add Joins to return all supporting Informaiton
        
    $_join "
                INNER JOIN list_brands
                            ON list_brands.id = inventory.manu
                                                                            
                        INNER JOIN list_engineType
                            ON list_engineType.id = inventory.engineConfig
                        
                        INNER JOIN list_fuel
                            ON list_fuel.id = inventory.fuelType
                            
                        INNER JOIN inventory_images
                            ON inventory_images.folderID = inventory.folderID 
                            AND inventory_images.isMainImage = 1  
          
                "


    I can successfully search on fields in the inventory table using this:
    PHP Code:
    $_where "WHERE MATCH (
                                  inventory.title
                                , inventory.shortDesc 
                                , inventory.longDesc 
                                , inventory.standards
                                , inventory.options
                                ) 
                                
                                AGAINST ('
    $keyword')
                                "


    Would like for something like this to work notice the 'list_brands', 'list_engineType' and other tables which are joined above:
    PHP Code:
            $_where "WHERE MATCH (
                                  inventory.title
                                , inventory.shortDesc 
                                , inventory.longDesc 
                                , inventory.standards
                                , inventory.options
                                , list_brands.name
                                , list_engineType.name 
                                , list_fuel.name
                                ) 
                                
                                AGAINST ('
    $keyword')
                                "


    My code may look a little funny to some, but I've found that splitting up the statements really helps me build dynamic queries. I then assemble all the individual parts before running the query.

    Again, Thanks in advance for any guidance or assistance!

  2. #2
    SitePoint Evangelist
    Join Date
    Sep 2006
    Posts
    428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    btw, forgot to post this. Here is the error I receive from mysql

    MySQL said:
    #1210 - Incorrect arguments to MATCH

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    fulltext indexes cannot span tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    fulltext indexes cannot span tables
    and must match a defined FULLTEXT index exactly, including column order.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast


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
  •