SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT STATMENT (Full Text Search Problem)

    Hey guys,

    Wondered if you could help me, Im doing a full text search and my select statement looks like this:

    Code:
    		$sql = "SELECT Submissions.Submission_ID, Submissions.Conference_ID, Submissions.Subject_ID, Submissions.Title, Submissions.Summary, Submissions.Keywords, Submissions.Timestamp, forum_members.M_FIRSTNAME, forum_members.M_SURNAME, forum_members.M_NAME, forum_members.MEMBER_ID 
    		
    			   MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords)
    			   AGAINST ('$searchstring') AS score FROM Submissions LEFT JOIN forum_members on Submissions.MEMBER_ID = forum_members.MEMBER_ID
    			   
    			   WHERE MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords)
    			   AGAINST ('$searchstring') ORDER BY score DESC";
    Problem is I only want it to select the current conference (which is stored in a variable called $CurrentConfID). So normally I would put "WHERE Submissions.Conference_ID = '$CurrentConfID'" somewhere in there but it doesn't seem to work anywhere I put it, any help?

  2. #2
    SitePoint Zealot shaman's Avatar
    Join Date
    Feb 2005
    Location
    W.R. Home
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT Submissions.Submission_ID, Submissions.Conference_ID, Submissions.Subject_ID, Submissions.Title, Submissions.Summary, Submissions.Keywords, Submissions.Timestamp, forum_members.M_FIRSTNAME, forum_members.M_SURNAME, forum_members.M_NAME, forum_members.MEMBER_ID 
    FROM Submissions LEFT JOIN forum_members on Submissions.MEMBER_ID = forum_members.MEMBER_ID
    			   
    WHERE Submissions.Conference_ID = '$CurrentConfID' AND
    MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords)
    			   AGAINST ('$searchstring') 
    ORDER BY score DESC
    It should be something like this.
    She's my german fraulein

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks that works a treat, however they're not ordered by score anymore. 'score' isn't an actual column in a table it was the results from the table itself.

    Normally I would put MATCH (blah blah blah blah) AS score FROM Submissions etc.... but that doesn't seem to work in this case.

  4. #4
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry...just don't want to see this go off the bottom of the page with a question still answered :S

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Hilly_2005
    Sorry...just don't want to see this go off the bottom of the page with a question still answered :S
    Bumpety Bump...

  6. #6
    SitePoint Wizard
    Join Date
    Jan 2004
    Location
    3rd rock from the sun
    Posts
    1,005
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's probably because this should be in databases/mysql forum.
    isempty()

  7. #7
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's 6 and two 3's though, it's all part of PHP and Ive had my main question answered in here so it seems stupid to go and post the same topic elsewhere.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Hilly_2005
    Normally I would put MATCH (blah blah blah blah) AS score FROM Submissions etc.... but that doesn't seem to work in this case.
    yes it will
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It doesn't mate, Ive tried....

    Code:
            $sql = "SELECT Submissions.Submission_ID, Submissions.Conference_ID, Submissions.Subject_ID, Submissions.Title, Submissions.Summary, Submissions.Keywords, Submissions.Timestamp, forum_members.M_FIRSTNAME, forum_members.M_SURNAME, forum_members.M_NAME, forum_members.MEMBER_ID 
    FROM Submissions LEFT JOIN forum_members on Submissions.MEMBER_ID = forum_members.MEMBER_ID
       
    WHERE Submissions.Conference_ID = '$CurrentConfID' AND
    MATCH(Submissions.Title, Submissions.Summary, Submissions.Keywords)
       AGAINST ('$searchstring') AS score FROM Submissions
    ORDER BY score DESC";

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    that query shouldn't even run, it's invalid syntax (which suggests you don't have proper error detection in your script)

    try this --
    Code:
    SELECT Submissions.Submission_ID
         , Submissions.Conference_ID
         , Submissions.Subject_ID
         , Submissions.Title
         , Submissions.Summary
         , Submissions.Keywords
         , Submissions.Timestamp
         , forum_members.M_FIRSTNAME
         , forum_members.M_SURNAME
         , forum_members.M_NAME
         , forum_members.MEMBER_ID 
         , MATCH(Submissions.Title
               , Submissions.Summary
               , Submissions.Keywords)
               AGAINST ('$searchstring') AS score
      FROM Submissions 
    LEFT 
      JOIN forum_members 
        on Submissions.MEMBER_ID 
         = forum_members.MEMBER_ID
     WHERE Submissions.Conference_ID = $CurrentConfID
       AND MATCH(Submissions.Title
               , Submissions.Summary
               , Submissions.Keywords)
               AGAINST ('$searchstring') > 0
    ORDER 
        BY score DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah it didn't I was getting error messages left right and centre, but that works a treat mate, thanks ever so much for helping me out.


  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    no prob, glad you got it working
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just another quick question! At the minute its just searching table Submissions for Title, Summary and Keywords, would it be possible to search 3 other columns in another table at the same time?

    I tried...

    Code:
    SELECT Submissions.Submission_ID
         , Submissions.Conference_ID
         , Submissions.Subject_ID
         , Submissions.Title
         , Submissions.Summary
         , Submissions.Keywords
         , Submissions.Timestamp
         , forum_members.M_FIRSTNAME
         , forum_members.M_SURNAME
         , forum_members.M_NAME
         , forum_members.MEMBER_ID 
         , MATCH(Submissions.Title
               , Submissions.Summary
               , Submissions.Keywords)
               AGAINST ('$searchstring') AS score
      FROM Submissions 
    LEFT 
      JOIN forum_members 
        on Submissions.MEMBER_ID 
         = forum_members.MEMBER_ID
    WHERE Submissions.Conference_ID = $CurrentConfID
       AND MATCH(Submissions.Title
               , Submissions.Summary
               , Submissions.Keywords
               , forum_members.M_FIRSTNAME
               , forum_members.M_SURNAME
                  , forum_members.M_NAME
               , forum_members.MEMBER_ID )
               AGAINST ('$searchstring') > 0
    ORDER 
        BY score DESC
    Thinking that it would work, but it says WRONG ARGUMENTS TO MATCH.

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you need a separate fulltext index for each table, because you cannot search two tables with the same fulltext index
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ive created an index on forum_members, including the fields M_FIRSTNAME, M_SURNAME and M_NAME.

    How would I join both of the tables up in the query I already have?

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the join would the same as you already have it, except you need to be sure that you really mean to have it as a LEFT OUTER JOIN, which implies that you want Submissions with or without matching forum_members rows

    which, if you ask me, doesn't sound right -- how can you have a submission from a non-exiting member?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Zealot
    Join Date
    Apr 2005
    Posts
    191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah it doesn't sound right does it? The reason I ask is that at the moment somebody can only search the database for:

    The text contained in a submission
    The text contained in a summary
    The text contained in the keywords provided

    I intended to allow users to also search for the name of the person who submitted something etc... etc... But if its not possible it doesn't really matter.

    However Im thinking of providing an 'Advanced' search so in there I could simply allow users to search different years submissions etc...

    So all I really have to do is something like:

    if option selected = 1 then do this search query and print the results out etc... etc....

    Make sense? lol... Thanks for all your help today anyway much appreciated.


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
  •