Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Senior Coder
    Join Date
    Jul 2005
    Location
    UK
    Posts
    1,051
    Thanks
    6
    Thanked 13 Times in 13 Posts

    Search database for two words in a field?

    Is this possible?

    Say the table is :


    ID string
    01 apple orange pear
    02 apple banana
    03 orange banana mango pear


    And I want to return only rows containing both 'orange' and 'pear'. Looking at the standard list of operators I'm uncertain. Can you do something like this?

    PHP Code:
    WHERE apple IN string && pear IN string 

  • #2
    Regular Coder
    Join Date
    May 2006
    Location
    Wales
    Posts
    820
    Thanks
    1
    Thanked 82 Times in 79 Posts
    Code:
    WHERE MATCH (string) AGAINST ('+orange +pear');

  • #3
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    You're getting the IN comparison mixed up. Using IN, you check to see if the column is equal to a value in the IN list:
    PHP Code:
    WHERE string in('apple''banana''orange'
    But the string must equal the value in the list.

    You might want to try the LIKE function:
    PHP Code:
    WHERE string like '%apple%'
    AND string like '%banana%' 
    Performance will suffer depending on how huge your tables are.

    Ideally you would use fulltext searching.

    http://dev.mysql.com/doc/refman/4.1/...xt-search.html

  • #4
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Mwnciau's syntax is an example of fulltext searching

    Make sure you create a fulltext index if you go that route.

  • #5
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts
    i hope this get u on your way i think it should work

    Code:
    SELECT * FROM table WHERE string LIKE '%apple%' AND string LIKE '%pear%'

  • #6
    Regular Coder
    Join Date
    Aug 2006
    Location
    UK, London, Dartford
    Posts
    221
    Thanks
    3
    Thanked 14 Times in 14 Posts
    mic2100's answer should be right or this:
    PHP Code:
    SELECT FROM table WHERE string {LIKE '%apple%' AND '%pear%'

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Quote Originally Posted by Lee Stevens
    PHP Code:
    SELECT FROM table WHERE string {LIKE '%apple%' AND '%pear%'
    That won't work.

  • #8
    Senior Coder
    Join Date
    Jul 2005
    Location
    UK
    Posts
    1,051
    Thanks
    6
    Thanked 13 Times in 13 Posts
    Thanks guys. I will go with the FULLTEXT route - my understanding is that indexing slows down the insertion of data, not the extraction, which is fine by me.

    Do my fields need to have both INDEX and FULLTEXTapplied to them, or just FULLTEXT?


  •  

    Posting Permissions

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