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?

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.


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 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

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%’?

interesting – I thought that fulltext searching could only do wildcards at the *end of the word. Has something been updated in MySQL?


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?

yes the form is supposed to be

... ('$word*' IN BOOLEAN MODE)

Unless something has changed in MySQL…

Cathie - do you mind posting your code for this?