SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jun 2004
    Location
    hull
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    wildcard's in a full-text search

    hi, im looking to put wildcards in my full-text search... can it be done???

    my query is

    $query = "SELECT *,
    MATCH(synopsis, title_english, title_original) AGAINST('$searchValue') AS score
    FROM film_information WHERE MATCH(synopsis, title_english, title_original) AGAINST('$searchValue')
    ORDER BY score DESC";

    which works fine if your $searchValue is in there, but for example:

    a search for 'plants' works fine, but a search for plant does not, so i would like to do a search for

    '*plant*', which would pick up, "plants", or "eggplant".... etc etc

    i hope you understand what im trying to do.

    many thanks

    Greg

  2. #2
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can use LIKE
    PHP Code:
     LIKE '%$searchstring%' 
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  3. #3
    SitePoint Member
    Join Date
    Jun 2004
    Location
    hull
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by frezno
    you can use LIKE
    PHP Code:
     LIKE '%$searchstring%' 

    will that work within the MATCH query. I need to do a full-text search. can i do a like accross 3 or more columns?

  4. #4
    does not play well with others frezno's Avatar
    Join Date
    Jan 2003
    Location
    Munich, Germany
    Posts
    1,391
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    don't know offhand, why don't you try it
    We are the Borg. Resistance is futile. Prepare to be assimilated.
    I'm Pentium of Borg.Division is futile.Prepare to be approximated.

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Washington, DC
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i'm curious how that will play out also
    post the results

  6. #6
    SitePoint Member
    Join Date
    Jun 2004
    Location
    hull
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AbstractHipHop
    i'm curious how that will play out also
    post the results
    Code:
    "SELECT *,
    			MATCH(synopsis, title_english, title_original) AGAINST('$searchValue') AS score
    			FROM film_information WHERE synopsis LIKE '%$searchValue%'
    			OR title_english LIKE '%$searchValue%'
    			OR title_original LIKE '%$searchValue%'"
    well thats the query i have, im not sure if its anydifferent to just doing 3 LIKE's with wild cards on the 3 columns

    im no sql genius

  7. #7
    simple tester McGruff's Avatar
    Join Date
    Sep 2003
    Location
    Glasgow
    Posts
    1,690
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql manual (IN BOOLEAN MODE operators):
    * An asterisk is the truncation operator. Unlike the other operators, it should be appended to the word, not prepended.
    You can't search for prefixes - just suffixes. Great.

    LIKE & RLIKE could be used instead:

    "... WHERE col RLIKE '[[:]]buff[[:>:]]'" (buff not buffy or rebuff)

    "... WHERE col LIKE '%buff%'" (buff, buffy, rebuffed)

    "... WHERE col RLIKE '.*buff[[:>:]]'" (rebuff, buff, not buffy)

    "... WHERE col RLIKE 'RLIKE '[[:]]buff.*'" (buff, buffy not rebuff)

    Rather than adding OR clauses to search in multiple columns, you could use CONCAT. Not sure if one is significantly faster than the other.

    I sometimes wonder just how useful MATCH is.

    For example, fulltext searching is optimised for very large databases: if a search term occurs in more than 50% of rows it will be ignored (see http://dev.mysql.com/doc/mysql/en/Fulltext_Search.html). That could be good or bad depending on your needs.

    With the minimum word length set at >3, you can't search for "PHP" or "MVC".

    Using LIKE/RLIKE and dynamic WHERE clause generation, it's possible to create a php search engine which supports all the usual boolean operators (+, -, *, ", etc) without using MATCH. I started building one a while back but haven't had time to finish it and compare performance.


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
  •