SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    search using fulltext and like %...% problem

    $q1 = $db->GetAll("SELECT id, title,path FROM ".T_CATEGORIES." WHERE title LIKE '%".$PMDR->get('Cleaner')->clean_db($_GET['keyword'])."%' ");

    $q2 = $db->GetAll("SELECT * FROM ".T_CATEGORIES." WHERE MATCH(title) AGAINST ('".$PMDR->get('Cleaner')->clean_db($_GET['keyword'])."')");
    Hi, I have 2 queries here for my search. Both are expected to return the same result.

    The 1st query ($q1) will return result correctly.

    The 2nd query return empty array. I have set the title field as fulltext.

    Do you know where is the problem?
    I need help

  2. #2
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The first thing to do is echo the generated query in your PHP application and paste it into a MySQL command line client or into phpMyAdmin to see if it returns any results.

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Quote Originally Posted by SJH View Post
    The first thing to do is echo the generated query in your PHP application and paste it into a MySQL command line client or into phpMyAdmin to see if it returns any results.


    Hi SJH, thanks for your reply.

    I tested my search again.

    I found that if I search for keyword "rack" with $q2, it return 0 result. When I search keyword "racks" with $q2 again, it will return 2 results.

    It seems full text search will not match the keyword in the %like% way.

    Is there any solution I can do cause my search causing my site down so I thinking to convert all the search using %like% method to full text search.

    I tried adding "WITH QUERY EXPANSION" in the query, but the result is zero.
    SELECT * FROM ".T_CATEGORIES."
    WHERE MATCH (
    title
    )
    AGAINST (
    'rack'
    WITH QUERY EXPANSION
    )
    I need help

  4. #4
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Does the word "rack" appear in more than 50% of the records that the search query would return? If so, full text will ignore those results when you search for that keyword.

    Using %like% unfortunately is not a viable alternative because MySQL will have to carry out a full table scan, which will cause wildcard search queries to perform very badly as soon as you've got a large number of rows.

  5. #5
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SJH View Post
    Does the word "rack" appear in more than 50% of the records that the search query would return? If so, full text will ignore those results when you search for that keyword.

    Using %like% unfortunately is not a viable alternative because MySQL will have to carry out a full table scan, which will cause wildcard search queries to perform very badly as soon as you've got a large number of rows.

    Dear SJH,

    There are 1600 rows in the category table, 2 categories contain the keyword "racks".

    So in this case (searching for "rack"), the full text search will not return the two results?

    When I search racks, it will show 2 matched result.
    I need help

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2006
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found the solution!!!

    All I need to do is to use full text search "IN BOOLEAN MODE", then put the keyword in between *.

    eg: *keyword*
    I need help

  7. #7
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So just a question....

    When you search for "rack" with Full Text Indexing using $q2 in Boolean Mode it will return like words such as "racks", "blackracks" and "rackona"?

    Basiclly with your $q2 your able to return LIKE matches '%rack%'?

  8. #8
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    interesting -- I thought that fulltext searching could only do wildcards at the *end of the word. Has something been updated in MySQL?

    Thanks

  9. #9
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cathie View Post
    I found the solution!!!

    All I need to do is to use full text search "IN BOOLEAN MODE", then put the keyword in between *.

    eg: *keyword*
    Yes, so did I from my understanding. Cathie is showing here that *keyword* with IN BOOLEAN MODE but does this allow the functionality of %like%? I think maybe a typo has happened here... Or is the syntax suppose to surround the keyword?

  10. #10
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes the form is supposed to be

    Code:
    ... ('$word*' IN BOOLEAN MODE)
    Unless something has changed in MySQL...

    Cathie - do you mind posting your code for this?

    Cheers


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
  •