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 3 of 3
  1. #1
    New Coder
    Join Date
    Feb 2006
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Minor "searching a table" problem - need quick help

    Hello everybody! Thank you for taking the time to help me.
    Here's the issue:

    USER types "car dog house" in a text field that sets the $criteria variable in php.
    Is there any way that I can ask mySQL to search for all the three keywords separately?

    $sql = mysql_query("SELECT * FROM thisismytable LIKE ".$criteria."%);

    this is if I search for the whole string - if I make a query for all these three there is a chance that a row containing all of them would appear as a result three times...

    It ought to be some fundamental trick that I am missing so please help..

  • #2
    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
    The fundamental trick is called fulltext searching.

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

  • #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
    Shadowfox sent me a PM and said:
    In the database you have:
    Field1 ##### Field2 ##### Field 3
    7235 ##### Red brown fox ##### canoe

    Okay,
    When someone enters "Red 7235" I'd like mySQL to give me an array of the results but not searching for the whole string "Red 7235" but rather "Red" and then "7235". I parse the string and make "LIKE" queries for every single keyword. Thus I will get that "7235 Red brown fox canoe" row 2 times because it contains both the keywords - do you get it?

    I do apologise in case I am asking bizarre questions or showing unquestionable confusion but I really do have to get this simple thing working...

    I would GREATLY appreciate if you would give me a sample of how a query that I need should look like (and how many queries)

    IN CASE you still recommend FULLTEXT I'd like to know whether it searches a "FULLTEXT" for a criteria or parses a "FULLTEXT" criteria and checks every keyword in the table.
    Yes I would still recommend FULLTEXT searching. I don't have a lot of experience with it so I don't feel like I can guide you through the minutia, but I know how powerful it can be and it has everything built in to it so you can use it instead of writing a series of complex "like" queries.

    You must create a FULLTEXT index on each field you intend to use in fulltext searches.

    A fulltext query along the lines of what you're after would look something like this:
    Code:
    SELECT *
    FROM table
    WHERE MATCH (field1,field2)
    AGAINST ('+string1 +string2' IN BOOLEAN MODE)
    Spend some quality study time on the manual and you'll catch on.


  •  

    Posting Permissions

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