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)

    how bout just help on an "OR" statement

    Code:
    SELECT MATCH (sun_inventory.mfgPartNumber,sun_inventory.vendorName,sun_inventory.description1,
    sun_inventory.description2,sun_inventory.subPartNumber,in_inventory.mfgPartNumber,
    in_inventory.vendorName,in_inventory.description1,
    in_inventory.description2,in_inventory.subPartNumber)
    AGAINST ('$search_terms')
    AS score FROM 
    sun_inventory WHERE match
    (sun_inventory.mfgPartNumber,sun_inventory.vendorName,sun_inventory.description1,
    sun_inventory.description2,sun_inventory.subPartNumber) 
    against ('$search_terms')
    OR FROM in_inventory WHERE match
    (in_inventory.mfgPartNumber,
    in_inventory.vendorName,in_inventory.description1,
    in_inventory.description2,in_inventory.subPartNumber)
    against ('$search_terms')
    This is still my big problem with doing the fulltext search over two tables, and keeping them in the right order of relevance. I seem to be only having problems with the OR statement now. Can I PLEASE get a little help on this before I go crazy? Or is this code completely off the mark?
    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
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    yes this code is completely off the mark

    you cannot do OR FROM

    perhaps you're thinking of two separate queries with UNION ALL to bring the results together
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict trigger's Avatar
    Join Date
    Jun 2003
    Location
    Eagan
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's what I figured. I've been trying to do a fulltext search across multiple tables, but I can't seem to get it right. How bout
    PHP Code:
    SELECT MATCH (mfgPartNumber,vendorName,description1,
    description2,subPartNumber)
    AGAINST ('$search_terms')
    AS 
    score FROM 
    sun_inventory WHERE match
    (mfgPartNumber,vendorName,description1,
    description2,subPartNumber
    against ('$search_terms')
    UNION ALL
    SELECT MATCH 
    (mfgPartNumber,vendorName,description1,
    description2,subPartNumber)
    AGAINST ('$search_terms')
    AS 
    score FROM
    in_inventory WHERE match
    (mfgPartNumber,vendorName,description1,
    description2,subPartNumber)
    against ('$search_terms'
    I've tried this, but get an error "You have an error in your SQL syntax near 'UNION ALL SELECT MATCH"... So, am I approaching this right? The two tables I'm querying, sun_inventory, and in_inventory, are identical in structure, and have the same fulltext index on both. I don't have a clue what is wrong with my syntax.
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    perhaps because you're not on 4.0 which supports UNION
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict trigger's Avatar
    Join Date
    Jun 2003
    Location
    Eagan
    Posts
    342
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Shoot! You're right! I just assumed my ISP switched to 4.0 by now, but I guess they haven't. I'm gonna check out http://codewalkers.com/tutorials/33/2.html, but if you can offer assistance, I would appreciate it. I'm not sure if this JOIN stuff will work with a FULLTEXT search.
    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)
    also, I don't have a unique ID, or key, in this table (for reasons too complicated to explain in short). I'm gonna try, anyways.
    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
  •