SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Mar 2003
    Location
    South Africa
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Search not %like%

    Hi
    I did a little search function on my site. I used
    SELECT *
    FROM table1 WHERE area ='province' and type like'%type_1%' and town like'%town_1%' and descr like '%descr_1%' and suburb like '%sub_1%'

    But it do not do the like thing. If I search for 3 bed and the actual data is 3 bedroom it gets the data, but if I search for 3 bedrooms (extra s) it do not get it or if I make spelling mistake I do not get a result either. So it seems that it rather search for the exact term instead of the like term.

  2. #2
    Prolific Blogger silver trophy Technosailor's Avatar
    Join Date
    Jun 2001
    Location
    Before These Crowded Streets
    Posts
    9,446
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not an Advanced issue, nor is it a PHP issue.

    Moved to MySQL.
    Aaron Brazell
    Technosailor



  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It does search for the like term - but doesn't take spelling or plurals into account. For example, the listing could be as follows:

    3 Bedroom Semi-Detached House in Outskirts

    If you search for "Bedroom" that would come up in the results. However, searching for "Bedrooms" would not, because it's a plural.

    Does this answer your question?

    -Sam
    Sam Hastings

  4. #4
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "bed" is a substring of "bedroom"
    "bedroom" is a substring of "bedroom"
    "bedrooms" is NOT a substring of "bedroom"
    "redboom" is NOT a substring of "bedroom"

    I.e. the LIKE operator does what it is supposed to do (determines whether or not a given character string matches a specified pattern) when nothing is returned if you type an extra "s" or make a spelling mistake.

  5. #5
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah what he said

    -Sam
    Sam Hastings

  6. #6
    SitePoint Member
    Join Date
    Mar 2003
    Location
    South Africa
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But how do one get it to search for something that is simular to the term I type in?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sql is not very good at "similar"

    you could, for example, take out all the vowels and replace them with wildcards before running the query

    thus, LIKE '%b%dr%m%' will match bedroom, budrim, boodream, badrum, and so on

    those are similar, but i don't think that's the direction you want to go
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    ...
    you could, for example, take out all the vowels and replace them with wildcards before running the query
    ...
    Now it's really interesting
    Anyone having a recipe fot the best "function" for finding the misspelled variants of a word?


    But, as r937 says, that's probably not the direction to go in this case.
    I think you should split the columns type, descr into properties like number_of_beds_in_a_room, room_with_a_view_or_not etc.
    Well, not exactly those properties, but I think you understand what I mean

  9. #9
    SitePoint Zealot ZangBunny's Avatar
    Join Date
    Jul 2003
    Location
    Mainz, Germany
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are two areas to check in the mysql manual:

    1) "fulltext" indices and searches

    2) the "soundex" function

  10. #10
    SitePoint Member
    Join Date
    Jun 2004
    Location
    London
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Plural solution

    I have come up with this solution, which seems to work ok...

    $search = $_POST['search'];

    // Strip the last character of the search string
    // to deal with plurals...
    $searchPlural = substr_replace($search, '', -1);
    //echo "orig string = $search";
    //echo "<br>search plural = $searchPlural";

    then include the new variable, $searchPlural in your query string.
    it strips off the 's' of a plural.

    $query="SELECT * FROM products WHERE productCode LIKE '%$search%' OR productName LIKE '%$search%' OR productName LIKE '%$searchPlural%' ORDER BY productName DESC";


    Hope it helps.


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
  •