Search using fulltext and like %...% problem

Databases
1

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

2

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

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
)

4

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

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.

6

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

7

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

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

Thanks

9

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

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?

Cheers